Index Defrag Script, v3.0

UPDATE: This script has been significantly updated. Please view the latest version at http://sqlfool.com/2011/06/index-defrag-script-v4-1/.

I’ve just completed the latest version of my index defrag script! Here’s a brief list of the updates:

  • Fixed a bug with the LOB logic. In the previous version, after a LOB was encountered, all subsequent indexes would be reorganized.
  • Added support for stat rebuilds after the index defrag is complete (@rebuildStats)
  • Added an exclusion list table (dba_indexDefragExclusion) to support index scheduling
  • Modified logging to show which defrags are “in progress”; added columns to dba_indexDefragLog
  • Added support for the defrag of the model and msdb databases
  • Added @scanMode as a configurable parameter

So what can this index defrag script do? Well, for starters, you can:

  • Schedule it to run with the default settings; it works “right out of the box” with no additional configuration necessary
  • Run this one script from a centralized database for all databases on a server
  • Run this script for a specific database or table
  • Configure custom threshold limits and the point at which a rebuild should be performed (instead of a reorganize)
  • Defrag individual partitions
  • Log its actions and the duration of the defrag
  • Run in “commands only” mode (@executeSQL = 0, @printCommands = 1)
  • Customize performance parameters such as @maxDopRestriction and @defragDelay to minimize impact on the server
  • Schedule specific indexes to only be defragged on weekends, or every other day

To use this last option, you need to add a record to the dba_indexDefragExclusion table. I think all of the columns are pretty self-explanatory except the [exclusionMask] column. The way this works is each day of the week is assigned a value:
1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday

Take a SUM of the values for the days that you want excluded. So if you want an index to only be defragged on weekends, you would add up Monday through Friday (2+4+8+16+32) and use a value of 62 for the exclusionMask column. For a little more information on how this works, check out my blog post on Bitwise Operations.

Please note: if you don’t insert any records into the dba_indexDefragExclusion table, by default all indexes will be defragged every run-time if they exceed the specified thresholds. This is normal behavior and may be perfectly fine in your environment. However, if the dba_indexDefragExclusion table does not exist, the script will fail.

I try to document each parameter within the code, so check the comments section in the script for a full list of parameters and what they do.

Special thanks to everyone who helped beta test this script! :)

Without further ado, the script:

/* Drop Table Scripts:
Drop Table dbo.dba_indexDefragLog;
Drop Table dbo.dba_indexDefragExclusion;
*/
If Not Exists(Select [object_id] From sys.tables 
    Where [name] In (N'dba_indexDefragLog', 'dba_indexDefragExclusion'))
Begin
 
    Create Table dbo.dba_indexDefragLog
    (
          indexDefrag_id    int identity(1,1)   Not Null
        , databaseID        int                 Not Null
        , databaseName      nvarchar(128)       Not Null
        , objectID          int                 Not Null
        , objectName        nvarchar(128)       Not Null
        , indexID           int                 Not Null
        , indexName         nvarchar(128)       Not Null
        , partitionNumber   smallint            Not Null
        , fragmentation     float               Not Null
        , page_count        int                 Not Null
        , dateTimeStart     datetime            Not Null
        , dateTimeEnd       datetime            Null
        , durationSeconds   int                 Null
 
        Constraint PK_indexDefragLog 
            Primary Key Clustered (indexDefrag_id)
    );
 
    Print 'dba_indexDefragLog Table Created';
 
    Create Table dbo.dba_indexDefragExclusion
    (
          databaseID        int                 Not Null
        , databaseName      nvarchar(128)       Not Null
        , objectID          int                 Not Null
        , objectName        nvarchar(128)       Not Null
        , indexID           int                 Not Null
        , indexName         nvarchar(128)       Not Null
        , exclusionMask     int                 Not Null
            /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */
 
        Constraint PK_indexDefragExclusion 
            Primary Key Clustered (databaseID, objectID, indexID)
    );
 
    Print 'dba_indexDefragExclusion Table Created';
 
End
Else
    RaisError('One or more tables already exist.  Please drop or rename before proceeding.', 16, 0);
 
If ObjectProperty(Object_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1
Begin
    Drop Procedure dbo.dba_indexDefrag_sp;
    Print 'Procedure dba_indexDefrag_sp dropped';
End;
Go
 
 
Create Procedure dbo.dba_indexDefrag_sp
 
    /* Declare Parameters */
      @minFragmentation     float           = 5.0  
        /* in percent, will not defrag if fragmentation less than specified */
    , @rebuildThreshold     float           = 30.0  
        /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
    , @executeSQL           bit             = 1     
        /* 1 = execute; 0 = print command only */
    , @database             varchar(128)    = Null
        /* Option to specify a database name; null will return all */
    , @tableName            varchar(4000)   = Null  -- databaseName.schema.tableName
        /* Option to specify a table name; null will return all */
    , @scanMode             varchar(10)     = N'LIMITED'
        /* Options are LIMITED, SAMPLED, and DETAILED */
    , @onlineRebuild        bit             = 1     
        /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
    , @maxDopRestriction    tinyint         = Null
        /* Option to restrict the number of processors for the operation; only in Enterprise */
    , @printCommands        bit             = 0     
        /* 1 = print commands; 0 = do not print commands */
    , @printFragmentation   bit             = 0
        /* 1 = print fragmentation prior to defrag; 
           0 = do not print */
    , @defragDelay          char(8)         = '00:00:05'
        /* time to wait between defrag commands */
    , @debugMode            bit             = 0
        /* display some useful comments to help determine if/where issues occur */
    , @rebuildStats         bit             = 1
        /* option to rebuild stats after completed index defrags */
 
As
/*********************************************************************************
    Name:       dba_indexDefrag_sp
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    Defrags all indexes for the current database
 
    Notes:
 
    CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING.
 
      @minFragmentation     defaulted to 10%, will not defrag if fragmentation 
                            is less than that
 
      @rebuildThreshold     defaulted to 30% as recommended by Microsoft in BOL;
                            greater than 30% will result in rebuild instead
 
      @executeSQL           1 = execute the SQL generated by this proc; 
                            0 = print command only
 
      @database             Optional, specify specific database name to defrag;
                            If not specified, all non-system databases will
                            be defragged.
 
      @tableName            Specify if you only want to defrag indexes for a 
                            specific table, format = databaseName.schema.tableName;
                            if not specified, all tables will be defragged.
 
      @scanMode             Specifies which scan mode to use to determine
                            fragmentation levels.  Options are:
                            LIMITED - scans the parent level; quickest mode,
                                      recommended for most cases.
                            SAMPLED - samples 1% of all data pages; if less than
                                      10k pages, performs a DETAILED scan.
                            DETAILED - scans all data pages.  Use great care with
                                       this mode, as it can cause performance issues.
 
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild
 
      @maxDopRestriction    Option to specify a processor limit for index rebuilds
 
      @printCommands        1 = print commands to screen; 
                            0 = do not print commands
 
      @printFragmentation   1 = print fragmentation to screen;
                            0 = do not print fragmentation
 
      @defragDelay          Time to wait between defrag commands; gives the
                            server a little time to catch up 
 
      @debugMode            1 = display debug comments; helps with troubleshooting
                            0 = do not display debug comments
 
      @rebuildStats         Affects only statistics that need to be rebuilt
                            1 = rebuild stats
                            0 = do not rebuild stats
 
    Called by:  SQL Agent Job or DBA
 
    Date        Initials	Version Description
    ----------------------------------------------------------------------------
    2007-12-18  MFU         1.0     Initial Release
    2008-10-17  MFU         1.1     Added @defragDelay, CIX_temp_indexDefragList
    2008-11-17  MFU         1.2     Added page_count to log table
                                    , added @printFragmentation option
    2009-03-17  MFU         2.0     Provided support for centralized execution
                                    , consolidated Enterprise & Standard versions
                                    , added @debugMode, @maxDopRestriction
                                    , modified LOB and partition logic  
    2009-06-18  MFU         3.0     Fixed bug in LOB logic, added @scanMode option
                                    , added support for stat rebuilds (@rebuildStats)
                                    , support model and msdb defrag
                                    , added columns to the dba_indexDefragLog table
                                    , modified logging to show "in progress" defrags
                                    , added defrag exclusion list (scheduling)
*********************************************************************************
    Exec dbo.dba_indexDefrag_sp
          @executeSQL           = 0
        , @printCommands        = 1
        , @debugMode            = 1
        , @printFragmentation   = 1;
*********************************************************************************/																
 
Set NoCount On;
Set XACT_Abort On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set Numeric_RoundAbort Off;
Set Quoted_Identifier On;
 
Begin
 
    If @debugMode = 1 RaisError('Undusting the cogs and starting up...', 0, 42) With NoWait;
 
    /* Declare our variables */
    Declare   @objectID             int
            , @databaseID           int
            , @databaseName         nvarchar(128)
            , @indexID              int
            , @partitionCount       bigint
            , @schemaName           nvarchar(128)
            , @objectName           nvarchar(128)
            , @indexName            nvarchar(128)
            , @partitionNumber      smallint
            , @fragmentation        float
            , @pageCount            int
            , @sqlCommand           nvarchar(4000)
            , @rebuildCommand       nvarchar(200)
            , @dateTimeStart        datetime
            , @dateTimeEnd          datetime
            , @containsLOB          bit
            , @editionCheck         bit
            , @debugMessage         varchar(128)
            , @updateSQL            nvarchar(4000)
            , @partitionSQL         nvarchar(4000)
            , @partitionSQL_Param   nvarchar(1000)
            , @LOB_SQL              nvarchar(4000)
            , @LOB_SQL_Param        nvarchar(1000)
            , @rebuildStatsID       int
            , @rebuildStatsSQL      nvarchar(1000)
            , @indexDefrag_id       int;
 
    /* Create our temporary tables */
    Create Table #indexDefragList
    (
          databaseID        int
        , databaseName      nvarchar(128)
        , objectID          int
        , indexID           int
        , partitionNumber   smallint
        , fragmentation     float
        , page_count        int
        , defragStatus      bit
        , schemaName        nvarchar(128)   Null
        , objectName        nvarchar(128)   Null
        , indexName         nvarchar(128)   Null
    );
 
    Create Table #databaseList
    (
          databaseID        int
        , databaseName      varchar(128)
        , scanStatus        bit
        , statsStatus       bit
    );
 
    Create Table #processor 
    (
          [index]           int
        , Name              varchar(128)
        , Internal_Value    int
        , Character_Value   int
    );
 
    If @debugMode = 1 RaisError('Beginning validation...', 0, 42) With NoWait;
 
    /* Just a little validation... */
    If @minFragmentation Not Between 0.00 And 100.0
        Set @minFragmentation = 10.0;
 
    If @rebuildThreshold Not Between 0.00 And 100.0
        Set @rebuildThreshold = 30.0;
 
    If @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
        Set @defragDelay = '00:00:05';
 
    If @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED')
        Set @scanMode = 'LIMITED';
 
    /* Make sure we're not exceeding the number of processors we have available */
    Insert Into #processor
    Execute xp_msver 'ProcessorCount';
 
    If @maxDopRestriction Is Not Null And @maxDopRestriction > (Select Internal_Value From #processor)
        Select @maxDopRestriction = Internal_Value
        From #processor;
 
    /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
    If (Select ServerProperty('EditionID')) In (1804890536, 610778273, -2117995310) 
        Set @editionCheck = 1 -- supports online rebuilds
    Else
        Set @editionCheck = 0; -- does not support online rebuilds
 
    If @debugMode = 1 RaisError('Grabbing a list of our databases...', 0, 42) With NoWait;
 
    /* Retrieve the list of databases to investigate */
    Insert Into #databaseList
    Select database_id
        , name
        , 0 -- not scanned yet for fragmentation
        , 0 -- statistics not yet updated
    From sys.databases
    Where name = IsNull(@database, name)
        And [name] Not In ('master', 'tempdb')-- exclude system databases
        And [state] = 0; -- state must be ONLINE
 
    If @debugMode = 1 RaisError('Looping through our list of databases and checking for fragmentation...', 0, 42) With NoWait;
 
    /* Loop through our list of databases */
    While (Select Count(*) From #databaseList Where scanStatus = 0) > 0
    Begin
 
        Select Top 1 @databaseID = databaseID
        From #databaseList
        Where scanStatus = 0;
 
        Select @debugMessage = '  working on ' + DB_Name(@databaseID) + '...';
 
        If @debugMode = 1
            RaisError(@debugMessage, 0, 42) With NoWait;
 
       /* Determine which indexes to defrag using our user-defined parameters */
        Insert Into #indexDefragList
        Select
              database_id As databaseID
            , QuoteName(DB_Name(database_id)) As 'databaseName'
            , [object_id] As objectID
            , index_id As indexID
            , partition_number As partitionNumber
            , avg_fragmentation_in_percent As fragmentation
            , page_count 
            , 0 As 'defragStatus' /* 0 = unprocessed, 1 = processed */
            , Null As 'schemaName'
            , Null As 'objectName'
            , Null As 'indexName'
        From sys.dm_db_index_physical_stats (@databaseID, Object_Id(@tableName), Null , Null, @scanMode)
        Where avg_fragmentation_in_percent >= @minFragmentation 
            And index_id > 0 -- ignore heaps
            And page_count > 8 -- ignore objects with less than 1 extent
            And index_level = 0 -- leaf-level nodes only, supports @scanMode
        Option (MaxDop 2);
 
        /* Keep track of which databases have already been scanned */
        Update #databaseList
        Set scanStatus = 1
        Where databaseID = @databaseID;
 
    End
 
    Create Clustered Index CIX_temp_indexDefragList
        On #indexDefragList(databaseID, objectID, indexID, partitionNumber);
 
    /* Delete any indexes from our to-do that are also in our exclusion list for today */
    Delete idl
    From #indexDefragList As idl
    Join dbo.dba_indexDefragExclusion As ide
        On idl.databaseID = ide.databaseID
        And idl.objectID = ide.objectID
        And idl.indexID = ide.indexID
    Where exclusionMask & Power(2, DatePart(weekday, GetDate())-1) > 0;
 
    Select @debugMessage = 'Looping through our list... there''s ' + Cast(Count(*) As varchar(10)) + ' indexes to defrag!'
    From #indexDefragList;
 
    If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait;
 
    /* Begin our loop for defragging */
    While (Select Count(*) From #indexDefragList Where defragStatus = 0) > 0
    Begin
 
        If @debugMode = 1 RaisError('  Picking an index to beat into shape...', 0, 42) With NoWait;
 
        /* Grab the most fragmented index first to defrag */
        Select Top 1 
              @objectID         = objectID
            , @indexID          = indexID
            , @databaseID       = databaseID
            , @databaseName     = databaseName
            , @fragmentation    = fragmentation
            , @partitionNumber  = partitionNumber
            , @pageCount        = page_count
        From #indexDefragList
        Where defragStatus = 0
        Order By fragmentation Desc;
 
        If @debugMode = 1 RaisError('  Looking up the specifics for our index...', 0, 42) With NoWait;
 
        /* Look up index information */
        Select @updateSQL = N'Update idl
            Set schemaName = QuoteName(s.name)
                , objectName = QuoteName(o.name)
                , indexName = QuoteName(i.name)
            From #indexDefragList As idl
            Inner Join ' + @databaseName + '.sys.objects As o
                On idl.objectID = o.object_id
            Inner Join ' + @databaseName + '.sys.indexes As i
                On o.object_id = i.object_id
            Inner Join ' + @databaseName + '.sys.schemas As s
                On o.schema_id = s.schema_id
            Where o.object_id = ' + Cast(@objectID As varchar(10)) + '
                And i.index_id = ' + Cast(@indexID As varchar(10)) + '
                And i.type > 0
                And idl.databaseID = ' + Cast(@databaseID As varchar(10));
 
        Execute sp_executeSQL @updateSQL;
 
        /* Grab our object names */
        Select @objectName  = objectName
            , @schemaName   = schemaName
            , @indexName    = indexName
        From #indexDefragList
        Where objectID = @objectID
            And indexID = @indexID
            And databaseID = @databaseID;
 
        If @debugMode = 1 RaisError('  Grabbing the partition count...', 0, 42) With NoWait;
 
        /* Determine if the index is partitioned */
        Select @partitionSQL = 'Select @partitionCount_OUT = Count(*)
                                    From ' + @databaseName + '.sys.partitions
                                    Where object_id = ' + Cast(@objectID As varchar(10)) + '
                                        And index_id = ' + Cast(@indexID As varchar(10)) + ';'
            , @partitionSQL_Param = '@partitionCount_OUT int OutPut';
 
        Execute sp_executeSQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OutPut;
 
        If @debugMode = 1 RaisError('  Seeing if there''s any LOBs to be handled...', 0, 42) With NoWait;
 
        /* Determine if the table contains LOBs */
        Select @LOB_SQL = ' Select @containsLOB_OUT = Count(*)
                            From ' + @databaseName + '.sys.columns With (NoLock) 
                            Where [object_id] = ' + Cast(@objectID As varchar(10)) + '
                                And (system_type_id In (34, 35, 99)
                                        Or max_length = -1);'
                            /*  system_type_id --> 34 = image, 35 = text, 99 = ntext
                                max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */
                , @LOB_SQL_Param = '@containsLOB_OUT int OutPut';
 
        Execute sp_executeSQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OutPut;
 
        If @debugMode = 1 RaisError('  Building our SQL statements...', 0, 42) With NoWait;
 
        /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */
        If @fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1
        Begin
 
            Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' 
                                + @schemaName + N'.' + @objectName + N' ReOrganize';
 
            /* If our index is partitioned, we should always reorganize */
            If @partitionCount > 1
                Set @sqlCommand = @sqlCommand + N' Partition = ' 
                                + Cast(@partitionNumber As nvarchar(10));
 
        End;
 
        /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */
        If @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1
        Begin
 
            /* Set online rebuild options; requires Enterprise Edition */
            If @onlineRebuild = 1 And @editionCheck = 1 
                Set @rebuildCommand = N' Rebuild With (Online = On';
            Else
                Set @rebuildCommand = N' Rebuild With (Online = Off';
 
            /* Set processor restriction options; requires Enterprise Edition */
            If @maxDopRestriction Is Not Null And @editionCheck = 1
                Set @rebuildCommand = @rebuildCommand + N', MaxDop = ' + Cast(@maxDopRestriction As varchar(2)) + N')';
            Else
                Set @rebuildCommand = @rebuildCommand + N')';
 
            Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
                            + @schemaName + N'.' + @objectName + @rebuildCommand;
 
        End;
 
        /* Are we executing the SQL?  If so, do it */
        If @executeSQL = 1
        Begin
 
            If @debugMode = 1 RaisError('  Executing SQL statements...', 0, 42) With NoWait;
 
            /* Grab the time for logging purposes */
            Set @dateTimeStart  = GetDate();
 
            /* Log our actions */
            Insert Into dbo.dba_indexDefragLog
            (
                  databaseID
                , databaseName
                , objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , dateTimeStart
            )
            Select
                  @databaseID
                , @databaseName
                , @objectID
                , @objectName
                , @indexID
                , @indexName
                , @partitionNumber
                , @fragmentation
                , @pageCount
                , @dateTimeStart;
 
            Set @indexDefrag_id = Scope_Identity();
 
            /* Execute our defrag! */
            Execute sp_executeSQL @sqlCommand;
            Set @dateTimeEnd  = GetDate();
 
            /* Update our log with our completion time */
            Update dbo.dba_indexDefragLog
            Set dateTimeEnd = @dateTimeEnd
                , durationSeconds = DateDiff(second, @dateTimeStart, @dateTimeEnd)
            Where indexDefrag_id = @indexDefrag_id;
 
            /* Just a little breather for the server */
            WaitFor Delay @defragDelay;
 
            /* Print if specified to do so */
            If @printCommands = 1
                Print N'Executed: ' + @sqlCommand;
        End
        Else
        /* Looks like we're not executing, just printing the commands */
        Begin
            If @debugMode = 1 RaisError('  Printing SQL statements...', 0, 42) With NoWait;
 
            If @printCommands = 1 Print IsNull(@sqlCommand, 'error!');
        End
 
        If @debugMode = 1 RaisError('  Updating our index defrag status...', 0, 42) With NoWait;
 
        /* Update our index defrag list so we know we've finished with that index */
        Update #indexDefragList
        Set defragStatus = 1
        Where databaseID       = @databaseID
          And objectID         = @objectID
          And indexID          = @indexID
          And partitionNumber  = @partitionNumber;
 
    End
 
    /* Do we want to output our fragmentation results? */
    If @printFragmentation = 1
    Begin
 
        If @debugMode = 1 RaisError('  Displaying fragmentation results...', 0, 42) With NoWait;
 
        Select databaseID
            , databaseName
            , objectID
            , objectName
            , indexID
            , indexName
            , fragmentation
            , page_count
        From #indexDefragList;
 
    End;
 
    /* Do we want to rebuild stats? */
    If @rebuildStats = 1
    Begin
 
        While Exists(Select Top 1 * From #databaseList Where statsStatus = 0)
        Begin
 
            /* Build our SQL statement to update stats */
            Select Top 1 @rebuildStatsSQL = 'Use [' + databaseName + ']; ' + 
                                            'Execute sp_updatestats;'
                    , @rebuildStatsID = databaseID
            From #databaseList
            Where statsStatus = 0;
 
            Set @debugMessage = 'Rebuilding Statistics: ' + @rebuildStatsSQL;
 
            If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait;
 
            /* Execute our stats update! */
            Execute sp_executesql @rebuildStatsSQL;
 
            /* Keep track of which databases have been updated */
            Update #databaseList 
            Set statsStatus = 1
            Where databaseID = @rebuildStatsID;
 
        End;
    End;
 
    /* When everything is said and done, make sure to get rid of our temp table */
    Drop Table #indexDefragList;
    Drop Table #databaseList;
    Drop Table #processor;
 
    If @debugMode = 1 RaisError('DONE!  Thank you for taking care of your indexes!  :)', 0, 42) With NoWait;
 
    Set NoCount Off;
    Return 0
End
Go
 
Set Quoted_Identifier Off 
Set ANSI_Nulls On
Go
0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , , , , . Bookmark the permalink.

117 Responses to Index Defrag Script, v3.0

  1. Hi Michelle, Great script and thanks for sharing. I had built a version myself but I find that on VLDBs, dm_db_index_physical_stats is very resource intensive (and hence it takes a while), even with LIMITED scan mode.

    It’s even a bit much on SLDBs (Somewhat large databases).

    What’s your experience with this function? Have you noticed anything yourself?

  2. Hi Michael. I actually run this index defrag on a 1.4TB database every night. I run it with a MAXDOP restriction of 2, and a @defragDelay of 1 minute. It takes about 4 hours for the DMF to finish running, and then about 30 minutes to an hour for the defrags to complete. So yes, it is resource intensive, but I have never run into any locking or blocking problems. I did run into a couple of replication delays when I tried to defrag the current partitions, so I added this into my script (line 325):

    And partition_number <> $partition.myPartitionFunction(GetDate()) — don’t defrag current partition

    That seemed to help with the replication problems. Also, the reason I added the scheduling was to move some of the nonpartitioned indexes on billion-row tables to a weekend-only defrag schedule. This has seemed to help in the amount of time that it takes to complete during the weekdays.

    I hope that helps answer your question! :)

  3. Vic Prahu says:

    Hi Michelle, once again great updates on this new version. I was using he previous IndexDefrag script version wiht minor modifications, one of them was to skip certain tables in 200+ databases, seems like you have covered that in this update.

    One minor finding, when i execute this Sproc with @executesql = 0, and @printCommands = 1 , @printFragmentation = 1, i noticed that the IndexName in results for certain tables is repeated! But the PrintCommands has the correct value for IndexName. I hope this makes sense, if not contact me and I will try to elaorate!

  4. Pingback: Aaron Bertrand

  5. Pingback: Microsoft and DiscountASP.NET news

  6. Pingback: Internet Marketing Email » Blog Archive » Index Defrag Script, v3.0 : SQL Fool

  7. Luke says:

    hi, thank you for the sript, but is there a downloadable version of the script or do i have copy the script from the post? thanks.

  8. John Sansom says:

    Hi Michelle,

    This looks to be an excellent update to an already brilliant database maintenance script. I for one certainly appreciate your efforts and want to say a big thank you to you.

    I’m looking forward to testing this out and I think the inclusion of statistics management will be a great feature.

  9. SDC says:

    Thanks for the contribution to the community! I’m going to put this to work.

  10. Pingback: SQL Server Index Defrag Script: Update | John Sansom - SQL Server DBA in the UK

  11. Jordan Schwartz says:

    Just a thought to get this to run faster on larger databases would be to spawn several instances of this script and have them pull out n number of tables at a time. This way you could have lets say 5 instances working on 50 tables each. That should help reduce the time to complete.

    I just found this script and I must say, its very thorough. I had been running a similar script that set threshold levels for rebuild or reorg but I never took it this far. I am definitely going to use this script. I did however use the “multi-instance” method in my script which sped up the process quite a bit..

  12. Tittan says:

    Hi Michelle..
    I have a problem to compiling this excellent script.

    Server: Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 263
    Incorrect syntax near ‘OBJECT_ID’.

    I run this on (SQL 2005, SP3):

    Microsoft SQL Server 2005 – 9.00.4035.00 (Intel X86)
    Nov 24 2008 13:01:59
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

  13. Hi Michelle,

    Thanks for this script, this is just what I was looking for.

    I have just one issue. When I run this SP I got an error, incorrect syntax near ‘(‘. It seems it does not work when the database is in compatible mode 80 (SQL Server 2000).

    I solved it by replacing the object_id(@tablename) parameter in the dm_db_index_physical_stats line with @tab_id. Then insert two line before this command:
    declare @tab_id int
    set @tab_id=object_id(@tablename)

  14. Derick says:

    Hi Michelle. Thanks for your script. However, I found that the “Rebuild Stat” part always rebuild the first database only. It seems that the script missed a loop to run through all the database in the database list.

    Besides, I found some databases are skipped during the defragmentation. Even though there are indexes with over threshold, they haven’t been defraged.

    Pls help. Thank you.

  15. SuperCoolMoss says:

    Hello Michelle,

    Thanks for the new version. Does the update stats section only run for one database?

    SCM

  16. Pingback: Index Defrag Script Update : SQL Fool

  17. chris crowe says:

    smal bug on line 562

    SELECT TOP 1 @rebuildStatsSQL = ‘Use ‘ + databaseName + ‘; ‘ +

    This should be

    SELECT TOP 1 @rebuildStatsSQL = ‘Use [' + databaseName + ']; ‘ +

    I have a number of web sites which have a database name like “blog.crowe.co.nz”

    This little fix sorts it out.

    Thanks for the script – much appreciated – I am trying (real hard) to learn more about SQL performance and I am planning on reading your blog often.

    ps: I am coming to SQL Pass Summit 2009 in Seattle (all the way from New Zealand)

    chris

  18. Pingback: 5 tâches courantes d’administration de SQL Server 2005 | Admin Réseau - Blog

  19. Thanks, Chris! I’ve never used a period in a database name before.

    Very cool news about the Summit. Looking forward to meeting you there. :)

  20. Pinal Dave says:

    Very nice Michelle,

    Again, it is wonderful script and I have just updated all my production server with this script.

    I am eager to meet you at PASS!

    Kind Regards,
    Pinal

  21. Jason Callas says:

    The square brackets around the USE command also fixes the situation when hyphens are in the database name. I use “-dev” and “-da” on my development servers.

  22. Jason Callas says:

    Make that “-qa”…

  23. Roy says:

    I found a small bug for /* Look up index information */ SELECT @updateSQL
    should add the below in the where clause

    And idl.indexID = ‘ + CAST(@indexID AS VARCHAR(10)) + ‘

    after
    And i.type > 0

  24. Ashish says:

    Thanks Michelle for sharing your script !

    I just have to make two small changes to work it on case sensitive server.
    It would be nice for the community if you include those changes in the script.

    1) Replace capital case “XP_MSVER “ to small case “xp_msver”
    2) Replace capital case “SP_EXECUTESQL” to small case “sp_executesql”

    Ashish

  25. Joshua Jin says:

    In your script logic, you have “If our index is partitioned, we should always reorganize”, can you expain why? Thanks! (Joshua)

  26. @Joshua Hi Joshua. Good question! The reason is that, if you attempt to rebuild a single partition *online*, it will fail. You can rebuild a single partition offline, but that’s never been acceptable in my environments. Alternatively, you can rebuild all partitions online, but again, that negates much of the benefit of partitioning, since most partitions should (hopefully) not need to be rebuilt.

  27. Rebecca says:

    Excellent, Michelle. I have been needing to smarten up my defragger for ages. Stumbled upon this today… the varied parms are perfect, and it works very well on all of my db’s…. VLDB and otherwise. I have already tested it and deployed.

    Thanks very much.

  28. Sven Moderow says:

    Will all fulltext indexes also be defraged? (like ALTER FULLTEXT CATALOG catalog_name REORGANIZE)?

  29. gmamata says:

    Hi Michelle Ufford,

    Can we use this script to defrag SHARE POINT databases in SQL Server 2005?

  30. gmamata says:

    Hi Michelle,

    I’m planning to use this Script to defrag Share point databases. Can we use this script to defrag SHARE POINT databases in SQL Server 2005?

  31. Thanks Michelle,

    I needed to move away from the defrag maintenance plan (created huge log files to ship) to a more analytical script. Yours is awesome but I don’t see any logic for sorting in the TempDB. Wouldn’t this minimize the i/o on the mdb’s partition when performing the rebuild online? Knowing that rebuilding the index will be logged, does sorting it in TempDB minimize the extent of the log growth?

  32. Rick says:

    getting a couple of errors

    1. When executing script get error
    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table ‘SP_EXECUTESQL’. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

    2. exec dbo.dba_indexDefrag_sp
    Could not find stored procedure ‘XP_MSVER’.

    Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table ‘SP_EXECUTESQL’. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

  33. @Sergio I’ll be adding options for sorting in TempDB in future versions. There’s several benefits in using Sort_In_TempDB = On, including shorter defrag durations, less impact on user transactions, and less logging (assuming TempDB is using simple recovery and your database is using full).

  34. @Rick what version of SQL Server are you using, including service pack?

  35. mstjean says:

    Nice script… but I’m gonna hafta stare at it for a while.

    I didn’t see it–but it may be there– do you have a cutoff to prevent rebuild/reorg if pagecount is below some threshold? I (somewhat arbitrarily) set minimum pagecount to 10 via a parm to prevent R/R. Recently I saw a post from Gila where she indicated it’s not worth it to R/R unless pagecount > IIRC 1000 pages (the one link I *didn’t* save)

    I see you are ordering the rebuild/reorg so the most fragmented get done first; I’ve done a variant of that where I order by (pagecount * fragmentation) desc. If I am limited for time, I’d rather rebuild/reorg a table consisting of 100000 pages at 30% fragmentation than a table with 10 pages at 90% fragmentation.

    How about a parm to set “max minutes to run”; set @startdt at the top, and before any major operation confirm “@maxmins=0 or getdate() <= dateadd(minute,@maxmins,@startdt)” … granted this won’t be exact; you’d just prevent starting a new operation after @maxmins minutes. If you have a small window of time per day to do maintenance you could schedule this every day and take care of the worst offenders without worrying about it running wild.

  36. @mstjean Thank you for your suggestions. Yes, I do use a minimum page count of 8 (1 extent)… I haven’t yet explored limited it to page counts that high (i.e. 1000), mainly because those defrag so quickly anyway. I’m also considering a max time restriction for the next version of the script, too.

  37. Rick says:

    Michelle,

    We are using SQL Server 2005 SP3 Enterprise Editon.

    thx,
    Rick

  38. Mike F says:

    I’ve been using this script for a couple of months and thoroughly love it. But (there’s always a “but”, right?), I have encountered some situations where the entire job bombs because the current index being rebuilt is a deadlock victim. Have you given any thought to adding a TRY/CATCH block around the execution piece, perhaps logging the error for the current index, then continuing on with the job normally? Or is it your intent that this situation is handled differently?

    Thanks!

    -Mike F

  39. Hi Mike,

    Thanks for the feedback! That’s a good idea and something I’ll consider for future versions. :)

    Regards,

    Michelle

  40. Hi Rick,

    Is your server case-sensitive? You could possibly have to make those commands lowercase.

    Regards,

    Michelle

  41. Vincent says:

    Great script Michelle, but I’m wondering why you don’t check sys.dm_db_index_usage_stats prior to rebuilding. Any index that is not used should not show up in that DMV, and there is no reason to rebuild indexes that are unused, right?

    Vince

  42. @Vincent thanks for your comment. Good point! Yes, the logic of identifying which indexes to rebuild is something I’m looking at in the upcoming version. :)

  43. Vincent says:

    Great, I added it using a simple inner join to sys.dm_db_index_usage_stats.
    I’ll keep an eye out for how you add that logig.
    I’m currently [trying to] add logic to check the version using SELECT CONVERT(int, SERVERPROPERTY(‘EngineEdition’)) … 3 being Enterprise ed. This way, the person running or automating the proc won’t have to know the edition – the proc will know and choose to use online or not appropriately.

  44. Vincent says:

    Never mind, I see you have edition logic. Feel free to delete my pollution. ;-)

  45. Karl F says:

    Excellent! Thank you for sharing!

    SharePoint 2007 SP2 added a proc that rebuilds with fill factor 80 that seems to be the current general starting point for SharePoint – but the SPS proc didn’t include any logic based on current fragmentation. So now I know why our TRN log backups seem larger than they should be…

    Now that target fill factor is set your script should be able to perform ongoing maintenance. Testing in Dev now.

    Like ‘mstjean’ I’ve always noticed that indices with low page counts naturally can have high fragmentation % values even when fully optimzed. I am wondering what logic might keep these from unnecessary rebuilding – even though they’re small and impact is low. I’m sure someone else out there has input on this one.

    Also noticed the SharePoint proc_DefragmentIndices doesn’t seem to sort in tempDB (yet) :) .

    Looking forward to future revisions!

    -Karl

  46. Bill says:

    Great script. I modified slightly to use Sort_In_TempDB = On. One issue I do encounter from time to time is a failure when rebuilding clustered indexes on views. For some strange reason, it behaves as if the SET QUOTED_IDENTIFIER ON was never set. The failure message looks like this:

    ALTER INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods

    When I print the list of commands and do not execute, sure enough I find an indexed view in the list. When I copy that command to a query session and run it, it works fine, and then running the sp as a whole after that will work. However, whenever an index on a view needs to be rebuilt, the procedure will fail when it gets to that statement. Strange behaviour, especially since we can clearly see Quoted_Identifier is properly set at the beginning of the procedure.

    Anyone else out there encounter this?

    Cheers,

    Bill

  47. Pingback: links for 2009-10-20 - sashidhar.com

  48. Jason Callas says:

    Great tool…have 1 question and 1 feature request

    1) Can I use the exclude table to exclude an entire database or table? A quick read through the code seems that I need to specify actual indexes.

    2) Detect if a database is in read-only mode and automatically skip it.

    As you can see, my feature request is related to my exclude question.

    Thanks.

  49. Bill says:

    Ok, I figured out why this proc was failing on Indexed Views. Despite having SET QUOTED_IDENTIFIERS ON inside the proc, it is ignored in favor of the session option when the proc is created which is saved in the database. For some reason, when I ran the script on my servers, the session option was SET QUOTED_IDENTIFIERS OFF. Once I recreated explicitly setting SET QUOTED_IDENTIFIERS ON before the procedure was created, everything works fine.

  50. Aq says:

    newbe :(

    what does this error mean?

    Msg 208, Level 16, State 1, Line 5
    Invalid object name ‘sys.tables’.
    Msg 170, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 263
    Line 263: Incorrect syntax near ‘OBJECT_ID’.

  51. lamingus says:

    Wanting to run your script however have same error as Newbe

    Msg 208, Level 16, State 1, Line 5
    Invalid object name ’sys.tables’.
    Msg 170, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 263
    Line 263: Incorrect syntax near ‘OBJECT_ID’.

  52. @Aq and @lamingus – what version of SQL Server are you using? What compatibility level are you using? Those error messages sound suspiciously like SQL Server 2000. This script is only intended for SQL Server 2005 and higher.

  53. Pingback: SQL 2008 Express: SQL Agent « My life as a DBA.

  54. Madhavarao says:

    Thanks for this Useful script .. which is really handy, helpful for dumb fellows like me

  55. lamingus says:

    Using SQL 2005 as far as I know; Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure ‘dba_indexDefrag_sp’.

    All I really need is to run a script to rebuild all indexes for a single data base. Our support staff has customer data set up on SQL for testing purpose. We basically need to verify that rebuilding the indexes will correct the performance problems a few customers are reporting.

    Although this script looks quite eloquent we need a simpler down and dirty tool that as I say only runs for the intended database and rebuilds all indexes in the selected database. The one we are working with right now has quite a lot of 60% to 99 % fragmentation.

  56. Matt says:

    I am testing this in a dev environment using and Adventureworks db. I have a query that tells me which databases / tables need to be defagged. For example, it states that AdventureWorksLT is at 99%.

    When I run your script agains the AWLT db, and then re-run my query that returns defrag statistics, shouldn’t the number be lower than 99? I guess I’m having trouble understanding how I know your script worked.

    Any help is much appreciated.

    Thanks,
    Matt

  57. Matt says:

    Oops I meant defragged on my comment above. Didn’t mean to offend anyone.

  58. Martin says:

    Hi Michelle,

    Thanks for your script, it’s exactly what I was looking for.
    Although I’m not an expert in SQL scripting I can interpret most of it.

    But wherever I run it, either 2005/2009 SE/EE it drops after:

    [Msg 50000, Level 16, State 0, Line 50
    One or more tables already exist. Please drop or rename before proceeding.
    Procedure dba_indexDefrag_sp dropped]

    The indexdefraglog table just shows ‘Null’ in all rows. I’m using the default for the variables.
    What am I doing wrong.

    cheers
    Martin

  59. Pingback: SQL 2008 Express: SQL Agent | Darryl Marshall

  60. @Matt the script will only defrag any index with 8 or more pages, which follows Microsoft’s best practices recommendation. I don’t have the AdventureWorksLT database handy, but I would guess that most of those indexes have less than 8 pages, which is probably why you’re not seeing any change after running the defrag script.

  61. @Martin You’re getting those errors because the tables already exist, which means some version of the script has already been executed once. In my script, I do not automatically drop the tables, just to err on the safe side (i.e. you want historical defrag information). To update to the latest version, just comment out the section that creates the tables that already exist (probably just dba_indexDefragLog, since dba_indexDefragExclusion is new in this version). After that, you can call the script with “Execute dba_indexDefrag_sp;” (uses all default parameters).

  62. Martin says:

    Hi Michelle,

    Thanks for the answer Michelle.
    I’m running the script against several heavily fragmented databases. I took a a script from Brent Ozars Blog and got that result.
    Now I Imagined that the tables could be the cause so I deleted them but still the result is the same. I realized later be running the newly created stored procedure I could defragment the indexes.

    Isn’t it the intend of the scipt to run it right away or do you have to run the SP separately?

    Thanks

  63. Olek says:

    I suggest script should skip disabled indexes.

  64. Jarno says:

    Hi Michelle,

    Thanks for the script, it looks great! But I can’t seem to manage to run the script.

    Since the very first time I tried it, it only says this: (yes that includes the first time too)

    dba_indexDefragLog Table Created
    dba_indexDefragExclusion Table Created
    Procedure dba_indexDefrag_sp dropped

    After that it doesn’t do anything at all.

    I tried it on 2 different servers in SQL Server Management Studio:
    Windows Server 2008,
    64bit based,
    SQL Server 2008 SP 1
    Microsoft SQL Server Management Studio 10.0.2531.0
    Microsoft Analysis Services Client Tools 10.0.1600.22
    Microsoft Data Access Components (MDAC) 6.0.6002.18005
    Microsoft MSXML 3.0 4.0 5.0 6.0
    Microsoft .NET Framework 2.0.50727.4200

    Things I changed in the script are:

    @DATABASE VARCHAR(128) = ‘WSS_Content_5a0475f003cb474da06f105ad8877434′ — instead of NULL, only want to test it on this database
    @printCommands BIT = 1 — instead of 0
    @printFragmentation BIT = 1 — instead of 0
    @debugMode BIT = 1 — instead of 0

    Thank in advantage.

  65. Jarno says:

    make that Windows Server 2008 SP2

  66. Jim says:

    Hi Michelle, I am using your greate script on a couple databases and i works like a charm until I lunch on my main DB. The script runs through the indexes until this error: “Msg 8152, Level 16, State 13, Line 1
    String or binary data would be truncated”
    I have run it several times, also including debug and print statements, so I can see the last running statement. BUT I can´t find out which statement gives me the error or where it comes from.
    Can you help or does any one else have a clue ?

  67. EstebanD says:

    Hi Michelle,

    Thank you for sharing your script and experiences.

    My question is probably dumb…

    Why when running this script again after rebuilding/reindexing still proposes the same number of indexes and the same fragmentation?

    I even manually rebuilt all indexes in the DB and the executed this SP and again it didn’t change.

    Also some fragmentation numbers seem high after running all these. 53.84, 77.77

    Thank you!

  68. Duncan McCormack says:

    Great stuff Michelle.

    I’m wondering if you’ve ever looked at the MS T-SQL that they have online for defraging WSUS databases.

    What I like about the code, is that it defrags based on fragmentation levels.

    Perhaps that could be of use to you in your SQL?

    Here’s the link:
    http://go.microsoft.com/fwlink/?LinkId=87027

    Whaddya think?

  69. Duncan McCormack says:

    OK, now that I’ve actually *read* your code – I see ya doin that! :)

  70. @Jarno you now want to open up a new query window and execute *only* this:

    Exec dbo.dba_indexDefrag_sp
    @DATABASE VARCHAR(128) = ‘WSS_Content_5a0475f003cb474da06f105ad8877434′
    , @printCommands BIT = 1
    , @printFragmentation BIT = 1
    , @debugMode BIT = 1

  71. @Esteban This script won’t affect the number of indexes that exist. If the fragmentation numbers are not changing, look at the page count in your index. If it’s a low number, you’re not going to see fragmentation numbers change. Here’s what Microsoft has to say about it: “Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.”

  72. @Jim I’m wondering if it’s not an issue with the logging. Do you have any really long database, table, or index names?

  73. Pingback: How do -you- install SQL Server? (Part 3) | SQL Server Blog - StraightPath Solutions

  74. EstebanD says:

    Hi Michelle,

    Thank you for the detailed explanation.

  75. Jarno says:

    Thanks for the reply Michelle.

    The code you gave didn’t work with copy/paste, but with some research in the database I made it working.

  76. Chris Reeder says:

    Is there a version of this script that will work with SQL 2000?

    BTW: It works great on our other SQL servers!

  77. mamata says:

    Is it safe to create this procedure in Master database as it creating couple of tables?

    thanks

  78. rgauch says:

    Been running this for a while, but came across error on one of our servers stating reorganization failed because page-level locks are disallowed. Overcame by adding exists clause to reorganize criteria with a query to sys.sysindexes for the index and lockflags = 0. Indexes that disallow page level locks now get rebuilt instead of reorganized.

    Awesome script by the way.

  79. Carol Noel says:

    Hi Michelle:
    This is fantastic. I have been testing and will deploy. A few things –

    1) Noticed same minor problem posted above by Vic Prahu on Wed, 24th Jun 2009 2:01 pm. Seems to happen when a table has more that one index selected for defrag.

    2) can this script be used for databases on a SQL2K5 box with compatability level 80 ?

    3) I modified code a bit to exclude processing of an entire database. Modified the exclusion table a bit as well to handle this.

    Looking forward to next enhancements.

  80. dhjackal says:

    Hi Michelle,

    Excellent procedure and thanks for sharing it with us.

    I’ve used it as the basis for all of my index defragmentation management across all of my production servers. I call it slightly differently though. I use a “wrapper” procedure (usp_ProcRunner) to call all of my procedures / functions and I pass a list of databases that I want the proc to run against into this which subsequently passes it down to the called proc.

    i.e.

    EXEC DBA_Development.dbo.usp_ProcRunner
    @Databases = ‘database1, database2′ — Can be USER, SYSTEM, ALL or a list of databases
    , @ProcCall = ‘EXECUTE DBA_Development.dbo.usp_IndexDefrag @minFragmentation = 5, @rebuildThreshold = 30, @maxDopRestriction = 2′
    , @DebugMode = ‘N’
    GO

    I too have opted to include the SORT_IN_TEMPDB option to the rebuild index statement.

    I have a couple of queries.

    1) Any reason why you don’t just update the statistics for the objects that are reorganised rather than all the objects in the db?

    2) You don’t include a FILL_FACTOR in your rebuild statement. Any particular reason for this?

  81. Gmamata says:

    Can we create these tables & procedures in Master database? Or do we need to create a separate Admin database & create the above tables & procedures?

    what is the best method

    thanks

  82. @Gmamata yes, you can create the table and proc in the Master database, but I typically recommend the creation of a database for DBA-related administration. I typically call mine something like “DBA_Sandbox.”

  83. @dhjackal sp_updatestats will only update the statistics that need it. So it’s just as easy and possibly more of a catch-all to run it for all objects in the database.

    The reason I don’t include a FILL FACTOR is I can’t think of an occasion where I would ever want to bulk-change the FILL FACTOR setting for all indexes. Right now, I consider the script fairly harmless, but I would consider that a potential accident waiting to happen if someone were not aware of what he/she was doing, set the value, and ran it for all databases and tables.

  84. @Chris Sorry, this version only works with SQL Server 2005 and above because of its extensive use of DMV’s.

  85. @Carol I haven’t been able to duplicate the issue you mention, although I’m not entirely sure I understand what’s happening, either. Any input would be appreciated.

    I don’t think it’ll work with a database compatibility level = 80.

    Thanks for your comments! :)

  86. Gmamata says:

    Hi Michelle,

    Is this latest version of the script that has addressed some of the issues by users (like Derick and SuperCoolMoss pointed about the stats rebuild feature in your defrag script will only complete for one database.)?

    Thank you

  87. Dale Langham says:

    Hi,
    Not sure where I had this script from, but I have been using it for ages now, basically it defrags indexes over a period of 7 days to spread the load, but for some reason it doesn’t like multiple schemas, as it adds dbo to all tables found etc… any ideas to make this work for all schemas would be great:

    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(‘dbo.DBMaint_Task’) AND xtype = ‘U’)
    BEGIN
    DROP TABLE dbo.DBMaint_Task
    END
    GO

    CREATE TABLE dbo.DBMaint_Task(
    MaintTaskId int NOT NULL IDENTITY(1, 1),
    Interval int NOT NULL,
    Command nvarchar(4000) NOT NULL,
    ItemWork numeric(10,2) NOT NULL,
    CompletedDatetime datetime NULL
    CONSTRAINT PK_DBMaint_Task PRIMARY KEY CLUSTERED(MaintTaskID)
    )
    GO

    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(‘dbo.p_maint_ScheduleMaintTasks’) AND xtype = ‘P’)
    BEGIN
    DROP PROCEDURE dbo.p_maint_ScheduleMaintTasks
    END
    GO

    CREATE PROCEDURE dbo.p_maint_ScheduleMaintTasks
    @PeriodLength int, – Number of intervals in a period
    @IndexRebuildThreshold int = 10 – Maximum LogicalFragmentation value allowed before an index is rebuilt
    AS

    DECLARE @WorkPerIntervalGoal numeric(10,2)
    DECLARE @CurrentInterval int
    DECLARE @RowsUpdated int
    DECLARE @MainTaskId int

    SET NOCOUNT ON

    IF @PeriodLength = @IndexRebuildThreshold
    AND
    ObjectId OBJECT_ID(‘dbo.DBMaint_Task’)

    – If we rebuild a clustered index, we don’t need to rebuild
    – any nonclustered indexes on the same table
    DELETE FROM
    rc1
    FROM
    #ReindexCandidate rc1
    INNER JOIN
    #ReindexCandidate rc2
    ON(
    rc1.ObjectId = rc2.ObjectId
    )
    WHERE
    rc2.IndexID = 1 — IndexID = 1 means the clustered index
    AND
    rc1.IndexID 1

    TRUNCATE TABLE dbo.DBMaint_Task

    INSERT INTO dbo.DBMaint_Task(
    Interval,
    Command,
    ItemWork)
    SELECT
    -1 AS Interval,
    N’DBCC DBREINDEX(”’ + USER_NAME(OBJECTPROPERTY(rc1.ObjectId, ‘OwnerId’)) + N’.’ + rc1.ObjectName + N”” +
    CASE IndexId WHEN 1 THEN N” ELSE N’, ”’ + IndexName + N”” END + N’)’ AS Command,
    (SELECT SUM(dpages) FROM dbo.sysindexes si1 WHERE id = rc1.ObjectId) AS ItemWork
    FROM
    #ReindexCandidate rc1

    – Find all tables that we’re not doing a clustered index rebuild on
    INSERT INTO dbo.DBMaint_Task(
    Interval,
    Command,
    ItemWork)
    SELECT
    -1 AS Interval,
    N’UPDATE STATISTICS [' + USER_NAME(uid) + N'].[' + so1.name + N'] WITH FULLSCAN’ AS Command,
    (SELECT SUM(dpages) FROM dbo.sysindexes si1 WHERE id = so1.id) * 0.40 AS ItemWork
    FROM
    dbo.sysobjects so1
    LEFT OUTER JOIN
    #ReindexCandidate rc1
    ON
    (
    rc1.ObjectId = so1.id
    AND
    rc1.IndexId = 1
    )
    WHERE
    so1.xtype = ‘U’
    AND
    rc1.ObjectId IS NULL
    AND
    (SELECT SUM(dpages) FROM dbo.sysindexes si1 WHERE id = so1.id) 0
    AND
    so1.id OBJECT_ID(‘dbo.DBMaint_Task’)

    DELETE FROM
    dbo.DBMaint_Task
    WHERE
    ItemWork = 0

    – Break all ties by ItemWork
    – This algorithm is arbitrary, but the amount of estimated work it adds is trival
    SET @RowsUpdated = 1
    WHILE @RowsUpdated 0
    BEGIN
    UPDATE
    mt1
    SET
    ItemWork = ItemWork + 0.01
    FROM
    dbo.DBMaint_Task mt1
    INNER JOIN
    (
    SELECT
    MAX(MaintTaskId) AS MaintTaskId
    FROM
    dbo.DBMaint_Task
    GROUP BY
    ItemWork
    HAVING
    COUNT(*) > 1
    ) mt2
    ON
    (
    mt1.MaintTaskId = mt2.MaintTaskId
    )

    SET @RowsUpdated = @@ROWCOUNT
    END

    SET @CurrentInterval = 1
    WHILE @CurrentInterval = mt1.ItemWork
    AND
    mt2.Interval = -1
    ) <= @WorkPerIntervalGoal

    SET @RowsUpdated = @@ROWCOUNT

    IF @RowsUpdated = 0
    BEGIN
    UPDATE
    mt1
    SET
    Interval = @CurrentInterval
    FROM
    dbo.DBMaint_Task mt1
    WHERE
    mt1.ItemWork = (
    SELECT
    MAX(mt2.ItemWork)
    FROM
    dbo.DBMaint_Task mt2
    WHERE
    mt2.Interval = -1
    )
    END

    SET @CurrentInterval = @CurrentInterval + 1
    END

    SET NOCOUNT OFF

    GO

    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.p_maint_ExecuteMaintTasks') AND xtype = 'P')
    BEGIN
    DROP PROCEDURE dbo.p_maint_ExecuteMaintTasks
    END
    GO

    CREATE PROCEDURE dbo.p_maint_ExecuteMaintTasks
    @PeriodLength int = 7, – Number of intervals in a period
    @IndexRebuildThreshold int = 10, – Maximum LogicalFragmentation value allowed before an index is rebuilt
    @ForceInterval int = NULL, – Manually set the current interval rather than calculating it from the date
    @ForceRebuildTaskList int = 0, – If non-zero, force a rebuild of the task list
    @RebuildTaskListInterval int = 1 – The interval in which to automatically rebuild the task list
    AS

    DECLARE @CurrentInterval int
    DECLARE @TaskCount int
    DECLARE @Command nvarchar(4000)
    DECLARE @MaintTaskId int

    SET NOCOUNT ON

    IF @PeriodLength < 1
    BEGIN
    RAISERROR(N'@PeriodLength must be greater than zero.', 16, 1)
    RETURN
    END

    IF @ForceInterval IS NOT NULL
    BEGIN
    IF @ForceInterval @PeriodLength
    BEGIN
    RAISERROR(N’If not null, @ForceInterval must be less than or equal to @PeriodLength.’, 16, 1)
    RETURN
    END
    END

    IF @RebuildTaskListInterval > @PeriodLength
    BEGIN
    RAISERROR(N’@RebuildTaskListInterval must be less than or equal to @PeriodLength.’, 16, 1)
    RETURN
    END

    IF NOT @IndexRebuildThreshold BETWEEN 0 AND 100
    BEGIN
    RAISERROR(N’@IndexRebuildThreshold must be between 0 and 100.’, 16, 1)
    RETURN
    END

    SELECT
    @TaskCount = COUNT(*)
    FROM
    dbo.DBMaint_Task
    WHERE
    CompletedDatetime IS NULL

    IF @ForceInterval IS NULL
    BEGIN
    SELECT
    @CurrentInterval = ((YEAR(GETDATE() – 59) * 365 + DATEPART(dy, GETDATE() – 59)) % @PeriodLength) + 1
    END
    ELSE
    BEGIN
    SELECT
    @CurrentInterval = @ForceInterval
    END

    IF @TaskCount = 0 OR @ForceRebuildTaskList 0 OR @CurrentInterval = @RebuildTaskListInterval
    BEGIN
    EXEC dbo.p_maint_ScheduleMaintTasks
    @PeriodLength = @PeriodLength,
    @IndexRebuildThreshold = @IndexRebuildThreshold
    END

    DECLARE csr_Task CURSOR FAST_FORWARD FOR
    SELECT
    MaintTaskId,
    Command
    FROM
    dbo.DBMaint_Task
    WHERE
    Interval = @CurrentInterval
    AND
    CompletedDatetime IS NULL

    OPEN csr_Task

    FETCH NEXT FROM csr_Task INTO @MaintTaskId, @Command
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT CONVERT(nvarchar(30), GETDATE(), 121) + N’: Executing “‘ + @Command + N’”‘
    EXEC(@Command)
    PRINT ”

    UPDATE
    dbo.DBMaint_Task
    SET
    CompletedDatetime = GETDATE()
    WHERE
    MaintTaskId = @MaintTaskId

    FETCH NEXT FROM csr_Task INTO @MaintTaskId, @Command
    END

    CLOSE csr_Task
    DEALLOCATE csr_Task

    SET NOCOUNT OFF
    GO

  88. Dale Langham says:

    After running the script, you should run the following command to analyze and re-index the entire database:
    EXEC dbo.p_maint_ExecuteMaintTasks @PeriodLength = 1
    By passing @PeriodLength = 1, the procedure will perform all maintenance tasks in a single execution rather than dividing them up across multiple executions.
    To set up recurring maintenance, you need a SQL Agent job that runs p_maint_ExecuteMaintTasks on a regular basis (if using the default settings, daily). No parameters are necessary to run this stored procedure with default settings. Using default settings, the procedure will divide all index maintenance tasks over a 7-day period with a near equal as possible load each day.
    The stored procedure breaks the maintenance into two main steps: scheduling and execution. The scheduling phase runs DBCC SHOWCONTIG WITH FAST to determine which indexes need to be defragmented and approximately how much work this will be. It applies several layers of logic including a fragmentation threshold (default 10% logical fragmentation) and eliminating duplicate defrag operations due to non-clustered/clustered indexes on the same table. For indexes where defragmentation isn’t needed, it schedules an update statistics operation.
    The scheduling phase also estimates the approximate relative cost of each operation and attempts to assign each operation to a specific interval within the maintenance period. By default, the maintenance period is 7 days long, and each period is one day. The scheduling phase runs once per period while the execution phase runs once per interval. (So by default, scheduling occurs weekly while maintenance execution occurs daily.) The results of the scheduling phase are stored in the DBMaint_Task table.

  89. Pingback: links for 2010-02-03 | Jonathan Gardner

  90. Aron Riktor says:

    The script is wonderful and I use it for server maintenance everywhere. However, I recently introduced some new indexes in a database. I then found that the index name was too long (or allocated variable too small). Debug output (removed identifiable names):

    Alter Index [_dta_index_CustomerOrderLine_22_608721221__K13_K69_4_34_146_150_152_154_165] On [...].[...].[CustomerOrderLine] Rebuild With (Online = On)
    Updating our index defrag status…
    Picking an index to beat into shape…
    Looking up the specifics for our index…
    Msg 8152, Level 16, State 13, Line 1
    String or binary data would be truncated.

    The cause is a long index name implemented on strength of the Database Engine Tuning Advisor’s advice. I have briefly tried to find the exact assignment that fails, but will probably use a shorter index name anyway.

  91. liliek says:

    HI Michelle,

    Great script, but how about SQL Server 2000 version,
    do you have script like this or reference for SQL Server 2000??
    please advice.

    thanks
    Liliek

  92. Pingback: New Index Defrag Script « Sergessqlnotes's Blog

  93. Pingback: rusanu.com » Dealing with Large Queues

  94. Stuart says:

    A question: is it a good idea to defrag clustered index first, before defragging non-clustered indexes (on the same table)?

    I added this modification to pick clustered indexes first…

    /* Grab the most fragmented index first to defrag */
    SELECT TOP 1
    @objectID = objectID

    FROM #indexDefragList
    WHERE defragStatus = 0 — not defragged yet
    — Choose clustered indexes first
    ORDER BY case index_type_desc
    when ‘CLUSTERED INDEX’ then 1 else 0 end desc,
    fragmentation DESC;

  95. Julio D says:

    Hi,
    I am reading and learning about Index Defrag, and I have a question about what issues should I encounter if I schedule this script on a 24/7 enviroment, will I encounter any locks, how often should I schedule to run. The application is on a system that don’t have any DBA onsite and no one will be checking the db, but it is one that has a lots of inserts and it is used for reporting. Application has been running for 3 years database size 2.2GB . It is used on an application that logs manufacturing production and process information.

    I am trying to create maintenance plans but i want to be careful about affecting the operation but again at the same time it needs a maintenance plan and the fact no one monitor the dbs.

    Thanks

    Julio D

  96. Portrman says:

    Noticed a problem(not sure why it would be by design) – If a table has any LOB columns, none of the indexes will be rebuilt. They will only be reorganized.

    For example:
    CREATE TABLE [dbo].[Event](
    [EventKey] [bigint] NOT NULL,
    [MessageID] [varchar](70) NULL,
    CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
    (
    [EventKey] ASC
    )WITH () ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_Event_MessageID] ON [dbo].[Event]
    (
    [MessageID] ASC
    )WITH (<taken out) ON [PRIMARY]

    The clustered index on EventKey should be reorganized. The non-clustered on MessageID should be rebuilt online, but will NOT be.

    This is because the script checks the TABLE for any LOBs and not the specific Index about to be worked on.

    I have not figured out exactly how to change this LOB check query

  97. Portrman says:

    Oh, not sure if it matters, but on the Event table above, there should also be a column of type XML called …. EventXML

  98. gmamata7 says:

    Michelle Ufford,

    Sorry to ask you again!! but I want to make sure that “can we use this Index defrag script for SHARE POINT databases”

    thank you

    Mamata

  99. gmamata7 says:

    Michelle Ufford,

    how about defragging indexes that have page_count>1000 . Because I read in several forums that it’s best practice to defrag indexes that have page_count>1000

    defragging indexes that have page_count<1000, will actually have no effect as I understood

    Please advice

  100. garyreeds says:

    Hi Michelle,

    Using this script will it be possible to index defrag for selected Tables in database

    Instead of all tables in database & instead of just one table in a database?

    I want to index defrag for 10 main large table on Saturday & the remaing small tables on Sunday?

    Can we pass multiple tables name instead of one table?

    Thanks

  101. BlueCollarCritic says:

    Michelle,

    I’ve looked at this before (many months ago) and I had problems them and gave up but now I’m revisiting this and am again unable to get it to work . I have tested it both on a fully compliant SQL Server 2005 DB and one that is set to a compatability mode of 2000 and while the Onject_ID() calls a failure on the DB with teh compatability mode of 2000, when I replace that with the Table ID the thing runs to completon but does not actual work and I have confirmed that the Table in the DB has fragmentation that meet the minimums liste in the script.

    Are there any requirements perhaps not explicitly listed like you have to be using Enterprise edition or something????

    Thanks

  102. Paul White NZ says:

    Hey Michelle,

    Do you have any plans to update this to incorporate heap defragmentation using the new SQL Server 2008 syntax ALTER TABLE dbo.Heap REBUILD; …?

    Great script, I never tire of recommending it.

    Paul

  103. gary says:

    Can we use this Index defrag stored procedure for Share point databases?

    We have a very large Content database of size 150 GB

    Thanks do much

  104. Steve says:

    I just wanted to thank you for sharing this.

    -Steve

  105. evil lady Dia says:

    Michelle,

    Thank you very much for the script. Just what I needed to get a small DB tuning to work just right.

    Dia

  106. Hi everyone. Sorry for accidentally spamming you earlier when I approved a bunch of comments. :(

    I’ve just posted a significant update to my index defrag script. You can find the latest version, along with a list of the changes, here: http://sqlfool.com/2010/04/index-defrag-script-v4-0/

  107. pjdiller says:

    Love it. Having trouble with it on SQl 2005 Ent. 64-bit SP3, in SQL_Latin1_General_CP850_BIN2 collation (don’t know if that’s my problem),

    I fixed the case-sensitivity problems as described already, but for whatever reason, it skips the database I’m most interested in defragmenting. I haven’t seen an error message, and there is absolutely work to do in the DB. It’s name is simple, 3 letters, that’s it.

    Any ideas?

  108. Donna says:

    Thanks for the script. It is very clever. I did have one database with a hyphen in the name, so I modified line 477 to put brackets around the name.

    From [' + DB_Name(@databaseID) + '].sys.partitions

  109. Pingback: Free T-SQL Database Maintenance Scripts | SQL Aloha

  110. Pingback: The Kinetic Serendipity of the Written Word « NULLgarity

  111. Steven says:

    Hi everyone, I am an IT Admin at a small office. I have little to no dba experience and I found this script to help defrag and rebuild index if necessary. Thank you Michelle for sharing such a wonderful script. However, when I paste this script and tried to run it, I get these errors.

    Caution: Changing any part of an object name could break scripts and stored procedures.
    Caution: Changing any part of an object name could break scripts and stored procedures.
    Caution: Changing any part of an object name could break scripts and stored procedures.
    dba_indexDefragLog Table Created
    dba_indexDefragExclusion Table Created
    Msg 2714, Level 16, State 4, Line 43
    There is already an object named ‘PK_indexDefragStatus_v40′ in the database.
    Msg 1750, Level 16, State 0, Line 43
    Could not create constraint. See previous errors.
    Msg 2714, Level 16, State 3, Procedure dba_indexDefrag_sp, Line 834
    There is already an object named ‘dba_indexDefrag_sp’ in the database.

    It always runs the first time creating the tables only, then reports that it has been completed. However, I know there are a lot of fragmentation on my database. Any help is greatly appreciated. I need to get this to run asap for everyone in the office is complaining that it is slow.

  112. mike says:

    Many, many thanks. I’m what some would call in involuntary DBA, so jewels like this are worth more than gold!

    My God bless you for your efforts!

  113. Brad Griffin says:

    Hi, thanks for this script. Been looking for something like this for ages. Up u ntil now have been running manual routines.

    Again, thanks

    Brad

  114. Pingback: Best Practices – A must for all SQL DBA’s | sqlsolutions360

  115. Pingback: Best way to rebuild all indexes on all tables in SQL Server 2005 - Just just easy answers

  116. Pingback: Less Than Dot - Blog - Awesome

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>