Updated Index Defrag Script (2005, 2008)

By Michelle Ufford | November 17, 2008 - 10:48 am | Performance & Tuning, SQL Server, T-SQL Scripts

Thanks to everyone who left a comment or sent me an e-mail regarding the Index Defrag Script. I’ve received some great feedback and requests for features. I’ve also had some questions regarding how to use it, which I will answer at the end of this post.

Changes include:
– separate version for both Enterprise and Standard editions
– Standard edition removes partitioning and online options
– output option to see fragmentation levels
– page_count added to the log table

I’ve also verified that this script works well in SQL 2008.

Enterprise Version:

IF EXISTS(SELECT OBJECT_ID FROM sys.tables
                WHERE [name] = N'dba_indexDefragLog')
BEGIN
 
    DROP TABLE dbo.dba_indexDefragLog;
 
    PRINT 'dba_indexDefragLog table dropped!';
 
END
 
CREATE TABLE dbo.dba_indexDefragLog
(
      indexDefrag_id    INT IDENTITY(1,1)   NOT NULL
    , objectID          INT                 NOT NULL
    , objectName        NVARCHAR(130)       NOT NULL
    , indexID           INT                 NOT NULL
    , indexName         NVARCHAR(130)       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';
 
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'),
        N'IsProcedure') IS Null
BEGIN
    EXECUTE ('Create Procedure dbo.dba_indexDefrag_sp
        As Print ''Hello World!''');
    RAISERROR('Procedure dba_indexDefrag_sp created.'
        , 10, 1);
END;
Go
 
SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go
 
ALTER PROCEDURE dbo.dba_indexDefrag_sp
 
    /* Declare Parameters */
      @minFragmentation     FLOAT           = 10.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 */
    , @onlineRebuild        bit             = 1     
        /* 1 = online rebuild; 0 = offline rebuild */
    , @executeSQL           BIT             = 1
        /* 1 = execute; 0 = print command only */
    , @tableName            VARCHAR(4000)   = Null 
        /* Option to specify a table name */
    , @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 */
AS
/********************************************************************
    Name:       dba_indexDefrag_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Defrags all indexes for the current database
 
    Notes:      This script was designed for SQL Server 2005
                Enterprise Edition.
 
    CAUTION: Monitor transaction log if executing for the first time!
 
      @minFragmentation     defaulted to 10%, will not defrag if
                            fragmentation if less than specified.
 
      @rebuildThreshold     defaulted to 30% as recommended by
                            Microsoft in BOL;
                            > than 30% will result in rebuild instead
 
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild
 
      @executeSQL           1 = execute the SQL generated by this proc;
                            0 = print command only
 
      @tableName            Specify if you only want to defrag indexes
                            for a specific table
 
      @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 some time to catch up
 
    Called by:  SQL Agent Job or DBA
 
    Date        Initials  Description
    ----------------------------------------------------------------
    2008-10-27  MFU       Initial Release
    2008-11-17  MFU       Added page_count to log table
                          , added @printFragmentation option
********************************************************************
    Exec dbo.dba_indexDefrag_sp
          @executeSQL         = 1
        , @printCommands      = 1
        , @minFragmentation   = 0
        , @printFragmentation = 1;
********************************************************************/
 
SET NOCOUNT ON;
SET XACT_Abort ON;
 
BEGIN
 
    /* Declare our variables */
    DECLARE   @objectID         INT
            , @indexID          INT
            , @partitionCount   BIGINT
            , @schemaName       NVARCHAR(130)
            , @objectName       NVARCHAR(130)
            , @indexName        NVARCHAR(130)
            , @partitionNumber  SMALLINT
            , @partitions       SMALLINT
            , @fragmentation    FLOAT
            , @pageCount        INT
            , @sqlCommand       NVARCHAR(4000)
            , @rebuildCommand   NVARCHAR(200)
            , @dateTimeStart    DATETIME
            , @dateTimeEnd      DATETIME
            , @containsLOB      BIT;
 
    /* 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';
 
    /* Determine which indexes to defrag using our
       user-defined parameters */
    SELECT
          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 */
    INTO #indexDefragList
    FROM sys.dm_db_index_physical_stats
        (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')
    WHERE avg_fragmentation_in_percent > @minFragmentation
        And index_id > 0
    OPTION (MaxDop 1);
 
    /* Create a clustered index to boost performance a little */
    CREATE CLUSTERED INDEX CIX_temp_indexDefragList
        ON #indexDefragList(objectID, indexID, partitionNumber);
 
    /* Begin our loop for defragging */
    WHILE (SELECT COUNT(*) FROM #indexDefragList
            WHERE defragStatus = 0) > 0
    BEGIN
 
        /* Grab the most fragmented index first to defrag */
        SELECT TOP 1
              @objectID         = objectID
            , @fragmentation    = fragmentation
            , @indexID          = indexID
            , @partitionNumber  = partitionNumber
            , @pageCount        = page_count
        FROM #indexDefragList
        WHERE defragStatus = 0
        ORDER BY fragmentation DESC;
 
        /* Look up index information */
        SELECT @objectName = QUOTENAME(o.name)
             , @schemaName = QUOTENAME(s.name)
        FROM sys.objects AS o
        Inner Join sys.schemas AS s
            ON s.schema_id = o.schema_id
        WHERE o.OBJECT_ID = @objectID;
 
        SELECT @indexName = QUOTENAME(name)
        FROM sys.indexes
        WHERE OBJECT_ID = @objectID
            And index_id = @indexID
            And type > 0;
 
        /* Determine if the index is partitioned */
        SELECT @partitionCount = COUNT(*)
        FROM sys.partitions
        WHERE OBJECT_ID = @objectID
            And index_id = @indexID;
 
        /* Look for LOBs */
        SELECT TOP 1
            @containsLOB = column_id
        FROM sys.columns WITH (NOLOCK)
        WHERE 
            [OBJECT_ID] = @objectID
            And (system_type_id In (34, 35, 99)
            -- 34 = image, 35 = text, 99 = ntext
                    Or max_length = -1);
            -- varbinary(max), varchar(max), nvarchar(max), xml
 
        /* See if we should rebuild or reorganize; handle thusly */
        IF @fragmentation < @rebuildThreshold And @partitionCount <= 1
            SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
                + @schemaName + N'.' + @objectName + N' ReOrganize';
 
        IF @fragmentation >= @rebuildThreshold
            And IsNull(@containsLOB, 0) = 0
                -- Cannot rebuild if the table has one or more LOB
            And @partitionCount <= 1
        BEGIN
 
            /* We should always rebuild online if possible
                (SQL 2005 Enterprise) */
            IF @onlineRebuild = 0
                SET @rebuildCommand = N' Rebuild With
                    (Online = Off, MaxDop = 1)';
            ELSE
                SET @rebuildCommand = N' Rebuild With
                    (Online = On, MaxDop = 1)';
 
            SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
                + @schemaName + N'.' + @objectName + @rebuildCommand;
        END;
 
        /* If our index is partitioned, we should always reorganize */
        IF @partitionCount > 1
            SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
                + @schemaName + N'.' + @objectName + N' ReOrganize'
                + N' Partition = '
                + CAST(@partitionNumber AS NVARCHAR(10));
                -- no MaxDop needed, single threaded operation
 
        /* Are we executing the SQL?  If so, do it */
        IF @executeSQL = 1
        BEGIN
 
            /* Grab the time for logging purposes */
            SET @dateTimeStart  = GETDATE();
            EXECUTE (@sqlCommand);
            SET @dateTimeEnd  = GETDATE();
 
            /* Log our actions */
            INSERT INTO dbo.dba_indexDefragLog
            (
                  objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , dateTimeStart
                , durationSeconds
            )
            SELECT
                  @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 print
            the commands */
        BEGIN
            IF @printCommands = 1
                PRINT @sqlCommand;
        END
 
        /* Update our index defrag list when we've
            finished with that index */
        UPDATE #indexDefragList
        SET defragStatus = 1
        WHERE objectID         = @objectID
          And indexID          = @indexID
          And partitionNumber  = @partitionNumber;
 
    END
 
    /* Do we want to output our fragmentation results? */
    If @printFragmentation = 1
        Select idl.objectID
            , o.name As 'tableName'
            , idl.indexID
            , i.name As 'indexName'
            , idl.fragmentation
            , idl.page_count
        From #indexDefragList As idl
        Join sys.objects AS o
            On idl.objectID = o.object_id
        Join sys.indexes As i
            On idl.objectID = i.object_id
            And idl.indexID = i.index_id;
 
    /* When everything is done, make sure to get rid of
        our temp table */
    DROP TABLE #indexDefragList;
 
    SET NOCOUNT OFF;
    RETURN 0
END
Go

IF EXISTS(SELECT OBJECT_ID FROM sys.tables WHERE [name] = N'dba_indexDefragLog') BEGIN DROP TABLE dbo.dba_indexDefragLog; PRINT 'dba_indexDefragLog table dropped!'; END CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) NOT NULL , objectID INT NOT NULL , objectName NVARCHAR(130) NOT NULL , indexID INT NOT NULL , indexName NVARCHAR(130) 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'; IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') IS Null BEGIN EXECUTE ('Create Procedure dbo.dba_indexDefrag_sp As Print ''Hello World!'''); RAISERROR('Procedure dba_indexDefrag_sp created.' , 10, 1); END; Go SET ANSI_Nulls ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET NOCOUNT ON; SET Numeric_RoundAbort OFF; SET Quoted_Identifier ON; Go ALTER PROCEDURE dbo.dba_indexDefrag_sp /* Declare Parameters */ @minFragmentation FLOAT = 10.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 */ , @onlineRebuild bit = 1 /* 1 = online rebuild; 0 = offline rebuild */ , @executeSQL BIT = 1 /* 1 = execute; 0 = print command only */ , @tableName VARCHAR(4000) = Null /* Option to specify a table name */ , @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 */ AS /******************************************************************** Name: dba_indexDefrag_sp Author: Michelle F. Ufford Purpose: Defrags all indexes for the current database Notes: This script was designed for SQL Server 2005 Enterprise Edition. CAUTION: Monitor transaction log if executing for the first time! @minFragmentation defaulted to 10%, will not defrag if fragmentation if less than specified. @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; > than 30% will result in rebuild instead @onlineRebuild 1 = online rebuild; 0 = offline rebuild @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @tableName Specify if you only want to defrag indexes for a specific table @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 some time to catch up Called by: SQL Agent Job or DBA Date Initials Description ---------------------------------------------------------------- 2008-10-27 MFU Initial Release 2008-11-17 MFU Added page_count to log table , added @printFragmentation option ******************************************************************** Exec dbo.dba_indexDefrag_sp @executeSQL = 1 , @printCommands = 1 , @minFragmentation = 0 , @printFragmentation = 1; ********************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; BEGIN /* Declare our variables */ DECLARE @objectID INT , @indexID INT , @partitionCount BIGINT , @schemaName NVARCHAR(130) , @objectName NVARCHAR(130) , @indexName NVARCHAR(130) , @partitionNumber SMALLINT , @partitions SMALLINT , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT; /* 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'; /* Determine which indexes to defrag using our user-defined parameters */ SELECT 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 */ INTO #indexDefragList FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited') WHERE avg_fragmentation_in_percent > @minFragmentation And index_id > 0 OPTION (MaxDop 1); /* Create a clustered index to boost performance a little */ CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList(objectID, indexID, partitionNumber); /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0 BEGIN /* Grab the most fragmented index first to defrag */ SELECT TOP 1 @objectID = objectID , @fragmentation = fragmentation , @indexID = indexID , @partitionNumber = partitionNumber , @pageCount = page_count FROM #indexDefragList WHERE defragStatus = 0 ORDER BY fragmentation DESC; /* Look up index information */ SELECT @objectName = QUOTENAME(o.name) , @schemaName = QUOTENAME(s.name) FROM sys.objects AS o Inner Join sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.OBJECT_ID = @objectID; SELECT @indexName = QUOTENAME(name) FROM sys.indexes WHERE OBJECT_ID = @objectID And index_id = @indexID And type > 0; /* Determine if the index is partitioned */ SELECT @partitionCount = COUNT(*) FROM sys.partitions WHERE OBJECT_ID = @objectID And index_id = @indexID; /* Look for LOBs */ SELECT TOP 1 @containsLOB = column_id FROM sys.columns WITH (NOLOCK) WHERE [OBJECT_ID] = @objectID And (system_type_id In (34, 35, 99) -- 34 = image, 35 = text, 99 = ntext Or max_length = -1); -- varbinary(max), varchar(max), nvarchar(max), xml /* See if we should rebuild or reorganize; handle thusly */ IF @fragmentation < @rebuildThreshold And @partitionCount <= 1 SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + N' ReOrganize'; IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) = 0 -- Cannot rebuild if the table has one or more LOB And @partitionCount <= 1 BEGIN /* We should always rebuild online if possible (SQL 2005 Enterprise) */ IF @onlineRebuild = 0 SET @rebuildCommand = N' Rebuild With (Online = Off, MaxDop = 1)'; ELSE SET @rebuildCommand = N' Rebuild With (Online = On, MaxDop = 1)'; SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + @rebuildCommand; END; /* If our index is partitioned, we should always reorganize */ IF @partitionCount > 1 SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + N' ReOrganize' + N' Partition = ' + CAST(@partitionNumber AS NVARCHAR(10)); -- no MaxDop needed, single threaded operation /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); EXECUTE (@sqlCommand); SET @dateTimeEnd = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart , durationSeconds ) SELECT @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 print the commands */ BEGIN IF @printCommands = 1 PRINT @sqlCommand; END /* Update our index defrag list when we've finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; END /* Do we want to output our fragmentation results? */ If @printFragmentation = 1 Select idl.objectID , o.name As 'tableName' , idl.indexID , i.name As 'indexName' , idl.fragmentation , idl.page_count From #indexDefragList As idl Join sys.objects AS o On idl.objectID = o.object_id Join sys.indexes As i On idl.objectID = i.object_id And idl.indexID = i.index_id; /* When everything is done, make sure to get rid of our temp table */ DROP TABLE #indexDefragList; SET NOCOUNT OFF; RETURN 0 END Go

Standard Version:

IF EXISTS(SELECT OBJECT_ID FROM sys.tables
                WHERE [name] = N'dba_indexDefragLog')
BEGIN
 
    DROP TABLE dbo.dba_indexDefragLog;
 
    PRINT 'dba_indexDefragLog table dropped!';
 
END
 
CREATE TABLE dbo.dba_indexDefragLog
(
      indexDefrag_id    INT IDENTITY(1,1)   NOT NULL
    , objectID          INT                 NOT NULL
    , objectName        NVARCHAR(130)       NOT NULL
    , indexID           INT                 NOT NULL
    , indexName         NVARCHAR(130)       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';
 
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefragStandard_sp'),
        N'IsProcedure') IS Null
BEGIN
    EXECUTE ('Create Procedure dbo.dba_indexDefragStandard_sp
        As Print ''Hello World!''');
    RAISERROR('Procedure dba_indexDefragStandard_sp created.'
        , 10, 1);
END;
Go
 
SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go
 
ALTER PROCEDURE dbo.dba_indexDefragStandard_sp
 
    /* Declare Parameters */
      @minFragmentation     FLOAT           = 10.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 */
    , @tableName            VARCHAR(4000)   = Null 
        /* Option to specify a table name */
    , @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 */
AS
/********************************************************************
    Name:       dba_indexDefragStandard_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Defrags all indexes for the current database
 
    Notes:      This script was designed for SQL Server 2005
                Standard edition.
 
    CAUTION: Monitor transaction log if executing for the first time!
 
      @minFragmentation     defaulted to 10%, will not defrag if
                            fragmentation if less than specified.
 
      @rebuildThreshold     defaulted to 30% as recommended by
                            Microsoft in BOL;
                            > than 30% will result in rebuild instead
 
      @executeSQL           1 = execute the SQL generated by this proc;
                            0 = print command only
 
      @tableName            Specify if you only want to defrag indexes
                            for a specific table
 
      @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 some time to catch up
 
    Called by:  SQL Agent Job or DBA
 
    Date        Initials  Description
    ----------------------------------------------------------------
    2008-10-27  MFU       Initial Release
    2008-11-17  MFU       Added page_count to log table
                          , added @printFragmentation option
********************************************************************
    Exec dbo.dba_indexDefragStandard_sp
          @executeSQL         = 1
        , @printCommands      = 1
        , @minFragmentation   = 0
        , @printFragmentation = 1;
********************************************************************/
 
SET NOCOUNT ON;
SET XACT_Abort ON;
 
BEGIN
 
    /* Declare our variables */
    DECLARE   @objectID         INT
            , @indexID          INT
            , @schemaName       NVARCHAR(130)
            , @objectName       NVARCHAR(130)
            , @indexName        NVARCHAR(130)
            , @fragmentation    FLOAT
            , @pageCount        INT
            , @sqlCommand       NVARCHAR(4000)
            , @rebuildCommand   NVARCHAR(200)
            , @dateTimeStart    DATETIME
            , @dateTimeEnd      DATETIME
            , @containsLOB      BIT;
 
    /* 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';
 
    /* Determine which indexes to defrag using our
       user-defined parameters */
    SELECT
          OBJECT_ID AS objectID
        , index_id AS indexID
        , avg_fragmentation_in_percent AS fragmentation
        , page_count 
        , 0 AS 'defragStatus'
            /* 0 = unprocessed, 1 = processed */
    INTO #indexDefragList
    FROM sys.dm_db_index_physical_stats
        (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')
    WHERE avg_fragmentation_in_percent > @minFragmentation
        And index_id > 0
    OPTION (MaxDop 1);
 
    /* Create a clustered index to boost performance a little */
    CREATE CLUSTERED INDEX CIX_temp_indexDefragList
        ON #indexDefragList(objectID, indexID);
 
    /* Begin our loop for defragging */
    WHILE (SELECT COUNT(*) FROM #indexDefragList
            WHERE defragStatus = 0) > 0
    BEGIN
 
        /* Grab the most fragmented index first to defrag */
        SELECT TOP 1
              @objectID         = objectID
            , @fragmentation    = fragmentation
            , @indexID          = indexID
            , @pageCount        = page_count
        FROM #indexDefragList
        WHERE defragStatus = 0
        ORDER BY fragmentation DESC;
 
        /* Look up index information */
        SELECT @objectName = QUOTENAME(o.name)
             , @schemaName = QUOTENAME(s.name)
        FROM sys.objects AS o
        Inner Join sys.schemas AS s
            ON s.schema_id = o.schema_id
        WHERE o.OBJECT_ID = @objectID;
 
        SELECT @indexName = QUOTENAME(name)
        FROM sys.indexes
        WHERE OBJECT_ID = @objectID
            And index_id = @indexID
            And type > 0;
 
        /* Look for LOBs */
        SELECT TOP 1
            @containsLOB = column_id
        FROM sys.columns WITH (NOLOCK)
        WHERE 
            [OBJECT_ID] = @objectID
            And (system_type_id In (34, 35, 99)
            -- 34 = image, 35 = text, 99 = ntext
                    Or max_length = -1);
            -- varbinary(max), varchar(max), nvarchar(max), xml
 
        /* See if we should rebuild or reorganize; handle thusly */
        IF @fragmentation < @rebuildThreshold 
            Or IsNull(@containsLOB, 0) > 0 
            -- Cannot rebuild if the table has one or more LOB
            SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
                + @schemaName + N'.' + @objectName + N' ReOrganize;'
        ELSE
            SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
                + @schemaName + N'.' + @objectName +  ' Rebuild '
                + 'With (MaxDop = 1)'; -- minimize impact on server
 
        /* Are we executing the SQL?  If so, do it */
        IF @executeSQL = 1
        BEGIN
 
            /* Grab the time for logging purposes */
            SET @dateTimeStart  = GETDATE();
            EXECUTE (@sqlCommand);
            SET @dateTimeEnd  = GETDATE();
 
            /* Log our actions */
            INSERT INTO dbo.dba_indexDefragLog
            (
                  objectID
                , objectName
                , indexID
                , indexName
                , fragmentation
                , page_count
                , dateTimeStart
                , durationSeconds
            )
            SELECT
                  @objectID
                , @objectName
                , @indexID
                , @indexName
                , @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 print
            the commands */
        BEGIN
            IF @printCommands = 1
                PRINT @sqlCommand;
        END
 
        /* Update our index defrag list when we've
            finished with that index */
        UPDATE #indexDefragList
        SET defragStatus = 1
        WHERE objectID  = @objectID
          And indexID   = @indexID;
 
    END
 
    /* Do we want to output our fragmentation results? */
    IF @printFragmentation = 1
        SELECT idl.objectID
            , o.name As 'tableName'
            , idl.indexID
            , i.name As 'indexName'
            , idl.fragmentation
            , idl.page_count
        FROM #indexDefragList AS idl
        JOIN sys.objects AS o
            ON idl.objectID = o.object_id
        JOIN sys.indexes As i
            ON idl.objectID = i.object_id
            AND idl.indexID = i.index_id;
 
    /* When everything is done, make sure to get rid of
        our temp table */
    DROP TABLE #indexDefragList;
 
    SET NOCOUNT OFF;
    RETURN 0
END
Go

IF EXISTS(SELECT OBJECT_ID FROM sys.tables WHERE [name] = N'dba_indexDefragLog') BEGIN DROP TABLE dbo.dba_indexDefragLog; PRINT 'dba_indexDefragLog table dropped!'; END CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) NOT NULL , objectID INT NOT NULL , objectName NVARCHAR(130) NOT NULL , indexID INT NOT NULL , indexName NVARCHAR(130) 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'; IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefragStandard_sp'), N'IsProcedure') IS Null BEGIN EXECUTE ('Create Procedure dbo.dba_indexDefragStandard_sp As Print ''Hello World!'''); RAISERROR('Procedure dba_indexDefragStandard_sp created.' , 10, 1); END; Go SET ANSI_Nulls ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET NOCOUNT ON; SET Numeric_RoundAbort OFF; SET Quoted_Identifier ON; Go ALTER PROCEDURE dbo.dba_indexDefragStandard_sp /* Declare Parameters */ @minFragmentation FLOAT = 10.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 */ , @tableName VARCHAR(4000) = Null /* Option to specify a table name */ , @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 */ AS /******************************************************************** Name: dba_indexDefragStandard_sp Author: Michelle F. Ufford Purpose: Defrags all indexes for the current database Notes: This script was designed for SQL Server 2005 Standard edition. CAUTION: Monitor transaction log if executing for the first time! @minFragmentation defaulted to 10%, will not defrag if fragmentation if less than specified. @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; > than 30% will result in rebuild instead @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @tableName Specify if you only want to defrag indexes for a specific table @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 some time to catch up Called by: SQL Agent Job or DBA Date Initials Description ---------------------------------------------------------------- 2008-10-27 MFU Initial Release 2008-11-17 MFU Added page_count to log table , added @printFragmentation option ******************************************************************** Exec dbo.dba_indexDefragStandard_sp @executeSQL = 1 , @printCommands = 1 , @minFragmentation = 0 , @printFragmentation = 1; ********************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; BEGIN /* Declare our variables */ DECLARE @objectID INT , @indexID INT , @schemaName NVARCHAR(130) , @objectName NVARCHAR(130) , @indexName NVARCHAR(130) , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT; /* 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'; /* Determine which indexes to defrag using our user-defined parameters */ SELECT OBJECT_ID AS objectID , index_id AS indexID , avg_fragmentation_in_percent AS fragmentation , page_count , 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */ INTO #indexDefragList FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited') WHERE avg_fragmentation_in_percent > @minFragmentation And index_id > 0 OPTION (MaxDop 1); /* Create a clustered index to boost performance a little */ CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList(objectID, indexID); /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0 BEGIN /* Grab the most fragmented index first to defrag */ SELECT TOP 1 @objectID = objectID , @fragmentation = fragmentation , @indexID = indexID , @pageCount = page_count FROM #indexDefragList WHERE defragStatus = 0 ORDER BY fragmentation DESC; /* Look up index information */ SELECT @objectName = QUOTENAME(o.name) , @schemaName = QUOTENAME(s.name) FROM sys.objects AS o Inner Join sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.OBJECT_ID = @objectID; SELECT @indexName = QUOTENAME(name) FROM sys.indexes WHERE OBJECT_ID = @objectID And index_id = @indexID And type > 0; /* Look for LOBs */ SELECT TOP 1 @containsLOB = column_id FROM sys.columns WITH (NOLOCK) WHERE [OBJECT_ID] = @objectID And (system_type_id In (34, 35, 99) -- 34 = image, 35 = text, 99 = ntext Or max_length = -1); -- varbinary(max), varchar(max), nvarchar(max), xml /* See if we should rebuild or reorganize; handle thusly */ IF @fragmentation < @rebuildThreshold Or IsNull(@containsLOB, 0) > 0 -- Cannot rebuild if the table has one or more LOB SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + N' ReOrganize;' ELSE SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @schemaName + N'.' + @objectName + ' Rebuild ' + 'With (MaxDop = 1)'; -- minimize impact on server /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); EXECUTE (@sqlCommand); SET @dateTimeEnd = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( objectID , objectName , indexID , indexName , fragmentation , page_count , dateTimeStart , durationSeconds ) SELECT @objectID , @objectName , @indexID , @indexName , @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 print the commands */ BEGIN IF @printCommands = 1 PRINT @sqlCommand; END /* Update our index defrag list when we've finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE objectID = @objectID And indexID = @indexID; END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 SELECT idl.objectID , o.name As 'tableName' , idl.indexID , i.name As 'indexName' , idl.fragmentation , idl.page_count FROM #indexDefragList AS idl JOIN sys.objects AS o ON idl.objectID = o.object_id JOIN sys.indexes As i ON idl.objectID = i.object_id AND idl.indexID = i.index_id; /* When everything is done, make sure to get rid of our temp table */ DROP TABLE #indexDefragList; SET NOCOUNT OFF; RETURN 0 END Go

For those who are having troubles with this script…

1) “Not all of my indexes were defragged!” or “Nothing happened when I executed this script.”

This script will only defrag those indexes that surpass the specified threshold. If you’re not seeing your index in the output, try executing this:

    Exec dbo.dba_indexDefrag_sp
          @executeSQL    = 0
        , @printCommands = 1
        , @minFragmentation = 0
        , @printFragmentation = 1;

Check to see what your index’s fragmentation level is. Maybe it’s not as fragmented as you feared. 🙂

2) “My indexes are still fragmented after running this script.”

To quote The Powers That Be (aka Microsoft)…

“In general, fragmentation on small indexes is often not controllable. 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 the index.” — Reorganizing and Rebuilding Indexes

3) “Can I use this in my production environment?”

That really depends on your environment. I’ve successfully used this in some very large production environments. However, I wouldn’t exactly recommend executing the script in the middle of a business day on a billion+ row, heavily fragmented, unpartitioned table, either. 🙂

If you’re not sure what the impact will be, execute the commands-only version of the script…

    Exec dbo.dba_indexDefrag_sp
	      @executeSQL    = 0
        , @printCommands = 1
        , @printFragmentation = 1;

… then execute the statements one at a time. Make sure you monitor tempdb and the transaction log to ensure you don’t have any space issues.

If you have any additional questions or suggestions for this script, leave me a comment and I’ll be happy to help. 🙂

0saves

Save

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 2008, defrag, indexes, partitioning, T-SQL Scripts, TSQL. Bookmark the permalink.

25 Responses to Updated Index Defrag Script (2005, 2008)

Chris K says:

November 17, 2008 at 12:23 pm

Thank you very much for the “Standard” version 🙂 Greatly appreciated!
Grant H says:

November 18, 2008 at 10:00 am

In this Enterprise version, the dba_indexDefragLog table create is missing the partitionNumber column and the stored procedure no longer takes the onlineRebuild option which is still referenced below.

Overall, nice script.
Michelle Ufford says:

November 18, 2008 at 12:17 pm

doh! That was a copy/paste error when I was moving it to the blog… the top was part of the SQL Standard version. Thanks for catching it! It’s fixed now. 🙂
Jim Markgra says:

November 21, 2008 at 2:17 pm

Michelle, thank you so very much for this script!! I hope to be able to use it soon, but I’ve run into a bit of a problem with our DB… Seems we’re running in SQL 2000 Compatibility mode here (80) so I can’t even create the sp on our production server… D’Oh! I did get to test it on my local sandbox, and it works so much better than what I had come up with… I’m far from a DBA, but we don’t have one here so I get to do this stuff… Yay… Just thought I’d chime in about compatibility in case someone was running into the same issue that I am currently (I’m looking to see what, if anything, will break if I raise the compatibility level of our prod db)…
Michelle Ufford says:

November 22, 2008 at 3:48 pm

Jim – you’re very welcome! Thanks for the head’s up about the SQL 2000 compatibility issue… I wasn’t aware but I’m not surprised. I hope all goes well with the move to level 90 compatibility mode. 🙂
Chris Sherlock says:

December 21, 2008 at 12:28 am

With regards to SQL Server 2000 compatibility – this relies heavily on SQL Server 2005 DMVs and DMFs, you would need to find the equivalent sysindexes views and DBCC commands to defrag the indexes in SQL Server 2000. Note that partitioning was completely revamped in SQL Server 2005, so partitions have become very important to the 2005 database engine.
Jim Markgraf says:

December 21, 2008 at 1:26 am

well Michelle, I just wanted to follow up. I was able to make the move to level 90 compatibility this weekend, so I finally got to use the SP on my production server tonight. It took a little over 2 1/2 hours to run as our indexes were heavily fragmented and we have quite a few of them. I have to say I LOVE your script. After the sp was done running, our website seemed to be much more responsive. Of course it could be that nobody is surfing the site at this time of day, but things that took forever even under the slightest load, were noticeably quicker.

Thanks again,
Jim
Michelle Ufford says:

December 21, 2008 at 8:20 am

Thanks, Chris. I should’ve clarified… I haven’t worked much with compatibility levels in SQL Server, so I wasn’t sure if a compatibility level of 80 on a SQL Server 2005 machine would still execute this proc, since the DMV’s actually exist. I just ran a test and, of course, it failed. 🙂
Michelle Ufford says:

December 21, 2008 at 8:21 am

Thanks, Jim, that’s great feedback! I’m glad to hear you’re on 90 and can take advantage of some of the wonderful features available in SQL 2005. 🙂
Marcel Rokers says:

December 28, 2008 at 2:33 am

Hello, I would like to use the script. Is it possible to offer it as a (or two) downloadable scripts.

I try to execute it using MS SQL Server Management Studio, but it gives a lot of errors:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘‘’.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘‘’.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near ‘‘’.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near ‘‘’.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ‘‘’.
Msg 102, Level 15, State 1, Procedure dba_indexDefragStandard_sp, Line 20
Incorrect syntax near ‘‘’.
and more ……

What am I doing wrong?
Chris Sherlock says:

December 28, 2008 at 9:35 pm

Argh! Death by a thousand MS Word curly quotes!

Any chance of modifying this script to include only straight quotes?
Marcel Rokers says:

December 29, 2008 at 6:42 am

That’s why I would like a downloadable version of the script.
SuperCoolMoss says:

February 2, 2009 at 2:25 pm

Hello,

I’m trying to modify this script to run against all databases within an instance of SQL 2005 (consolidated sever), but I’m struggling.

I could install this into each database, but I’d prefer to install into just one controlling database and have it execute against all databases.

Any pointers as to the best way to archive this?

Regards,

SCM
Michelle Ufford says:

February 2, 2009 at 4:12 pm

Hi SCM!

To be honest, I just use a single SQL Agent Job, then add job steps for each of my databases. I know someone modified the script to do exactly what you’re asking for, so I’ll see if they’re interested in sharing their work. 🙂
SuperCoolMoss says:

February 5, 2009 at 12:54 pm

Hello Michelle,

I’ve managed to get my changes to work with the help of colleague. I’m a little inexperienced with T-SQL so sorry if I used any bad techniques. Here’s the Enterprise version which is stored in a central database called DBAadmin and now takes a database name as a parameter.

IF EXISTS(SELECT OBJECT_ID FROM DBAAdmin.sys.tables
WHERE [name] = N’dba_indexDefragLog’)
BEGIN

DROP TABLE DBAAdmin.dbo.dba_indexDefragLog;

PRINT ‘dba_indexDefragLog table dropped!’;

END

CREATE TABLE DBAadmin.dbo.dba_indexDefragLog
(
databaseName NVARCHAR(130) NOT NULL
, indexDefrag_id INT IDENTITY(1,1) NOT NULL
, objectID INT NOT NULL
, objectName NVARCHAR(130) NOT NULL
, indexID INT NOT NULL
, indexName NVARCHAR(130) 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’;

USE DBAAdmin
go

IF OBJECTPROPERTY(OBJECT_ID(‘dbo.dba_indexDefrag_sp’),
N’IsProcedure’) IS Null
BEGIN
EXECUTE (‘Create Procedure dbo.dba_indexDefrag_sp
As Print ”Hello World!”’);
RAISERROR(‘Procedure dba_indexDefrag_sp created.’
, 10, 1);
END;
Go

SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go

ALTER PROCEDURE dbo.dba_indexDefrag_sp

/* Declare Parameters */
@minFragmentation FLOAT = 10.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 */
, @onlineRebuild BIT = 1
/* 1 = online rebuild; 0 = offline rebuild */
, @executeSQL BIT = 1
/* 1 = execute; 0 = print command only */
, @databaseName VARCHAR(4000) = ‘DBAAdmin’
/* Specify database name */
, @tableName VARCHAR(4000) = Null
/* Option to specify a table name */
, @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 */
AS
/********************************************************************
Name: dba_indexDefrag_sp

Author: Michelle F. Ufford, tampered with by SCM.

Purpose: Defrags all indexes for the specified database

Notes: This script was designed for SQL Server 2005
Enterprise Edition.

CAUTION: Monitor transaction log if executing for the first time!

@minFragmentation defaulted to 10%, will not defrag if
fragmentation if less than specified.

@rebuildThreshold defaulted to 30% as recommended by
Microsoft in BOL;
> than 30% will result in rebuild instead

@onlineRebuild 1 = online rebuild;
0 = offline rebuild

@executeSQL 1 = execute the SQL generated by this proc;
0 = print command only

@databaseName Specific database

@tableName Specify if you only want to defrag indexes
for a specific table

@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 some time to catch up

Called by: SQL Agent Job or DBA

Date Initials Description
—————————————————————-
2008-10-27 MFU Initial Release
2008-11-17 MFU Added page_count to log table
, added @printFragmentation option
2009-02-05 SCM Runs from central DB.
********************************************************************
Exec dbo.dba_indexDefrag_sp
@databaseName = ‘orders’
@executeSQL = 1
, @printCommands = 1
, @minFragmentation = 0
, @printFragmentation = 1;
********************************************************************/

SET NOCOUNT ON;
SET XACT_Abort ON;

BEGIN

/* Declare our variables */
DECLARE @objectID INT
, @indexID INT
, @partitionCount BIGINT
, @schemaName NVARCHAR(130)
, @objectName NVARCHAR(130)
, @indexName NVARCHAR(130)
, @partitionNumber SMALLINT
, @partitions SMALLINT
, @fragmentation FLOAT
, @pageCount INT
, @sqlCommand NVARCHAR(4000)
, @rebuildCommand NVARCHAR(200)
, @dateTimeStart DATETIME
, @dateTimeEnd DATETIME
, @containsLOB BIT;

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

/* Determine which indexes to defrag using our
user-defined parameters */
SELECT
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 */
INTO ##indexDefragList
FROM sys.dm_db_index_physical_stats
(DB_ID(@databaseName), OBJECT_ID(@tableName), NULL , NULL, N’Limited’)
WHERE avg_fragmentation_in_percent > @minFragmentation
And index_id > 0
OPTION (MaxDop 1);

/* Create a clustered index to boost performance a little */
CREATE CLUSTERED INDEX CIX_temp_indexDefragList
ON ##indexDefragList(objectID, indexID, partitionNumber);

/* Begin our loop for defragging */
WHILE (SELECT COUNT(*) FROM ##indexDefragList
WHERE defragStatus = 0) > 0
BEGIN

/* Grab the most fragmented index first to defrag */
SELECT TOP 1
@objectID = objectID
, @fragmentation = fragmentation
, @indexID = indexID
, @partitionNumber = partitionNumber
, @pageCount = page_count
FROM ##indexDefragList
WHERE defragStatus = 0
ORDER BY fragmentation DESC;

/* Look up index information */
SET @sqlCommand = N’SELECT QUOTENAME(o.name) AS objectName,QUOTENAME(s.name) AS schemaName into ##indexInfo1 FROM ‘ +
@databaseName + N’.sys.objects AS o INNER Join ‘ + @databaseName +
N’.sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.object_id = ‘ + convert(varchar(256),@objectID) +
N’;’;

EXECUTE (@sqlCommand);

SELECT @objectName = objectName,
@schemaName = schemaName
FROM ##indexInfo1;

— SELECT @objectName = QUOTENAME(o.name)
— , @schemaName = QUOTENAME(s.name)
— FROM sys.objects AS o
— INNER Join sys.schemas AS s
— ON s.schema_id = o.schema_id
— WHERE o.OBJECT_ID = @objectID;

SET @sqlCommand = N’SELECT QUOTENAME(name) AS indexName into ##indexInfo2 FROM ‘ +
@databaseName + N’.sys.indexes WHERE object_id = ‘ + convert(varchar(256),@objectID) +
N’ And index_id = ‘ + convert(varchar(256),@indexID) + N’ And type > 0;’;

EXECUTE (@sqlCommand);

SELECT @indexName = indexname from ##indexInfo2;

— SELECT @indexName = QUOTENAME(name)
— FROM sys.indexes
— WHERE OBJECT_ID = @objectID
— And index_id = @indexID
— And type > 0;

/* Determine if the index is partitioned */

SET @sqlCommand = N’SELECT COUNT(*) AS partitionCount into ##indexInfo3 FROM ‘ +
@databaseName + N’.sys.partitions WHERE object_id = ‘ + convert(varchar(256),@objectID) +
N’ And index_id = ‘ + convert(varchar(256),@indexID) + N’;’;

EXECUTE (@sqlCommand);

SELECT @partitionCount = partitionCount from ##indexInfo3;

— SELECT @partitionCount = COUNT(*)
— FROM sys.partitions
— WHERE OBJECT_ID = @objectID
— And index_id = @indexID;

/* Look for LOBs */

SET @sqlCommand = N’SELECT TOP 1 column_id as containsLOB into ##indexInfo4 FROM ‘ +
@databaseName + N’.sys.columns with (NOLOCK) WHERE [object_id] = ‘ +
convert(varchar(256),@objectID) + N’ And (system_type_id In (34, 35, 99) or max_length = -1);’;

EXECUTE (@sqlCommand);

SELECT @containsLOB = containsLOB from ##indexInfo4;

— SELECT TOP 1
— @containsLOB = column_id
— FROM sys.columns WITH (NOLOCK)
— WHERE
— [OBJECT_ID] = @objectID
— And (system_type_id In (34, 35, 99)
— — 34 = image, 35 = text, 99 = ntext
— Or max_length = -1);
— — varbinary(max), varchar(max), nvarchar(max), xml

/* See if we should rebuild or reorganize; handle thusly */
IF @fragmentation < @rebuildThreshold And @partitionCount = @rebuildThreshold
And IsNull(@containsLOB, 0) = 0
— Cannot rebuild if the table has one or more LOB
And @partitionCount 1
SET @sqlCommand = N’Alter Index ‘ + @indexName + N’ On ‘
+ @databaseName + N’.’ + @schemaName + N’.’ + @objectName + N’ ReOrganize’
+ N’ Partition = ‘
+ CAST(@partitionNumber AS NVARCHAR(10));
— no MaxDop needed, single threaded operation

/* Are we executing the SQL? If so, do it */
IF @executeSQL = 1
BEGIN

/* Grab the time for logging purposes */
SET @dateTimeStart = GETDATE();
EXECUTE (@sqlCommand);
SET @dateTimeEnd = GETDATE();

/* Log our actions */
INSERT INTO DBAadmin.dbo.dba_indexDefragLog
(
databaseName
, objectID
, objectName
, indexID
, indexName
, partitionNumber
, fragmentation
, page_count
, dateTimeStart
, durationSeconds
)
SELECT
@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 print
the commands */
BEGIN
IF @printCommands = 1
PRINT @sqlCommand;
END

/* Update our index defrag list when we’ve
finished with that index */
UPDATE ##indexDefragList
SET defragStatus = 1
WHERE objectID = @objectID
And indexID = @indexID
And partitionNumber = @partitionNumber;

DROP TABLE ##indexInfo1;
DROP TABLE ##indexInfo2;
DROP TABLE ##indexInfo3;
DROP TABLE ##indexInfo4;

END

/* Do we want to output our fragmentation results? */
IF @printFragmentation = 1
SET @sqlCommand = N’ SELECT ”’ + @databaseName +
N”’as”DatabaseName”, id1.objectID, o.name AS ”tableName”, i.name AS ”indexName”, id1.fragmentation, id1.page_count FROM ##indexDefragList as id1 Join ‘ + @databaseName + N’.sys.objects AS o ON id1.objectID = o.object_id Join ‘
+ @databaseName + N’.sys.indexes AS i on id1.objectID = i.object_id and id1.indexID = i.index_id;’
EXECUTE (@sqlcommand);

— IF @printFragmentation = 1
— SELECT idl.objectID
— , o.name AS ‘tableName’
— , idl.indexID
— , i.name AS ‘indexName’
— , idl.fragmentation
— , idl.page_count
— FROM #indexDefragList AS idl
— Join sys.objects AS o
— ON idl.objectID = o.OBJECT_ID
— Join sys.indexes AS i
— ON idl.objectID = i.OBJECT_ID
— And idl.indexID = i.index_id;

/* When everything is done, make sure to get rid of
our temp table */
DROP TABLE ##indexDefragList;

SET NOCOUNT OFF;
RETURN 0
END
Go
Perry Whittle says:

February 9, 2009 at 4:09 pm

Hi Michelle

change the recovery model to bulk logged first and there should be no transaction log issues as alter index is minimally logged in this mode.

regards

Perry
Nathan Asdourian says:

March 16, 2009 at 4:04 pm

Could you Please add an option to specify the database name as the database I’m attempting to use this on must remain with the compatibility set to sql2000, I tried using the persons code that attempted this modification however when I have the proc print out the commands they don’t come out correct therefor I don’t believe I can trust what it’s doing…
Michelle Ufford says:

March 16, 2009 at 4:19 pm

Hi Nathan! I’m actually working on a revision to my script that will include the option to specify a database name. I’m almost done, I hope to have it up sometime this week. 🙂
Slick says:

May 17, 2010 at 1:35 pm

Hi Michelle,

Thank you for the great stored procedure. I have a question. I have about 3 databases I need to run this in on the same server. Does this mean I have to put this stored procedure and table within each DB or just the master DB.

I want to be able to run this as a scheduled SQL job. Please let me know.

Thanks,
Slick
Michelle Ufford says:

May 17, 2010 at 2:03 pm

@Slick Check out the latest version at: http://sqlfool.com/2010/04/index-defrag-script-v4-0/. Among (many) other features, the updated version allows you to defrag multiple databases by calling just the one job. I usually put the proc in an admin database and call it from there.
Pingback: Index Defragmentation « dbadiary
jessica says:

March 21, 2012 at 3:57 pm

hi, this is a great script, i have a question though why can’t you do do a offline rebuild if the table contains a LOB column? i see in your script you just skip that table if you have a BOL (enterprise edition script).
Richard Brown says:

June 27, 2012 at 3:13 pm

Michelle,

Thanks for this! It works perfectly against my SQL2008 instance and will make my life simpler in the future.

Richard
Ravi says:

March 27, 2014 at 4:02 am

how to use it, which I will answer at the end of this post. ?
Marc-André Bertrand says:

July 2, 2014 at 9:23 am

Here is a compact script to rebuild all the indexes of all your databases:

DECLARE @dbName VARCHAR(255); DECLARE @command VARCHAR(255);
DECLARE curDatabase CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' ORDER BY name OPEN curDatabase; FETCH NEXT FROM curDatabase INTO @dbName; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Loading database ' + @dbName; DECLARE curIndex CURSOR FOR SELECT 'ALTER INDEX ' + QUOTENAME(ind.name) + ' ON ' + QUOTENAME(@dbName) + '.' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name) + ' REBUILD;' command FROM sys.dm_db_index_physical_stats(DB_ID(@dbName), NULL, NULL , NULL, N'Limited') AS sta INNER JOIN sys.indexes AS ind ON sta.object_id = ind.object_id INNER JOIN sys.objects AS obj ON ind.object_id = obj.object_id INNER JOIN sys.schemas AS sch ON sch.schema_id = obj.schema_id WHERE sta.index_id > 0 AND sta.avg_fragmentation_in_percent > 20 AND sta.fragment_count > 20; OPEN curIndex; FETCH NEXT FROM curIndex INTO @command; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Executing ' + @command EXECUTE (@command); FETCH NEXT FROM curIndex INTO @command; END; CLOSE curIndex; DEALLOCATE curIndex;
FETCH NEXT FROM curDatabase INTO @dbName; END CLOSE curDatabase; DEALLOCATE curDatabase;