Automated Index Defrag Script

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

So after much delay, here’s the latest and greatest version of my index defrag script.

A summary of the changes:

  • Added support for centralized execution. Option to defrag indexes for a single database from another database, or for all non-system databases.
  • Consolidated Enterprise and Standard versions of the script with new @editionCheck variable.
  • Added parametrization for MaxDop restrictions during rebuilds; validates that the value does not exceed the actual number of processors on the server.
  • Changed minimum fragmentation default value from 10 to 5 to match BOL recommendations.
  • Limited defrags to objects with more than 8 pages.
  • Added a debug option to give a little more insight into what’s happening and to assist with troubleshooting.
  • Updated logic for handling partitions and LOBs.

And a couple of notes and explanations:

Don’t you know you can just pass NULL to sys.dm_db_index_physical_stats for the DatabaseID?
Yes, I realize you can do this. But I don’t want to defrag the system databases, i.e. tempdb, so I opted to handle it this way instead.

How long will this take?
It depends. I don’t necessarily recommend running it without specifying a database; at least, not unmonitored. You *can* do that, but it could take a while. For example, to run sys.dm_db_index_physical_stats for all databases and tables, totaling 2TB, took me 4.5 hours; that doesn’t even count the actual defrags.

Where should I put this?
It’s up to you. If you have a database for items like centralized maintenance or scratch tables, that may be a good place for it. If you prefer, you could also put this in each individual database and call it locally, too. I would not put this in the master or msdb databases.

This is pretty close to a complete rewrite, so please let me know if you encounter any bugs. And now… the code!

If Not Exists(Select [object_id] From sys.tables Where name = N'dba_indexDefragLog')
Begin
    -- Drop Table dbo.dba_indexDefragLog
    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
        , durationSeconds   int                 Not Null
        Constraint PK_indexDefragLog Primary Key Clustered (indexDefrag_id)
    )
 
    Print 'dba_indexDefragLog Table Created';
End
 
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 */
    , @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 */
As
/*********************************************************************************
    Name:       dba_indexDefrag_sp
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    Defrags all indexes for one or more databases
 
    Notes:
 
    CAUTION: TRANSACTION LOG SIZE MUST 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.
 
      @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
 
    Called by:  SQL Agent Job or DBA
 
    Date        Initials	Description
    ----------------------------------------------------------------------------
    2008-10-27  MFU         Initial Release for public consumption
    2008-11-17  MFU         Added page-count to log table
                            , added @printFragmentation option
    2009-03-17  MFU         Provided support for centralized execution, 
                            , consolidated Enterprise & Standard versions
                            , added @debugMode, @maxDopRestriction
                            , modified LOB and partition logic                            
*********************************************************************************
    Exec dbo.dba_indexDefrag_sp
          @executeSQL           = 0
        , @minFragmentation     = 80
        , @printCommands        = 1
        , @debugMode            = 1
        , @printFragmentation   = 1
        , @database             = 'AdventureWorks'
        , @tableName            = 'AdventureWorks.Sales.SalesOrderDetail';
*********************************************************************************/																
 
Set NoCount On;
Set XACT_Abort On;
Set Quoted_Identifier On;
 
Begin
 
    If @debugMode = 1 RaisError('Dusting off the spiderwebs 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
            , @partitions           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);
 
    /* 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)
    );
 
    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 = 5.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';
 
    /* 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
    From sys.databases
    Where name = IsNull(@database, name)
        And database_id > 4 -- 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) > 0
    Begin
 
        Select Top 1 @databaseID = databaseID
        From #databaseList;
 
        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, N'Limited')
        Where avg_fragmentation_in_percent >= @minFragmentation 
            And index_id > 0 -- ignore heaps
            And page_count > 8 -- ignore objects with less than 1 extent
        Option (MaxDop 1);
 
        Delete From #databaseList
        Where databaseID = @databaseID;
 
    End
 
    Create Clustered Index CIX_temp_indexDefragList
        On #indexDefragList(databaseID, objectID, indexID, partitionNumber);
 
    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 Top 1 @containsLOB_OUT = column_id
                            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();
            Execute sp_executeSQL @sqlCommand;
            Set @dateTimeEnd  = GetDate();
 
            /* Log our actions */
            Insert Into dbo.dba_indexDefragLog
            (
                  databaseID
                , databaseName
                , objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , dateTimeStart
                , durationSeconds
            )
            Select
                  @databaseID
                , @databaseName
                , @objectID
                , @objectName
                , @indexID
                , @indexName
                , @partitionNumber
                , @fragmentation
                , @pageCount
                , @dateTimeStart
                , DateDiff(second, @dateTimeStart, @dateTimeEnd);
 
            /* 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;
 
    /* 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

Thanks to my beta testers, @scoinva, @davidmtate, @jdanton, and @SuperCoolMoss!
Special thanks to SCM for keeping on me to finish this.

Happy Defragging!

Michelle

Source: http://sqlfool.com/2009/03/automated-index-defrag-script/

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.

55 Responses to Automated Index Defrag Script

  1. onpnt says:

    As always your code is excellent Michelle!

    One thing I played around with was changing the @database var so I could pass a comma separated list of DB names. My times to defrag are all over for groups of db’s so it makes it easier over making multiple calls on the proc. Which only means I’m being lazy ;)

  2. Wesley Smith says:

    Michelle,

    I’ve been experimenting with your script, and so far, I like it a lot.

    I notice that you look to see if the table has any LOB columns, and if so, you use a ReOrganize instead of a ReBuild. But it’s my understanding that it’s not _table_ that’s of concern, the question is if the _index_ contains any LOB columns. So, your approach is issuing ReOrganize when it could be using ReBuild. (Assuming that I’m not misunderstanding SQL Server or your script)

    Taking a look at another index maintenance tool from Lara Rubbelke here:
    http://blogs.digineer.com/blogs/larar/archive/2007/07/30/smart-index-defragmentation-for-an-online-world.aspx

    gave me some ideas.

    I added an indexType VARCHAR(18) not NULL to the #indexDefragList, which I filled via the index_type_desc column of the sys.dm_db_index_physical_stats.

    Then, each time through the loop, I fill a new variable: @indexType VARCHAR(18) with the indexType column from #indexDefragList

    Finally, I changed the code below “Determine if the table contains LOBs” to the following:

    IF @indexType = ‘CLUSTERED INDEX’
    BEGIN
    SELECT @LOB_SQL = ‘ Select @containsLOB_OUT =
    CASE
    WHEN EXISTS(SELECT * FROM ‘ + @databaseName + ‘.SYS.COLUMNS c
    WHERE c.OBJECT_ID = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
    AND (user_type_id in (34, 35, 99, 241)
    OR (user_type_id in (165, 167, 231) AND max_length = -1)))
    THEN 1
    ELSE 0
    END’
    , @LOB_SQL_Param = ‘@containsLOB_OUT int OutPut’;
    END

    IF @indexType = ‘NONCLUSTERED INDEX’
    BEGIN
    SELECT @LOB_SQL = ‘ Select @containsLOB_OUT =
    CASE
    WHEN EXISTS(SELECT * FROM ‘ + @databaseName + ‘.SYS.index_columns ic
    INNER JOIN ‘ + @databasename + ‘.sys.Columns c
    ON ic.Column_ID = c.column_id
    AND ic.Object_ID = c.Object_ID
    WHERE ic.Object_ID = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
    AND ic.Index_ID = ‘ + CAST(@indexID AS VARCHAR(10)) + ‘
    AND (user_type_id in (34, 35, 99, 241)
    OR (user_type_id in (165, 167, 231) AND max_length = -1)))
    THEN 1
    ELSE 0
    END’
    , @LOB_SQL_Param = ‘@containsLOB_OUT int OutPut’;
    END

    EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT;

    (I note that Laura is using slightly different methods than you for determining lob columns – I don’t know which is better)

    These changes seem to be working well for me, but I haven’t given them too much testing. Hopefully they will be useful to someone.

  3. Pingback: Database Fragmentation with Visual Defrag « Tipsy little box

  4. Pingback: Confluence: Valdis Pukis

  5. Thanks, Wesley! That’s a great point. I don’t work with LOB’s much in my production data, so I haven’t spent too much time on it in the defrag script. I’ll try to enhance the LOB functionality in the next release.

  6. Thanks, Ted! That’s another great idea, I’ll add it on the list of things to include in the future. :)

  7. Pingback: SQL SERVER - Automated Index Defragmentation Script « Journey to SQL Authority with Pinal Dave

  8. Just the scrpit which i am looking for…thank you very much….

    Thanks — Vijaya Kadiyala
    http://www.dotnetvj.com

  9. Pingback: gOODiDEA.NET

  10. Pingback: Aaron Bertrand

  11. John Sansom says:

    Hi, thank you again for the excellent script. This has proved very useful indeed in a number of our environments.

    I have come across a very minor bug and hope you do not mind me bringing it to your attention on this post however, I could not locate alternative contact details.

    Within the loop for defragging (Titled: /* Begin our loop for defragging */), the variable @containsLOB needs to be initialised or cleared with each pass through the loop, otherwise a prior value can be persisted to a subsequent loop.

    Hope my explanation makes sense.

  12. Hi John! Do I mind if you point out a bug? Absolutely not! This is great feedback and will only improve my script. I don’t work with LOB’s much so I didn’t test that part of the script very well, obviously. :)

    I’ve added it to the list of revisions for my next release, which I’ll hopefully get to in the next couple of weeks. Thank you for bringing it to my attention!

  13. John Sansom says:

    You’re welcome. Looking forward to the next release!

  14. Jason Hale says:

    I have one minor complaint with the script (after the obligatory praise, of course). If you set the @printCommands bit on, it prints the command after executing it. This should be before the command executes and here’s why. If an error occurs during the operation of the script (as it did with me – related to one of my triggers), you cannot see where the error is occurring.

  15. @Jason Good point! I’ll take a look at making that change in my next revision.

  16. Jon says:

    Great script.

    Idea, how about adding an option to set the database into ‘Simple’ recovery mode before defragging/rebuilding indexes, and then set it back to ‘Full’, or is that bad practice?

  17. @Jon I’m not an expert on recovery, but there’s several reasons why I wouldn’t want to make that an option. The biggest reason is, index defrag operations could take hours to complete, and you wouldn’t necessary want to be without point-in-time recovery for that long. Also, I believe it could cause several issues, such as breaking replication or causing errors if a backup t-log job tries to run. So yes, in general, I believe it’s bad practice.

    Thanks for the suggestion and for reading my blog. :)

  18. CELIO says:

    follows the error message that appeared when the script ran. What procedure should I take.
    thanks.

    dba_indexDefragLog Table Created
    Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 226
    Incorrect syntax near ‘OBJECT_ID’.

  19. Paul says:

    This worked great for me at very short notice on a production system. So if ever you decide to doubt your efforts, I’d encourage you to continue! Thanks for your contribution.

  20. Kenneth Urena says:

    Hi,
    I think this script is great, but it could be even better if u add a maximum quantity of index to rebuild / reorganize every time that the script get call, and another important add would be add page count range management , then for automated tasks we as DBA can create different maintenance plans based on peak times usage of the database.
    I’m actually adding that to the script, and it would be great if I can send it back to you and you can make it available or merge it with the actual changes you are actually doing. If you see is worth. :)

  21. @Celio what version of SQL Server are you running? What patch are you on?

  22. @Kenneth I’ll be releasing a new version in a couple of days, so it’s probably too late to make it into that version. But if you want to send me an e-mail at michelle @ sqlfool dot com, I may incorporate it into the next version. Thank you! :)

  23. CELIO says:

    @Michelle I am running in SQL Server 2005 Express Edition. V.9.00.3042.00

  24. Pingback: Aaron Bertrand

  25. gary says:

    Michelle Ufford,

    Does the new version of this script is released??

  26. gary says:

    Hi Michelle,

    We have share point databases in SQL Server 2005 Enterprise edition in one server and BizTalk Application databases in another Server. Does this script validated to index defrag for Share point databases and BizTalk databases??

    Thank You

  27. Chris says:

    Looks fantastic!

    Anyone get the following error when trying to run it on SQL2005 Ent.?

    Procedure dba_indexDefrag_sp dropped
    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.

    Chris

  28. Tim says:

    I am getting the same error on a SQL 2008 server :
    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.

    Any help?

  29. @Chris and @Tim – I’m not sure what’s causing that error. My Googling returned no results, although I did see a couple of forum discussions where the advice was “I’m not sure what’s causing that but you can safely ignore the error message.” Does the proc still run for you even after getting the error message? Also, please try upgrading to the latest version and seeing if that resolves the issue.

    You can find the latest version here:
    http://sqlfool.com/2009/06/index-defrag-script-v30/

  30. Tim says:

    Michelle,
    I actually replaced ‘SP_EXECUTESQL’ with ‘sp_executesql’ and it worked. Hope this helps.

  31. Ed says:

    Something to check if you are getting odd errors on sp_ExecuteSQL:

    If you are getting errors about sp_ExecuteSQL then check your DB’s Collation settings and you’ll most likley find that it’s a case sensative selection and that in turn can cause probelms if just one of the characters in your SQL code is not the correct case.

    Just something to check that I found. The Sample AdventureWorks DB from Microsoft is set to a case senative collation

  32. Tim Shay says:

    Awesome script! I’ve been chasing index maintenance manually for a good while, planning to write something just like this… but when you’re the operational, development and support DBA wrapped in one, things like that seem to slip down the to-do list, and you end up wasting sooooo much time chasing the issues later. So it’s really good to find a script like this…well done!

  33. Thank you, Tim! I appreciate the kind feedback. :)

  34. Madhu.G says:

    Nice work, Thank you for the script.

  35. Found an error check that is needed… If you have the script set to reindex all, which is the default, and you have a read-only DB, you get a failure…
    “Failed to update database “a” because the database is read-only.”
    I’m going to add the functionality to skip any read-only DBs.
    Still a great script despite the fact that it runs longer than a Maintence Plan that does the same things.

  36. To get around the read-only DB problem, in the “Retrieve the list of databases to investigate” section, I simply added a line below
    And [STATE] = 0 — state must be ONLINE
    AND [is_read_only] = 0; — VC: must NOT be read-only

    Of course, the semicolon has to be moved to the last line of the query, which is now the read-only check. Tested and working fine.

  37. Noddy says:

    For starters, I am no DBA and I am working on rebuilding the indexes.

    I made use of the amazing TSQL script from msdn to alter index based onthe fragmente percent returned by dm_db_index_physical_stats and if the fragment percent is more than 30 then do a REBUILD or do a REORGANISE.

    What I found out was, in the first iteration, there were 87 records which needed defrag.I ran the script and all the 87 indexes (clustered & nonclustered) were rebuilt or reindexed. When I got the stats from dm_db_index_physical_stats , there were still 27 records which needed defrag and all of theses were NON CLUSTERED Indexes. All the Clustered indexes were fixed.

    No matter how many times I run the script to defrag these records, I still have the same indexes to be defraged and most of them with the same fragmentation %. Nothing seems to change after this.

    Note: I did not perform any inserts/ updates/ deletes to the tables during these iterations. Still the Rebuild/reorganise did not result in any change.

    More information: Using SQL 2008
    Script as available in msdn http://msdn.microsoft.com/en-us/library/ms188917.aspx

    Could you please explain why these 27 records of non clustered indexes are not being changed/ modified ?

    Any help on this would be highly appreciated.

    Nod

  38. Pingback: SQL Server 2005 Smart Defrag Page Count | Kingston Lee

  39. phillip says:

    Noddy heres a good link to cluster explanation. Check your settings and version of SQL as well. I manage some fairly large DB’s and this script is about o make me a VERY HAPPY Man.

  40. Leon Orlov says:

    Thank you. Very useful script for large databases. I fixed a few minor issues reported above. Made some additional modification and make is a permanent member of my sql_script library.

    By rebuilding/reorg-ing index with this script you can cut time significantly to avoid unnecessary index rebuilds.

    cheers

  41. sunil says:

    Hi Michelle,

    I started my rebuild index job and its running for hours and i could see the job is hung, can i kill it ,if so will the indexes gets dropped or not, please let me know.

    Regards
    Sunil.B

  42. Hi Sunil,

    The job is probably not hung. My guess if you either have a lot of indexes that need to be defragged, or some are really large. Most likely a combination of both. Regardless, if you need to kill the job, rest assured that nothing needs to be rolled back and that the indexes will *not* be dropped.

    Hope that helps.

    Michelle

  43. Adriano says:

    Hi, I apologize for my English (I’m Italian). I want to know how to use the script and the extension.
    Can I use it on a database maintained by iAnywhere?
    Thanks

  44. DSA says:

    Hello,

    Have you this script for sql server 2000?

  45. Pingback: How do you want your index? | Verity

  46. IRFAN says:

    GETTING ERROR WHEN CREATING THE PROCEDURE

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

  47. D says:

    Thanks very much for this – I’ve used it and trimmed it to my needs (removed the log tables etc).

    Amazing piece of work though. Nice one.

  48. Mike S says:

    Hey Michelle – I seem to remember you once had some logic in this that had the procedure quit if it went outside a maintenance window (e.g. if it ran past 5 AM server time). Is my memory playing tricks in my old age?

    PS. How’s the baby?

  49. Meghana says:

    Hi ,

    Great Script…

    I have few query on this script…
    In this script ,
    its checking if @allowPageLocks =0 (count for index which have Allow_Page_Locks=0) and if object have 1 or more LOB Objects then although index fragmentation value exceeds the rebuidthreshold value , it dont make rebuid , but make reorganization on it.

    I am a little confused on it, and need to know , what is reason that we should do rebuid on index if we found ,1 or more LOB objects and Allow_Page_Locks=0

  50. PeterD says:

    The am getting the same error as reported by IRFAN and CELIO. I am running the script on 2008R2 standard. Can anyone offer a solution?

  51. Jennifer says:

    I am getting the following results after running the script:

    Procedure dba_indexDefrag_sp dropped
    Cannot add rows to sysdepends for the current object because it depends on the missing object ‘sp_executeSQL’. The object will still be created.
    Cannot add rows to sysdepends for the current object because it depends on the missing object ‘sp_executeSQL’. The object will still be created.
    Cannot add rows to sysdepends for the current object because it depends on the missing object ‘sp_executeSQL’. The object will still be created.
    Cannot add rows to sysdepends for the current object because it depends on the missing object ‘sp_executeSQL’. The object will still be created.

  52. Ionut JULEA says:

    Great article! Thank you for the script. Worked like a charm!

  53. Dev says:

    if the database has online read-only file groups containing older partitions for partitioned tables, would this script work. I understand that if a filegroup is marked as read-only the index should not be fragmented anyways but if it is, I am guessing this script would blow up correct?

  54. Duane Lawrence says:

    The new functionality in SQL Server 2012 named AlwaysOn does not mark the read only replica as read only in the sys.databases.is_read_only instead it is a new DMV sys.dm_hadr_availability_group_states in the column primary_replica. What I do is compare that to @@servername and then react based on that. The function named sys.fn_hadr_backup_is_preferred_replica is buggy and incorrectly skips stuff.

    https://connect.microsoft.com/SQLServer/feedback/details/781440/backups-are-being-skipped-by-sys-fn-hadr-backup-is-preferred-replica-alwayson-available-groups#tabs

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>