A More Effective Selective Index Rebuild/Reorganize Strategy

This is a follow up post to : Why SQL Fragmentation Remains High

As mentioned in this previous post, our dilemma was we were rebuilding every index on a nightly basis, and :

  1. the process proves to be very resource intensive
  2. we don’t see the benefit. The fragmentation for some of the indexes remain high.

After some digging, this is what we found:

Rebuild the index only if there will be at least 1000 pages affected. If < 1000 pages will be affected, fragmentation will not really be affected and potentially would remain high.

This is the script that we now use to selectively identify which indexes to rebuild, which indexes to reorganize, and which indexes to leave alone. This script uses the following criteria in determining which action to take:

  • current fragmentation %
  • number of pages used by the index

The current logic is:

  • reorganize index : if fragmentation is > 10 % but < 30% and number of pages > 1000
  • rebuild index : if fragmentation is > 30 and number of pages > 1000

This script is adapted from “Rebuild or reorganize indexes (with configuration)” from MSDN Books Online
(http://msdn.microsoft.com/en-us/library/ms188917.aspx)”

Download T-SQL Script

   1: -- http://www.sqlmusings.com
   2: -- Ensure a USE <databasename> statement has been executed first.
   3: SET NOCOUNT ON
   4:
   5: -- adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online 
   6: -- (http://msdn.microsoft.com/en-us/library/ms188917.aspx)
   7:
   8: -- =======================================================
   9: -- || Configuration variables:
  10: -- || - 10 is an arbitrary decision point at which to
  11: -- || reorganize indexes.
  12: -- || - 30 is an arbitrary decision point at which to
  13: -- || switch from reorganizing, to rebuilding.
  14: -- || - 0 is the default fill factor. Set this to a
  15: -- || a value from 1 to 99, if needed.
  16: -- =======================================================
  17: DECLARE @reorg_frag_thresh   float        SET @reorg_frag_thresh   = 10.0
  18: DECLARE @rebuild_frag_thresh float        SET @rebuild_frag_thresh = 30.0
  19: DECLARE @fill_factor         tinyint    SET @fill_factor         = 80
  20: DECLARE @report_only         bit            SET @report_only         = 1
  21:
  22: -- added (DS) : page_count_thresh is used to check how many pages the current table uses
  23: DECLARE @page_count_thresh     smallint    SET @page_count_thresh   = 1000
  24:
  25: -- Variables required for processing.
  26: DECLARE @objectid       int
  27: DECLARE @indexid        int
  28: DECLARE @partitioncount bigint
  29: DECLARE @schemaname     nvarchar(130)
  30: DECLARE @objectname     nvarchar(130)
  31: DECLARE @indexname      nvarchar(130)
  32: DECLARE @partitionnum   bigint
  33: DECLARE @partitions     bigint
  34: DECLARE @frag           float
  35: DECLARE @page_count     int
  36: DECLARE @command        nvarchar(4000)
  37: DECLARE @intentions     nvarchar(4000)
  38: DECLARE @table_var      TABLE(
  39:                           objectid     int,
  40:                           indexid      int,
  41:                           partitionnum int,
  42:                           frag         float,
  43:                                   page_count   int
  44:                         )
  45:
  46: -- Conditionally select tables and indexes from the
  47: -- sys.dm_db_index_physical_stats function and
  48: -- convert object and index IDs to names.
  49: INSERT INTO
  50:     @table_var
  51: SELECT
  52:     [object_id]                    AS objectid,
  53:     [index_id]                     AS indexid,
  54:     [partition_number]             AS partitionnum,
  55:     [avg_fragmentation_in_percent] AS frag,
  56:     [page_count]                   AS page_count
  57: FROM
  58:     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
  59: WHERE
  60:     [avg_fragmentation_in_percent] > @reorg_frag_thresh
  61:     AND
  62:     page_count > @page_count_thresh
  63:     AND
  64:     index_id > 0
  65:
  66:
  67: -- Declare the cursor for the list of partitions to be processed.
  68: DECLARE partitions CURSOR FOR
  69:     SELECT * FROM @table_var
  70:
  71: -- Open the cursor.
  72: OPEN partitions
  73:
  74: -- Loop through the partitions.
  75: WHILE (1=1) BEGIN
  76:     FETCH NEXT
  77:         FROM partitions
  78:         INTO @objectid, @indexid, @partitionnum, @frag, @page_count
  79:
  80:     IF @@FETCH_STATUS < 0 BREAK
  81:
  82:     SELECT
  83:         @objectname = QUOTENAME(o.[name]),
  84:         @schemaname = QUOTENAME(s.[name])
  85:     FROM
  86:         sys.objects AS o WITH (NOLOCK)
  87:         JOIN sys.schemas as s WITH (NOLOCK)
  88:         ON s.[schema_id] = o.[schema_id]
  89:     WHERE
  90:         o.[object_id] = @objectid
  91:
  92:     SELECT
  93:         @indexname = QUOTENAME([name])
  94:     FROM
  95:         sys.indexes WITH (NOLOCK)
  96:     WHERE
  97:         [object_id] = @objectid AND
  98:         [index_id] = @indexid
  99:
 100:     SELECT
 101:         @partitioncount = count (*)
 102:     FROM
 103:         sys.partitions WITH (NOLOCK)
 104:     WHERE
 105:         [object_id] = @objectid AND
 106:         [index_id] = @indexid
 107:
 108:     -- Build the required statement dynamically based on options and index stats.
 109:     SET @intentions =
 110:         @schemaname + N'.' +
 111:         @objectname + N'.' +
 112:         @indexname + N':' + CHAR(13) + CHAR(10)
 113:     SET @intentions =
 114:         REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
 115:         @intentions
 116:     SET @intentions = @intentions +
 117:         N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
 118:         N' PAGE COUNT: '    + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
 119:
 120:     IF @frag < @rebuild_frag_thresh BEGIN
 121:         SET @intentions = @intentions +
 122:             N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
 123:         SET @command =
 124:             N'ALTER INDEX ' + @indexname +
 125:             N' ON ' + @schemaname + N'.' + @objectname +
 126:             N' REORGANIZE; ' +
 127:             N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname +
 128:             N' ' + @indexname + ';'
 129:
 130:     END
 131:     IF @frag >= @rebuild_frag_thresh BEGIN
 132:         SET @intentions = @intentions +
 133:             N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
 134:         SET @command =
 135:             N'ALTER INDEX ' + @indexname +
 136:             N' ON ' + @schemaname + N'.' +     @objectname +
 137:             N' REBUILD'
 138:     END
 139:     IF @partitioncount > 1 BEGIN
 140:         SET @intentions = @intentions +
 141:             N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
 142:         SET @command = @command +
 143:             N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
 144:     END
 145:     IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
 146:         SET @intentions = @intentions +
 147:             N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
 148:         SET @command = @command +
 149:             N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
 150:     END
 151:
 152:     -- Execute determined operation, or report intentions
 153:     IF @report_only = 0 BEGIN
 154:         SET @intentions = @intentions + N' EXECUTING: ' + @command
 155:         PRINT @intentions
 156:         EXEC (@command)
 157:     END ELSE BEGIN
 158:         PRINT @intentions
 159:     END
 160:     PRINT @command
 161:
 162: END
 163:
 164: -- Close and deallocate the cursor.
 165: CLOSE partitions
 166: DEALLOCATE partitions
 167:
 168: GO
Advertisements

23 comments

  • Gives an error when you try and run the script:
    Msg 102, Level 15, State 1, Line 58
    Incorrect syntax near ‘(‘.
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’)

    Like

    • @Tom what version of SQL Server and Service Pack are you running this in?
      also make sure that LIMITED is enclosed in single quotes. sometimes when you paste this script to Word, the character for single quote changes. You may need to retype it in another editor or in SSMS.

      Like

  • Hi, sql 2005 sp3 developer version. Its loading the script via the link and appears to be the same as your code.

    Like

  • Ah, my db was set to compatibility level 80, changed it to 90 and it works fine now. Thanks for the code.

    Like

  • If the fragmentation is > 30 would it be acceptable to do a reorganize over a rebuild? The reason I ask is I’m not running Enterprise edition and as such couldn’t do a rebuild with the online option set to on…I’m worried about the table (which is rather large) being inaccessible while the rebuild was occurring….

    Like

    • Hi Dave,

      How much fragmentation do you have? How big is your table? A reorganize should be ok in a more regular maintenance schedule (ex every couple days, every week), but you will need to build into your schedule a full rebuild at some point (once a month, once every couple of months depending on your fragmentation). Unfortunately this will mean some downtime for you.

      Like

  • I went back and did a check, and the bigger tables actually have relatively small levels of fragmentation (5% on 388k pages) and the higher levels of fragmentation (> 30%) span less than 10 pages so I’m probably okay…

    But I should definitely perform a rebuild periodically? If I perform a reorganize every few days or even once a week, will a rebuild make a noticeable performance difference?

    Like

    • Yes I will recommend rebuilding your indexes periodically.
      For reorganizing, you will see a performance gain when the fragmentation is higher (ie reorganizing a 5% fragmented table, and reorganizing a 40% fragmented tables).

      Paul Randal wrote an excellent article, coupled with excellent blog posts, on tradeoffs of reorganizing and rebuilding indexes, I am sure you will find it invaluable:

      Top Tips for Effective Database Maintenance
      http://technet.microsoft.com/en-us/magazine/cc671165.aspx

      Like

  • Thanks for the link! I’ve read through his article (and followed each of the links he provided as well) and found it extremely informative…

    So it isn’t recommended to reorganize or rebuild indexes that are below 1000 pages no matter their fragmentation level?

    Thanks for all your help!!

    Like

  • Tarun Rodrigues

    Great article, Belle !!! Btw, what text editor are you using for sql files, the look and feel is good.

    Like

  • Tarun Rodrigues

    Great article and script.. Belle…
    Btw what text editor did use over here for sql files, the look is good!!!

    Like

  • Tarun Rodrigues

    The script concatenates PARTITION= to the reorganize/rebuild commands. Don’t you think the script would throw an error in case of ALTER INDEX.. REORGANIZE; because the string is already terminated by semi-colon (;)??? And if we try to concatenate partition = to this??

    Infact partition= would be concatenated after the update statistics..? It would throw an error rt.??

    Like

  • Very nice script, Belle. Thanks for sharing!

    Like

  • Great script!! – fillfactor in this script means that once the rebuild index operation performed will it set all the indexes to 80% fillfactor from original?

    Thanks
    John

    Like

  • Belle, thank you so much for this wonderful script. For a newbie DBA like me it has proven invaluable already. The last person responsible for databases had set up maintenance jobs to reorganize AND rebuild all indexes in all databases every night; ouch! Although the server is small (in comparison to some) this maintenance plan was taking almost 3.5 hours to run. Your script reduced it to less than 20 minutes, and now is run once per week. I was wondering if you are planning on extending this script in the future? As I said I’m a newbie, only started into the DBA world a few months back so am still learning. I have been reading information on LOB, Online rebuilding (Enterprise edition) and such and wondering if things like that need to be in this script or if the default variables on ALTER INDEX will cover it? Thanks again, and cheers!

    Like

  • I have been using this code for quite some time. It is great. Thanks! I qwas wondering if this could be modified to scroll through all user databases on a server and perform the indexing instead of only on a prescribed database?

    Thanks!

    Like

  • How do I apply this script to run on all databases on the server?

    Like

  • Alonso Fernandez

    Hi the error
    “Gives an error when you try and run the script:
    Msg 102, Level 15, State 1, Line 58
    Incorrect syntax near ‘(’.”
    is because you have a SQL 2005 with DB in compatibility 80 you can fix this error just declar 1 more variable @DB_ID smallint and set @DB_ID=DB_ID()
    DECLARE @DB_ID smallint
    DECLARE @table_var TABLE(
    objectid int,
    indexid int,
    partitionnum int,
    frag float,
    page_count int
    )

    — Conditionally select tables and indexes from the
    — sys.dm_db_index_physical_stats function and
    — convert object and index IDs to names.
    set @DB_ID = DB_ID()
    INSERT INTO @table_var
    SELECT [object_id] AS objectid,
    [index_id] AS indexid,
    [partition_number] AS partitionnum,
    [avg_fragmentation_in_percent] AS frag,
    [page_count] AS page_count
    FROM sys.dm_db_index_physical_stats (@DB_ID,NULL, NULL , NULL, ‘LIMITED’)
    WHERE [avg_fragmentation_in_percent] > @reorg_frag_thresh
    AND page_count > @page_count_thresh
    AND index_id > 0

    Like

  • in addition to Tarun Rodrigues’s post (#14) – about statistics – which is correct I want to add about rebuilding partitioned indexes –

    FILLFACTOR is NOT valid (http://msdn.microsoft.com/en-us/library/ms188388.aspx)- thus it has to be omitted for partitioned indexes

    my resulting code for generating command line is below

    IF @frag = @rebuild_frag_thresh BEGIN
    SET @intentions = @intentions +
    N’ OPERATION: REBUILD’ + CHAR(13) + CHAR(10)
    SET @command =
    N’ALTER INDEX ‘ + @indexname +
    N’ ON ‘ + @schemaname + N’.’ + @objectname +
    N’ REBUILD’
    END
    IF @partitioncount > 1 BEGIN
    SET @intentions = @intentions +
    N’ PARTITION: ‘ + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
    SET @command = @command +
    N’ PARTITION=’ + CAST(@partitionnum AS nvarchar(10))
    END
    ELSE BEGIN

    IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
    SET @intentions = @intentions +
    N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
    SET @command = @command +
    N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
    END

    Like

  • Thanks. I used this to defragment a third party SQL Server 2008 Express database that had developed serious performance issues.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s