Category Archives: T-SQL Scripts

/* 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

/* 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