Index Defrag Script, v4.1

It’s been quite some time since my last index defrag script update. A big part of the reason for that is because I wanted to implement many of the suggestions I’ve received, but I just haven’t had the time. I still have those changes planned, but I’m not sure quite when I’ll get to it. Rather than continue to wait for a major release, I’m releasing a small update to my defrag that will take care of the most common complaints I receive.

Change Log:

  • Bug fix for databases containing spaces or special characters
  • Support for case-sensitive databases
  • Re-executable CREATE script (for those who want to re-run the whole script)
  • Comma-delimited list of databases is now supported for the @database parameter

Feature List:

  • Defrag a single database, a list of databases, or all databases (@database)
  • Time Limitations: stop defragging after the specified amount of time has elapsed (@timeLimit). Please note, it will not kill a defrag that is currently in process, even if it exceeds the threshold.
  • Optional stop-and-resume functionality: pick up where your defrag last left off without having to rescan sys.dm_db_index_physical_stats. (@forceRescan)
  • Defrag scheduling: choose which days to defrag certain indexes, or exclude certain indexes altogether, by using the dbo.dba_indexDefragExclusion table.
  • Defrag priority: choose whether to defrag indexes in ascending or descending order by range_scan_count (default), fragmentation, or page_count.
  • Current partition exclusion: choose whether or not to exclude the right-most populated partition from the defrag process, common for sliding-window tables (@excludeMaxPartition)
  • Commands-only mode: Choose to just log the current defrag status and print the defrag commands, rather than executing them, by using @executeSQL.
  • … and tons more! Please read the parameter list and notes section for details of all the options available.

FAQ:

I often receive the same questions about this script, so allow me to answer them here:

“I keep running the script, but my index is still fragmented. Why?”
This is most likely a very small index. Here’s what Microsoft has to say:

“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. For more information about mixed extents, see Understanding Pages and Extents.”

“What database should I create it in?” or “Can I create this in the MASTER database?”
It’s up to you where you create it. You could technically create it in the MASTER database, but I recommend creating a utility database for your DBA administrative tasks.

“Can I run this againt a SharePoint database?”
Yes, you can.

“What are the minimum requirements to run this script?” or “Will this run on SQL Server 2000 instances?”
You need to be on SQL Server 2005 SP2 or higher.

Special thanks to Richard Yanger for his assistance with beta testing. :)

You can download a text file of this script here: dba_indexDefrag_sp_v41

/*** Scroll down to the see important notes, disclaimers, and licensing information ***/
 
/* Let's create our parsing function... */
IF EXISTS ( SELECT  [object_id]
            FROM    sys.objects
            WHERE   name = 'dba_parseString_udf' )
    DROP FUNCTION dbo.dba_parseString_udf;
GO
 
CREATE FUNCTION dbo.dba_parseString_udf
(
          @stringToParse VARCHAR(8000)  
        , @delimiter     CHAR(1)
)
RETURNS @parsedString TABLE (stringValue VARCHAR(128))
AS
/*********************************************************************************
    Name:       dba_parseString_udf
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    This function parses string input using a variable delimiter.
 
    Notes:      Two common delimiter values are space (' ') and comma (',')
 
    Date        Initials    Description
    ----------------------------------------------------------------------------
    2011-05-20  MFU         Initial Release
*********************************************************************************
Usage: 		
    SELECT *
    FROM dba_parseString_udf(<string>, <delimiter>);
 
Test Cases:
 
    1.  multiple strings separated by space
        SELECT * FROM dbo.dba_parseString_udf('  aaa  bbb  ccc ', ' ');
 
    2.  multiple strings separated by comma
        SELECT * FROM dbo.dba_parseString_udf(',aaa,bbb,,,ccc,', ',');
*********************************************************************************/
BEGIN
 
    /* Declare variables */
    DECLARE @trimmedString  VARCHAR(8000);
 
    /* We need to trim our string input in case the user entered extra spaces */
    SET @trimmedString = LTRIM(RTRIM(@stringToParse));
 
    /* Let's create a recursive CTE to break down our string for us */
    WITH parseCTE (StartPos, EndPos)
    AS
    (
        SELECT 1 AS StartPos
            , CHARINDEX(@delimiter, @trimmedString + @delimiter) AS EndPos
        UNION ALL
        SELECT EndPos + 1 AS StartPos
            , CHARINDEX(@delimiter, @trimmedString + @delimiter , EndPos + 1) AS EndPos
        FROM parseCTE
        WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0
    )
 
    /* Let's take the results and stick it in a table */  
    INSERT INTO @parsedString
    SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)
    FROM parseCTE
    WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0
    OPTION (MaxRecursion 8000);
 
    RETURN;   
END
GO
 
/* First, we need to take care of schema updates, in case you have a legacy 
   version of the script installed */
DECLARE @indexDefragLog_rename      VARCHAR(128)
  , @indexDefragExclusion_rename    VARCHAR(128)
  , @indexDefragStatus_rename       VARCHAR(128);
 
SELECT  @indexDefragLog_rename = 'dba_indexDefragLog_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112)
      , @indexDefragExclusion_rename = 'dba_indexDefragExclusion_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112);
 
IF EXISTS ( SELECT  [object_id]
            FROM    sys.indexes
            WHERE   name = 'PK_indexDefragLog' ) 
    EXECUTE sp_rename dba_indexDefragLog, @indexDefragLog_rename;
 
IF EXISTS ( SELECT  [object_id]
            FROM    sys.indexes
            WHERE   name = 'PK_indexDefragExclusion' ) 
    EXECUTE sp_rename dba_indexDefragExclusion, @indexDefragExclusion_rename;
 
IF NOT EXISTS ( SELECT  [object_id]
                FROM    sys.indexes
                WHERE   name = 'PK_indexDefragLog_v40' )
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
       , sqlStatement       VARCHAR(4000)       NULL
       , errorMessage       VARCHAR(1000)       NULL 
 
        CONSTRAINT PK_indexDefragLog_v40 
            PRIMARY KEY CLUSTERED (indexDefrag_id)
    );
 
    PRINT 'dba_indexDefragLog Table Created';
 
END
 
IF NOT EXISTS ( SELECT  [object_id]
                FROM    sys.indexes
                WHERE   name = 'PK_indexDefragExclusion_v40' )
BEGIN
 
    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_v40 
            PRIMARY KEY CLUSTERED (databaseID, objectID, indexID)
    );
 
    PRINT 'dba_indexDefragExclusion Table Created';
 
END
 
IF NOT EXISTS ( SELECT  [object_id]
                FROM    sys.indexes
                WHERE   name = 'PK_indexDefragStatus_v40' )
BEGIN
 
    CREATE TABLE dbo.dba_indexDefragStatus
    (
         databaseID         INT             NOT NULL
       , databaseName       NVARCHAR(128)   NOT NULL
       , objectID           INT             NOT NULL
       , indexID            INT             NOT NULL
       , partitionNumber    SMALLINT        NOT NULL
       , fragmentation      FLOAT           NOT NULL
       , page_count         INT             NOT NULL
       , range_scan_count   BIGINT          NOT NULL
       , schemaName         NVARCHAR(128)   NULL
       , objectName         NVARCHAR(128)   NULL
       , indexName          NVARCHAR(128)   NULL
       , scanDate           DATETIME        NOT NULL
       , defragDate         DATETIME        NULL
       , printStatus        BIT DEFAULT (0) NOT NULL
       , exclusionMask      INT DEFAULT (0) NOT NULL
 
        CONSTRAINT PK_indexDefragStatus_v40 
            PRIMARY KEY CLUSTERED (databaseID, objectID, indexID, partitionNumber)
    );
 
    PRINT 'dba_indexDefragStatus Table Created';
 
END;
 
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1 
    BEGIN
        DROP PROCEDURE dbo.dba_indexDefrag_sp;
        PRINT 'Procedure dba_indexDefrag_sp dropped';
    END;
Go
 
CREATE PROCEDURE dbo.dba_indexDefrag_sp
 
    /* Declare Parameters */
    @minFragmentation       FLOAT               = 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 */
  , @defragOrderColumn      NVARCHAR(20)        = 'range_scan_count'
        /* Valid options are: range_scan_count, fragmentation, page_count */
  , @defragSortOrder        NVARCHAR(4)         = 'DESC'
        /* Valid options are: ASC, DESC */
  , @timeLimit              INT                 = 720 /* defaulted to 12 hours */
        /* Optional time limitation; expressed in minutes */
  , @database               VARCHAR(128)        = NULL
        /* Option to specify one or more database names, separated by commas; NULL will return all */
  , @tableName              VARCHAR(4000)       = NULL  -- databaseName.schema.tableName
        /* Option to specify a table name; null will return all */
  , @forceRescan            BIT                 = 0
        /* Whether or not to force a rescan of indexes; 1 = force, 0 = use existing scan, if available */
  , @scanMode               VARCHAR(10)         = N'LIMITED'
        /* Options are LIMITED, SAMPLED, and DETAILED */
  , @minPageCount           INT                 = 8 
        /*  MS recommends > 1 extent (8 pages) */
  , @maxPageCount           INT                 = NULL
        /* NULL = no limit */
  , @excludeMaxPartition    BIT                 = 0
        /* 1 = exclude right-most populated partition; 0 = do not exclude; see notes for caveats */
  , @onlineRebuild          BIT                 = 1     
        /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
  , @sortInTempDB           BIT                 = 1
        /* 1 = perform sort operation in TempDB; 0 = perform sort operation in the index's database */
  , @maxDopRestriction      TINYINT             = NULL
        /* Option to restrict the number of processors for the operation; only in Enterprise */
  , @printCommands          BIT                 = 0     
        /* 1 = print commands; 0 = do not print commands */
  , @printFragmentation     BIT                 = 0
        /* 1 = print fragmentation prior to defrag; 
           0 = do not print */
  , @defragDelay            CHAR(8)             = '00:00:05'
        /* time to wait between defrag commands */
  , @debugMode              BIT                 = 0
        /* display some useful comments to help determine if/WHERE issues occur */
AS /*********************************************************************************
    Name:       dba_indexDefrag_sp
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    Defrags one or more indexes for one or more databases
 
    Notes:
 
    CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING.
             DO NOT RUN UNATTENDED ON LARGE DATABASES DURING BUSINESS HOURS.
 
      @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
 
      @defragOrderColumn    Defines how to prioritize the order of defrags.  Only
                            used if @executeSQL = 1.  
                            Valid options are: 
                            range_scan_count = count of range and table scans on the
                                               index; in general, this is what benefits 
                                               the most FROM defragmentation
                            fragmentation    = amount of fragmentation in the index;
                                               the higher the number, the worse it is
                            page_count       = number of pages in the index; affects
                                               how long it takes to defrag an index
 
      @defragSortOrder      The sort order of the ORDER BY clause.
                            Valid options are ASC (ascending) or DESC (descending).
 
      @timeLimit            Optional, limits how much time can be spent performing 
                            index defrags; expressed in minutes.
 
                            NOTE: The time limit is checked BEFORE an index defrag
                                  is begun, thus a long index defrag can exceed the
                                  time limitation.
 
      @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.
 
      @forceRescan          Whether or not to force a rescan of indexes.  If set
                            to 0, a rescan will not occur until all indexes have
                            been defragged.  This can span multiple executions.
                            1 = force a rescan
                            0 = use previous scan, if there are indexes left to defrag
 
      @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.
 
      @minPageCount         Specifies how many pages must exist in an index in order 
                            to be considered for a defrag.  Defaulted to 8 pages, AS 
                            Microsoft recommends only defragging indexes with more 
                            than 1 extent (8 pages).  
 
                            NOTE: The @minPageCount will restrict the indexes that
                            are stored in dba_indexDefragStatus table.
 
      @maxPageCount         Specifies the maximum number of pages that can exist in 
                            an index and still be considered for a defrag.  Useful
                            for scheduling small indexes during business hours and
                            large indexes for non-business hours.
 
                            NOTE: The @maxPageCount will restrict the indexes that
                            are defragged during the current operation; it will not
                            prevent indexes FROM being stored in the 
                            dba_indexDefragStatus table.  This way, a single scan
                            can support multiple page count thresholds.
 
      @excludeMaxPartition  If an index is partitioned, this option specifies whether
                            to exclude the right-most populated partition.  Typically,
                            this is the partition that is currently being written to in
                            a sliding-window scenario.  Enabling this feature may reduce
                            contention.  This may not be applicable in other types of 
                            partitioning scenarios.  Non-partitioned indexes are 
                            unaffected by this option.
                            1 = exclude right-most populated partition
                            0 = do not exclude
 
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild
 
      @sortInTempDB         Specifies whether to defrag the index in TEMPDB or in the
                            database the index belongs to.  Enabling this option may
                            result in faster defrags and prevent database file size 
                            inflation.
                            1 = perform sort operation in TempDB
                            0 = perform sort operation in the index's database 
 
      @maxDopRestriction    Option to specify a processor limit for index rebuilds
 
      @printCommands        1 = print commands to screen; 
                            0 = do not print commands
 
      @printFragmentation   1 = print fragmentation to screen;
                            0 = do not print fragmentation
 
      @defragDelay          Time to wait between defrag commands; gives the
                            server a little time to catch up 
 
      @debugMode            1 = display debug comments; helps with troubleshooting
                            0 = do not display debug comments
 
    Called by:  SQL Agent Job or DBA
 
    ----------------------------------------------------------------------------
    DISCLAIMER: 
    This code and information are provided "AS IS" without warranty of any kind,
    either expressed or implied, including but not limited to the implied 
    warranties or merchantability and/or fitness for a particular purpose.
    ----------------------------------------------------------------------------
    LICENSE: 
    This index defrag script is free to download and use for personal, educational, 
    and internal corporate purposes, provided that this header is preserved. 
    Redistribution or sale of this index defrag script, in whole or in part, is 
    prohibited without the author's express written consent.
    ----------------------------------------------------------------------------
    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)
    2009-08-28  MFU         3.1     Fixed read_only bug for database lists
    2010-04-20  MFU         4.0     Added time limit option
                                    , added static table with rescan logic
                                    , added parameters for page count & SORT_IN_TEMPDB
                                    , added try/catch logic and additional debug options
                                    , added options for defrag prioritization
                                    , fixed bug for indexes with allow_page_lock = off
                                    , added option to exclude right-most partition
                                    , removed @rebuildStats option
                                    , refer to http://sqlfool.com for full release notes
    2011-04-28  MFU         4.1     Bug fixes for databases requiring []
                                    , cleaned up the create table section
                                    , updated syntax for case-sensitive databases
                                    , comma-delimited list for @database now supported
*********************************************************************************
    Example of how to call this script:
 
        EXECUTE dbo.dba_indexDefrag_sp
              @executeSQL           = 1
            , @printCommands        = 1
            , @debugMode            = 1
            , @printFragmentation   = 1
            , @forceRescan          = 1
            , @maxDopRestriction    = 1
            , @minPageCount         = 8
            , @maxPageCount         = NULL
            , @minFragmentation     = 1
            , @rebuildThreshold     = 30
            , @defragDelay          = '00:00:05'
            , @defragOrderColumn    = 'page_count'
            , @defragSortOrder      = 'DESC'
            , @excludeMaxPartition  = 1
            , @timeLimit            = NULL
            , @database             = 'sandbox,sandbox_caseSensitive';
*********************************************************************************/																
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET QUOTED_IDENTIFIER ON;
 
BEGIN
 
    BEGIN TRY
 
        /* Just a little validation... */
        IF @minFragmentation IS NULL 
            OR @minFragmentation NOT BETWEEN 0.00 AND 100.0
                SET @minFragmentation = 10.0;
 
        IF @rebuildThreshold IS NULL
            OR @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 @defragOrderColumn IS NULL
            OR @defragOrderColumn NOT IN ('range_scan_count', 'fragmentation', 'page_count')
                SET @defragOrderColumn = 'range_scan_count';
 
        IF @defragSortOrder IS NULL
            OR @defragSortOrder NOT IN ('ASC', 'DESC')
                SET @defragSortOrder = 'DESC';
 
        IF @scanMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED')
            SET @scanMode = 'LIMITED';
 
        IF @debugMode IS NULL
            SET @debugMode = 0;
 
        IF @forceRescan IS NULL
            SET @forceRescan = 0;
 
        IF @sortInTempDB IS NULL
            SET @sortInTempDB = 1;
 
 
        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             NVARCHAR(4000)
                , @updateSQL                NVARCHAR(4000)
                , @partitionSQL             NVARCHAR(4000)
                , @partitionSQL_Param       NVARCHAR(1000)
                , @LOB_SQL                  NVARCHAR(4000)
                , @LOB_SQL_Param            NVARCHAR(1000)
                , @indexDefrag_id           INT
                , @startdatetime            DATETIME
                , @enddatetime              DATETIME
                , @getIndexSQL              NVARCHAR(4000)
                , @getIndexSQL_Param        NVARCHAR(4000)
                , @allowPageLockSQL         NVARCHAR(4000)
                , @allowPageLockSQL_Param   NVARCHAR(4000)
                , @allowPageLocks           INT
                , @excludeMaxPartitionSQL   NVARCHAR(4000);
 
        /* Initialize our variables */
        SELECT @startdatetime = GETDATE()
            , @enddatetime = DATEADD(minute, @timeLimit, GETDATE());
 
        /* Create our temporary tables */
        CREATE TABLE #databaseList
        (
              databaseID        INT
            , databaseName      VARCHAR(128)
            , scanStatus        BIT
        );
 
        CREATE TABLE #processor 
        (
              [index]           INT
            , Name              VARCHAR(128)
            , Internal_Value    INT
            , Character_Value   INT
        );
 
        CREATE TABLE #maxPartitionList
        (
              databaseID        INT
            , objectID          INT
            , indexID           INT
            , maxPartition      INT
        );
 
        IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NOWAIT;
 
        /* 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
 
        /* Output the parameters we're working with */
        IF @debugMode = 1 
        BEGIN
 
            SELECT @debugMessage = 'Your SELECTed parameters are... 
            Defrag indexes WITH fragmentation greater than ' + CAST(@minFragmentation AS VARCHAR(10)) + ';
            REBUILD indexes WITH fragmentation greater than ' + CAST(@rebuildThreshold AS VARCHAR(10)) + ';
            You' + CASE WHEN @executeSQL = 1 THEN ' DO' ELSE ' DO NOT' END + ' want the commands to be executed automatically; 
            You want to defrag indexes in ' + @defragSortOrder + ' order of the ' + UPPER(@defragOrderColumn) + ' value;
            You have' + CASE WHEN @timeLimit IS NULL THEN ' NOT specified a time limit;' ELSE ' specified a time limit of ' 
                + CAST(@timeLimit AS VARCHAR(10)) END + ' minutes;
            ' + CASE WHEN @database IS NULL THEN 'ALL databases' ELSE 'The ' + @database + ' database(s)' END + ' will be defragged;
            ' + CASE WHEN @tableName IS NULL THEN 'ALL tables' ELSE 'The ' + @tableName + ' TABLE' END + ' will be defragged;
            We' + CASE WHEN EXISTS(SELECT Top 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS NULL)
                AND @forceRescan <> 1 THEN ' WILL NOT' ELSE ' WILL' END + ' be rescanning indexes;
            The scan will be performed in ' + @scanMode + ' mode;
            You want to limit defrags to indexes with' + CASE WHEN @maxPageCount IS NULL THEN ' more than ' 
                + CAST(@minPageCount AS VARCHAR(10)) ELSE
                ' BETWEEN ' + CAST(@minPageCount AS VARCHAR(10))
                + ' AND ' + CAST(@maxPageCount AS VARCHAR(10)) END + ' pages;
            Indexes will be defragged' + CASE WHEN @editionCheck = 0 OR @onlineRebuild = 0 THEN ' OFFLINE;' ELSE ' ONLINE;' END + '
            Indexes will be sorted in' + CASE WHEN @sortInTempDB = 0 THEN ' the DATABASE' ELSE ' TEMPDB;' END + '
            Defrag operations will utilize ' + CASE WHEN @editionCheck = 0 OR @maxDopRestriction IS NULL 
                THEN 'system defaults for processors;' 
                ELSE CAST(@maxDopRestriction AS VARCHAR(2)) + ' processors;' END + '
            You' + CASE WHEN @printCommands = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to PRINT the ALTER INDEX commands; 
            You' + CASE WHEN @printFragmentation = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to OUTPUT fragmentation levels; 
            You want to wait ' + @defragDelay + ' (hh:mm:ss) BETWEEN defragging indexes;
            You want to run in' + CASE WHEN @debugMode = 1 THEN ' DEBUG' ELSE ' SILENT' END + ' mode.';
 
            RAISERROR(@debugMessage, 0, 42) WITH NOWAIT;
 
        END;
 
        IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NOWAIT;
 
        /* Retrieve the list of databases to investigate */
        /* If @database is NULL, it means we want to defrag *all* databases */
        IF @database IS NULL
        BEGIN
 
            INSERT INTO #databaseList
            SELECT database_id
                , name
                , 0 -- not scanned yet for fragmentation
            FROM sys.databases
            WHERE [name] NOT IN ('master', 'tempdb')-- exclude system databases
                AND [state] = 0 -- state must be ONLINE
                AND is_read_only = 0;  -- cannot be read_only
 
        END;
        ELSE
        /* Otherwise, we're going to just defrag our list of databases */
        BEGIN
 
            INSERT INTO #databaseList
            SELECT database_id
                , name
                , 0 -- not scanned yet for fragmentation
            FROM sys.databases AS d
            JOIN dbo.dba_parseString_udf(@database, ',') AS x
                ON d.name = x.stringValue
            WHERE [name] NOT IN ('master', 'tempdb')-- exclude system databases
                AND [state] = 0 -- state must be ONLINE
                AND is_read_only = 0;  -- cannot be read_only
 
        END; 
 
        /* Check to see IF we have indexes in need of defrag; otherwise, re-scan the database(s) */
        IF NOT EXISTS(SELECT Top 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS NULL)
            OR @forceRescan = 1
        BEGIN
 
            /* Truncate our list of indexes to prepare for a new scan */
            TRUNCATE TABLE dbo.dba_indexDefragStatus;
 
            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 dbo.dba_indexDefragStatus
                (
                      databaseID
                    , databaseName
                    , objectID
                    , indexID
                    , partitionNumber
                    , fragmentation
                    , page_count
                    , range_scan_count
                    , scanDate
                )
                SELECT
                      ps.database_id AS 'databaseID'
                    , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
                    , ps.[object_id] AS 'objectID'
                    , ps.index_id AS 'indexID'
                    , ps.partition_number AS 'partitionNumber'
                    , SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
                    , SUM(ps.page_count) AS 'page_count'
                    , os.range_scan_count
                    , GETDATE() AS 'scanDate'
                FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL, @scanMode) AS ps
                JOIN sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL) AS os
                    ON ps.database_id = os.database_id
                    AND ps.[object_id] = os.[object_id]
                    AND ps.index_id = os.index_id
                    AND ps.partition_number = os.partition_number
                WHERE avg_fragmentation_in_percent >= @minFragmentation 
                    AND ps.index_id > 0 -- ignore heaps
                    AND ps.page_count > @minPageCount 
                    AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
                GROUP BY ps.database_id 
                    , QUOTENAME(DB_NAME(ps.database_id)) 
                    , ps.[object_id]
                    , ps.index_id 
                    , ps.partition_number 
                    , os.range_scan_count
                OPTION (MAXDOP 2);
 
                /* Do we want to exclude right-most populated partition of our partitioned indexes? */
                IF @excludeMaxPartition = 1
                BEGIN
 
                    SET @excludeMaxPartitionSQL = '
                        SELECT ' + CAST(@databaseID AS VARCHAR(10)) + ' AS [databaseID]
                            , [object_id]
                            , index_id
                            , MAX(partition_number) AS [maxPartition]
                        FROM [' + DB_NAME(@databaseID) + '].sys.partitions
                        WHERE partition_number > 1
                            AND [rows] > 0
                        GROUP BY object_id
                            , index_id;';
 
                    INSERT INTO #maxPartitionList
                    EXECUTE sp_executesql @excludeMaxPartitionSQL;
 
                END;
 
                /* Keep track of which databases have already been scanned */
                UPDATE #databaseList
                SET scanStatus = 1
                WHERE databaseID = @databaseID;
 
            END
 
            /* We don't want to defrag the right-most populated partition, so
               delete any records for partitioned indexes where partition = MAX(partition) */
            IF @excludeMaxPartition = 1
            BEGIN
 
                DELETE ids
                FROM dbo.dba_indexDefragStatus AS ids
                JOIN #maxPartitionList AS mpl
                    ON ids.databaseID = mpl.databaseID
                    AND ids.objectID = mpl.objectID
                    AND ids.indexID = mpl.indexID
                    AND ids.partitionNumber = mpl.maxPartition;
 
            END;
 
            /* Update our exclusion mask for any index that has a restriction ON the days it can be defragged */
            UPDATE ids
            SET ids.exclusionMask = ide.exclusionMask
            FROM dbo.dba_indexDefragStatus AS ids
            JOIN dbo.dba_indexDefragExclusion AS ide
                ON ids.databaseID = ide.databaseID
                AND ids.objectID = ide.objectID
                AND ids.indexID = ide.indexID;
 
        END
 
        SELECT @debugMessage = 'Looping through our list... there are ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!'
        FROM dbo.dba_indexDefragStatus
        WHERE defragDate IS NULL
            AND page_count BETWEEN @minPageCount AND ISNULL(@maxPageCount, page_count);
 
        IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NOWAIT;
 
        /* Begin our loop for defragging */
        WHILE (SELECT COUNT(*) 
               FROM dbo.dba_indexDefragStatus 
               WHERE (
                           (@executeSQL = 1 AND defragDate IS NULL) 
                        OR (@executeSQL = 0 AND defragDate IS NULL AND printStatus = 0)
                     )
                AND exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) = 0
                AND page_count BETWEEN @minPageCount AND ISNULL(@maxPageCount, page_count)) > 0
        BEGIN
 
            /* Check to see IF we need to exit our loop because of our time limit */        
            IF ISNULL(@enddatetime, GETDATE()) < GETDATE()
            BEGIN
                RAISERROR('Our time limit has been exceeded!', 11, 42) WITH NOWAIT;
            END;
 
            IF @debugMode = 1 RAISERROR('  Picking an index to beat into shape...', 0, 42) WITH NOWAIT;
 
            /* Grab the index with the highest priority, based on the values submitted; 
               Look at the exclusion mask to ensure it can be defragged today */
            SET @getIndexSQL = N'
            SELECT TOP 1 
                  @objectID_Out         = objectID
                , @indexID_Out          = indexID
                , @databaseID_Out       = databaseID
                , @databaseName_Out     = databaseName
                , @fragmentation_Out    = fragmentation
                , @partitionNumber_Out  = partitionNumber
                , @pageCount_Out        = page_count
            FROM dbo.dba_indexDefragStatus
            WHERE defragDate IS NULL ' 
                + CASE WHEN @executeSQL = 0 THEN 'AND printStatus = 0' ELSE '' END + '
                AND exclusionMask & Power(2, DatePart(weekday, GETDATE())-1) = 0
                AND page_count BETWEEN @p_minPageCount AND ISNULL(@p_maxPageCount, page_count)
            ORDER BY + ' + @defragOrderColumn + ' ' + @defragSortOrder;
 
            SET @getIndexSQL_Param = N'@objectID_Out        INT OUTPUT
                                     , @indexID_Out         INT OUTPUT
                                     , @databaseID_Out      INT OUTPUT
                                     , @databaseName_Out    NVARCHAR(128) OUTPUT
                                     , @fragmentation_Out   INT OUTPUT
                                     , @partitionNumber_Out INT OUTPUT
                                     , @pageCount_Out       INT OUTPUT
                                     , @p_minPageCount      INT
                                     , @p_maxPageCount      INT';
 
            EXECUTE sp_executesql @getIndexSQL
                , @getIndexSQL_Param
                , @p_minPageCount       = @minPageCount
                , @p_maxPageCount       = @maxPageCount
                , @objectID_Out         = @objectID         OUTPUT
                , @indexID_Out          = @indexID          OUTPUT
                , @databaseID_Out       = @databaseID       OUTPUT
                , @databaseName_Out     = @databaseName     OUTPUT
                , @fragmentation_Out    = @fragmentation    OUTPUT
                , @partitionNumber_Out  = @partitionNumber  OUTPUT
                , @pageCount_Out        = @pageCount        OUTPUT;
 
            IF @debugMode = 1 RAISERROR('  Looking up the specifics for our index...', 0, 42) WITH NOWAIT;
 
            /* Look up index information */
            SELECT @updateSQL = N'UPDATE ids
                SET schemaName = QUOTENAME(s.name)
                    , objectName = QUOTENAME(o.name)
                    , indexName = QUOTENAME(i.name)
                FROM dbo.dba_indexDefragStatus AS ids
                INNER JOIN ' + @databaseName + '.sys.objects AS o
                    ON ids.objectID = o.[object_id]
                INNER JOIN ' + @databaseName + '.sys.indexes AS i
                    ON o.[object_id] = i.[object_id]
                    AND ids.indexID = i.index_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 ids.databaseID = ' + CAST(@databaseID AS VARCHAR(10));
 
            EXECUTE sp_executesql @updateSQL;
 
            /* Grab our object names */
            SELECT @objectName  = objectName
                , @schemaName   = schemaName
                , @indexName    = indexName
            FROM dbo.dba_indexDefragStatus
            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 are 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('  Checking for indexes that do NOT allow page locks...', 0, 42) WITH NOWAIT;
 
            /* Determine if page locks are allowed; for those indexes, we need to always REBUILD */
            SELECT @allowPageLockSQL = 'SELECT @allowPageLocks_OUT = COUNT(*)
                                        FROM ' + @databaseName + '.sys.indexes
                                        WHERE object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
                                            AND index_id = ' + CAST(@indexID AS VARCHAR(10)) + '
                                            AND Allow_Page_Locks = 0;'
                , @allowPageLockSQL_Param = '@allowPageLocks_OUT INT OUTPUT';
 
            EXECUTE sp_executesql @allowPageLockSQL, @allowPageLockSQL_Param, @allowPageLocks_OUT = @allowPageLocks 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)
                AND @allowPageLocks = 0
            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, 
               or if the index does not allow page locks, REBUILD it */
            ELSE IF (@fragmentation >= @rebuildThreshold OR @allowPageLocks <> 0)
                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 sort operation preferences */
                IF @sortInTempDB = 1 
                    SET @rebuildCommand = @rebuildCommand + N', SORT_IN_TEMPDB = ON';
                ELSE
                    SET @rebuildCommand = @rebuildCommand + N', SORT_IN_TEMPDB = 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
            ELSE
                /* Print an error message if any indexes happen to not meet the criteria above */
                IF @printCommands = 1 OR @debugMode = 1
                    RAISERROR('We are unable to defrag this index.', 0, 42) WITH NOWAIT;
 
            /* Are we executing the SQL?  IF so, do it */
            IF @executeSQL = 1
            BEGIN
 
                SET @debugMessage = 'Executing: ' + @sqlCommand;
 
                /* Print the commands we're executing if specified to do so */
                IF @printCommands = 1 OR @debugMode = 1
                    RAISERROR(@debugMessage, 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
                    , sqlStatement
                )
                SELECT
                      @databaseID
                    , @databaseName
                    , @objectID
                    , @objectName
                    , @indexID
                    , @indexName
                    , @partitionNumber
                    , @fragmentation
                    , @pageCount
                    , @datetimestart
                    , @sqlCommand;
 
                SET @indexDefrag_id = SCOPE_IDENTITY();
 
                /* Wrap our execution attempt in a TRY/CATCH and log any errors that occur */
                BEGIN TRY
 
                    /* 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;
 
                END TRY
                BEGIN CATCH
 
                    /* Update our log with our error message */
                    UPDATE dbo.dba_indexDefragLog
                    SET dateTimeEnd = GETDATE()
                        , durationSeconds = -1
                        , errorMessage = ERROR_MESSAGE()
                    WHERE indexDefrag_id = @indexDefrag_id;
 
                    IF @debugMode = 1 
                        RAISERROR('  An error has occurred executing this command! Please review the dba_indexDefragLog table for details.'
                            , 0, 42) WITH NOWAIT;
 
                END CATCH
 
                /* Just a little breather for the server */
                WAITFOR DELAY @defragDelay;
 
                UPDATE dbo.dba_indexDefragStatus
                SET defragDate = GETDATE()
                    , printStatus = 1
                WHERE databaseID       = @databaseID
                  AND objectID         = @objectID
                  AND indexID          = @indexID
                  AND partitionNumber  = @partitionNumber;
 
            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 OR @debugMode = 1 
                    PRINT ISNULL(@sqlCommand, 'error!');
 
                UPDATE dbo.dba_indexDefragStatus
                SET printStatus = 1
                WHERE databaseID       = @databaseID
                  AND objectID         = @objectID
                  AND indexID          = @indexID
                  AND partitionNumber  = @partitionNumber;
            END
 
        END
 
        /* Do we want to output our fragmentation results? */
        IF @printFragmentation = 1
        BEGIN
 
            IF @debugMode = 1 RAISERROR('  Displaying a summary of our action...', 0, 42) WITH NOWAIT;
 
            SELECT databaseID
                , databaseName
                , objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , range_scan_count
            FROM dbo.dba_indexDefragStatus
            WHERE defragDate >= @startdatetime
            ORDER BY defragDate;
 
        END;
 
    END TRY
    BEGIN CATCH
 
        SET @debugMessage = ERROR_MESSAGE() + ' (Line Number: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + ')';
        PRINT @debugMessage;
 
    END CATCH;
 
    /* When everything is said and done, make sure to get rid of our temp table */
    DROP TABLE #databaseList;
    DROP TABLE #processor;
    DROP TABLE #maxPartitionList;
 
    IF @debugMode = 1 RAISERROR('DONE!  Thank you for taking care of your indexes!  :)', 0, 42) WITH NOWAIT;
 
    SET NOCOUNT OFF;
    RETURN 0;
END
0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , , , , , , . Bookmark the permalink.

148 Responses to Index Defrag Script, v4.1

  1. Pingback: Recommended reading for anyone dealing with MSSQL | Clarion Edge

  2. Pingback: Log Buffer #225, A Carnival of the Vanities for DBAs | The Pythian Blog

  3. Martin says:

    Hi Michelle,

    Thanks for making this available. Just an exceedingly minor point

    FROM [' + DB_NAME(@databaseID) + '].sys.partitions

    would be better off as

    FROM ‘ + QUOTENAME(DB_NAME(@databaseID)) + ‘.sys.partitions

    to deal correctly with any database names containing the “]” character

  4. Martin says:

    … And on a CS collation DATETIMEStart needs to be dateTimeStart

  5. phil says:

    Super script.

    Have found some bugs,
    1) @fragmentation_Out is set as INT where @fragmentation, and the table columns are floats.
    2) If you run the sp with @executeSQL = 0 and @database = NULL, it will populate the Status table with all the indexes to process. However if you then run the sp again with @executeSQL = 1 and specify a value for @database, all the rows in the status table get processed, regardless of the database.

  6. Pingback: Index Defrag Script, v4.0 « SQL Fool

  7. Pingback: Index Defrag Script, v3.0 « SQL Fool

  8. Pingback: Index Defrag Script « SQL Fool

  9. Pingback: Index Defrag Script « SQL Fool

  10. Chris says:

    This is by far the best script I’ve found for db maintnence, although have noted a slight bug in our environment (probably the way the applications work more than anything).
    We run it nightly for 30 minutes at a time with a force rescan on weekends. During the week, if an object is dropped / index recreated after the initial scan, the job fails in the next run.

    I’ve amended it on our environment to remove the object from dba_indexDefragStatus if it comes back without an object / index name after the /* Look up index information */.

    Just thought to let you know.

  11. Babji says:

    The script was excellent, it would be great if you can add the code for HTML report upon the completion. so that we can see the fragmentation level of pre index and post indexing.

  12. AMax says:

    Awesome script. I have been using it in our production systems for over a year now. Other than what phil mentione above, the @database variable is a bit too short for instances with a lot of databases. I increased it to VARCHAR(4000). Just ran into this on a new instance we are taking over.

  13. Skip Kelley says:

    What is the format for the days exclusion to put into the dba_indexDefragExclusion table? I want to exclude an entire dbatabase (SharePoint_Config) since Mircosoft recommends not doing this outside of SharePoint.

    UPDATE ids
    SET ids.exclusionMask = ide.exclusionMask
    FROM dbo.dba_indexDefragStatus AS ids
    JOIN dbo.dba_indexDefragExclusion AS ide
    ON ids.databaseID = ide.databaseID
    AND ids.objectID = ide.objectID
    AND ids.indexID = ide.indexID;

    This is a great procedure and has greatly simplified my life.

  14. Sven says:

    Hey Michelle,
    I’ve got a little question about the script:
    We are running this script daily, the job starts with:

    EXEC master.dbo.dba_indexDefrag_sp
    @timeLimit = 120,
    @maxPageCount = 3000000

    There’s no @forceRescan parameter given (so standard = 0, no rescan)
    Now all indexes will be defragmented (except those > 3000000 page count).
    We have 1 big index, this one stays fragmented (stays in dbo.dba_indexDefragStatus as not defragmented, while other indexes are defragmented now).

    In this case, nothing will happen anymore with your script…
    Because the script will check if there were still indexes to defrag (the 1 big index, but with those parameters, the defragmentation will not start for this 1), he will find one and not rescan the databases… while all other indexes are defragmented (done) in the dbo.dba_indexDefragStatus.

    Ok I could use the @forceRescan parameter, but I don’t want to rescan the whole instance each day (timeloss and performance loss, I’ve only a 2hours maintenance window available)

    Wouldn’t it be better to add the parameters in the check to see IF we have indexes in need of defrag?

  15. Shaun says:

    Love this routine! Thanks! I’m replacing my current script with this one across all our servers. Would love it even more if the log contained the post-defrag fragmentation level too :-)

  16. Patrice Boissonneault says:

    Sounds like a great script. Just a little problem on installation, I get the following error…

    Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 411
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

  17. rick says:

    This is an excellent tool. Thanks!

  18. David says:

    How can I just log the fragmentation with out defraging or rebuilding. I want to track the fragmentation over time for baselining.

  19. cyborg says:

    Is there any specific reason to use OPTION (MAXDOP 2); on dmv query?

  20. cyborg says:

    Is there any specific reason to use OPTION (MAXDOP 2); on dmv query? I am having 8 CPUs

  21. phil says:

    @David, guessing a combination of @executeSQL = 0 & @forceRescan = 1, will do the capturing part, but you’d have to move the results from dbo.indexDefragStatus into another table to store it.

  22. Jeremy says:

    First of all – great script!
    Second, I found an error where an object name (index name) is the full 128 characters. When using the QuoteName() function, the storage in the tables and the variables in the script need to be upped to 130 characters to allow for the storage of the ‘[]‘ characters.

  23. Sven says:

    Why is my posts removed?

  24. debbie says:

    when compiling the procedure, i get this msg

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

    thoughts?

  25. kriki says:

    A question: why did you remove the @rebuildStats-option?
    After doing a defrag, it is better the stats are recalculated.
    Especially using your tools on a regular basis: indexes will probably never become much fragmented to trigger a rebuild. So it will always do a defrag never updating the statistics and I do NOT want them to be triggered at random.

  26. Hi Michelle, I have a recommendation for the next release.

    In this version, the table dba_indexDefragStatus is truncated in case of a @ForceRescan. This means it is deleting all outstanding re-index actions from all databases. Since I run this script at various times for various databases, it is currently also deleting remaining index actions for other databases. My suggestion is to change the “truncate table” statement to “delete from … where databasename = @databasename”

  27. Correction: the delete statement should be: “databasename in (select name from #databaselist)”

  28. Rob says:

    Hi Michelle,

    Great script!

    It would be great though, if you could add the option to choose between ONLINE REORGANIZE of individual index partitions (like it does now) and ONLINE REBUILD of the complete partitioned index.

    We can’t afford rebuilding partitions offline, and an online reorganize does not reduce fragmentation levels enough.

    Another idea: if would be nice to be able to exclude certain databases (without having to add each individual table of that database into the dba_indexDefragExclusion table.

  29. Dirk Hondong says:

    Hi Michelle,

    once again: thank you for this great solution.
    I think the idea to be able to exclude certain databases ( as Rob mentioned ) would be a nice feature.
    Maybe the same variable @database but when you wish to exclude a database you have to type ‘-YourDatabaseName’.

  30. Brendan H says:

    Michelle,

    Great script!

    One thing I ran across.

    If looking to just print the commands (@executeSQL = 0) with print fragmentation results on (@printFragmentation = 1) the results at the end of executing the procedure do not display as the select statement is based off of defragDate which is NULL in table dba_indexDefragStatus because I did not execute.

    I feel some might want to execute the procedure to receive the commands only but to also view the results of the current fragmentation without executing.

    Just an FYI for possible improvements.

    Thanks again for the script!

  31. Bug when selecting a tablename?

    It seems that a single table defrag is not working if the tablename is not in the database where this SP is installed. This because the OBJECT_ID(@tablename) is always returning NULL. As a result the complete database is processed, taking a very long time. To fix this, the object_ID() function must use the 3-part name convention)

  32. Wes says:

    Hi. first off, many thanks for sharing this excellent script. I’ve noticed some weird behaviour today though.
    I copied this last week and have shared it with my team and we’ve all used it successfully. However, when I opened it today to execute it on a different server I’m getting the error “Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 464. Incorrect syntax near ‘OBJECT_ID’.” which is around this line…

    ” , GETDATE() AS ‘scanDate’
    FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL, @scanMode) AS ps
    JOIN sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL) AS os …”

    I thought someone must have edited the script by mistake so I re-downloaded and the same thing happened. I then thought it must be SSMS playing up, so tried SSMS from a different machine to a different server and same thing. I copied the offending select part of the Insert statement to a new window, declared the variables used within the select and the query will parse. Any ideas?

    Using:-

    Microsoft SQL Server Management Studio 10.50.1617.0
    Microsoft Data Access Components (MDAC) 3.85.1132
    Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
    Microsoft Internet Explorer 8.0.6001.18702
    Microsoft .NET Framework 2.0.50727.3623
    Operating System 5.1.2600

  33. Pingback: Update Statistics fails with Error Number:–1073548784 "Could not allocate space for object 'dbo.SORT” - REPLTalk covers Using and Tuning SQL Replication - Site Home - MSDN Blogs

  34. We needed this for fast changing tables with several thousand pages. We have to apply online defrag due to the characteristics of data

  35. Mike says:

    This is a great script! Thanks!

    I’m not seeing any options to “prune” the “dba_indexDefragLog” table. Does this just continue to grow?

    Thanks!!

  36. Roy says:

    I think I found the solution for the below error
    “Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 464. Incorrect syntax near ‘OBJECT_ID’.”

    All you just need to do is to change the database Compatibility level to either “SQL Server 2005 (90)” or “SQL Server 2008 (100)”

  37. Jason says:

    Hello,

    Thanks for sharing your great backup script. Is there any way we could get logic added to rebuild statistics if an index is reorganized?

    Thanks again!

  38. Chaz says:

    Hey Michelle and everyone…

    I made some additions to this script to provide some of the functionality asked for in the replies (those I thought would be good) and some that I felt would make this a fully rounded procedure. I am willing to share what I have done and was wondering if I could get this to Michelle for her to look over.

    I will admit I am not the expert at T-SQL so I most likely perfomed some things incorrectly (though they all seem to work in my environment.) I would love it if someone (like an expert) could really look over my code and let me know if the see some common issues.

    Thanks

  39. Dave says:

    Thanks for the awesome script!

  40. Anish says:

    Wonderful script!
    However, would it be possible to exclude certain indexes from being rebuilt/re-organized altogether. In other words what value do I assign in ExclusionMask in the dba_indexDefragExclusion table to exclude the index for all days?

  41. Anish says:

    For some reason my previous comment did not get published….

    My question is…how can we exclude some indexes from being rebuilt/reorganized at all? In other words what should I set in the exclusion mask to ignore rebuilding the index on all days?

    Thanks in advance. Other than that, this script is superb :)

    Cheers!

  42. Tara Shankar Jana says:

    This is an awesome script.. i loved testing this, changing it as per my own need and the parameters thought are absolutely inline and captures most of the information…

    I have one question and don’t know how to go about doing this, please do let me know if their is a way out or you can help me out getting the code done.

    The customer of mine wants me to segregate indexes based on the operation used:

    1) They want to run Rebuild index online for some of the tables and we need to to automate that without doing any manual work, similarly reorg.

    2) Also, they want to segregate the indexes which needs rebuilding of indexes offline and they wont run as part of the script provided, but rather it has to pick the details and dump into an exclusion table and later the commands should be picked up and executed (by a job or in any automated way possible), also they wish to exclude all those indexes which they know they can’t do much about (they keep getting defragged, small tables).

    3) Keeping all this in mind, i looked at the script its very close to what we require except the above 2 points..
    Rebuilding indexes offline needs change request here at my clients place and hence possibly keeping them in some store and then executing it when the maintenance window is enabled. (no manual work, only automation required)

    4) Also, i wish to know if i wish to complete the defrag in 4 hours, i specify the time in the parameter, question is what if it doesn’t complete within the time range specified, i know there is a parameter named forcescan, but do you think its a good way to go for stopping the indexing at a specific time frame? what will be the state of the database? please help me with these questions would be a lot of help.

    You can also mail me at: shanku02@yahoo.co.in

    Thanks and Regards,
    Tara

  43. Gary Smith says:

    Michelle, thanks for making this script available. It’s great.
    How would you recommend redirecting the ‘debugMode = 1′ and @debugMessage messages to a log file?
    Thank you.

  44. Jason says:

    One more request… Could you add logic involving the use of the index exlusion table? Right now you can only define one day to exclude an index from being defragmented. It would be nice to have an option to exclude all days or multiple days instead of just one day per index.

    Thanks!

  45. Andrew T. Fry says:

    @Jason: The logic you’ve requested should already be in there. The exclusion mask for days is just that a bit flag mask for the days that should be excluded. The values are listed in the script and just need to be added together to come up with the integer equivalent of the bits required. So for Monday and Sunday exclusion you would enter 3 which sets the bit mask as 0000011. An exclusion of all days would look like this in binary 1111111 or 127 as an integer value.

  46. Christophe says:

    Hi Michelle,

    First, thank you for this great script, it’s exactly what I searched.

    Just one question: would it be possible to add a function to send a mail with the result, a kind of report?

    Thanks.

  47. Cameron says:

    Thanks for taking the time to build this script and sharing.

  48. gary says:

    Thanks for the great script.

    It would be great if you can provide similar script for backups & recovery!!!!

  49. Ron Moses says:

    Thanks for the updated script, Michelle. In comparing it to the 2008 version I’ve been using, one thing I notice is the removal of DBCC SHRINKFILE. I know db shrinking is a point of contention among most db developers, so I supposed I’m not surprised to see it gone. I’m just wondering what your specific reasons are for removing it, and perhaps for including it in the first place.

  50. Pingback: RedGate SQL Index Manager BETA « Tradney's Blog

  51. Gary says:

    What is the use of creating the function dba_parseString_udf?

  52. Vishal says:

    should we add one more parameter, so if there is LOB data then first we need to reorganize index but after that it still more fragmented then do rebuild on that ?

  53. Meghana says:

    Hi ,

    Great Script…

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

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

  54. Pingback: SQL Server Tools | Jeremy Reid's Website

  55. James Howard says:

    Fantastic script with superb flexibility. Thanks very much.

  56. Pingback: Selectively Updating Statistics

  57. Noah says:

    Great tool.
    Is there a way to compare Fragmentation Levels side-by-side Before and After the running this SP?

  58. Pingback: SharePoint SQL Server Performance Tuning Roundup | SharePoint 2010 Performance Blog

  59. Pingback: SharePoint SQL Server Performance Tuning Roundup | SharePoint 2010 Performance Blog

  60. Pingback: SQL Server isn’t always perfect - SQL Server with Mr. Denny

  61. Pingback: Statistics and Recompilations, Part II | Erin Stellato | Erin Stellato

  62. Mattias Axelsson says:

    @Patrice
    Old comment/question but could be of help for someone else:

    Change
    ON d.name = x.stringValue
    To
    ON d.name COLLATE DATABASE_DEFAULT = x.stringValue COLLATE DATABASE_DEFAULT

  63. harry says:

    I run the script an d got the error:
    Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 411
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1255_CI_AS” and “Hebrew_CI_AS” in the equal to operation.

  64. Yigit Aktan says:

    Well done. Pretty great tool that I had ever try to maintanence on indexes.

    Maybe If you should modify a little bit change.

    You are checking the Online rebuild eligibility on this part of your code (1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer) ;

    IF (SELECT ServerProperty(‘EditionID’)) IN (1804890536, 610778273, -2117995310)

    But you didn’t add a SQL Server 2008 R2 Datacenter Edition ID into your code.

    On the next version don’t forget to add -978676123 = Datacenter ID in to your code. Because, you can use Online rebuild processing with Datacenter Edition too.

    Kind Regards,
    Yigit.

  65. Phil says:

    when i run the script to create it first time i get:

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

  66. Tim Edwards says:

    Phil,

    For your OBJECT_ID error, check to make sure that the compatibility level of the database that you are creating the stored procedure in is 90 or higher. If the database is in compatibility mode 80, it will generate the OBJECT_ID error you are experiencing.

    Tim

  67. Foustrouka says:

    Hello there,

    Great script, I have ran this script on one of our servers and the transcactional log backup is huge and makes are log shipping to break do you know a way to fix this or maybe what should we look at?

    Much appreciate your help

  68. Paul says:

    Great script! Only change I made to it was to include a @FillFactor variable that let me specify the fill factor to use when rebuilding.

  69. Pingback: One Hour Server

  70. David says:

    When I run your script, I get this message.

    Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 411
    Cannot resolve the collation conflict between “Latin1_General_CI_AS_KS_WS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

    Will you please help?

  71. Rob says:

    I’m sorry for this being so silly, but is there a trick to actually making this work?
    The tables are created and I exec the sp, but nothing happens. All the tables are still empty and it seems nothing changes.
    I’ve tried looking back at previous posts of your different versions, but can’t seem to find any kind of trick to actually working this for those of us sql newbies.

  72. Hi Rob,

    Try running this. It should return some messages to you. Do you see anything?

    EXECUTE dbo.dba_indexDefrag_sp
    @executeSQL = 1
    , @printCommands = 1
    , @debugMode = 1
    , @printFragmentation = 1
    , @forceRescan = 1
    , @maxDopRestriction = 1
    , @minPageCount = 8
    , @maxPageCount = NULL
    , @minFragmentation = 1
    , @rebuildThreshold = 30
    , @defragDelay = ’00:00:05′
    , @defragOrderColumn = ‘page_count’
    , @defragSortOrder = ‘DESC’
    , @excludeMaxPartition = 1
    , @timeLimit = NULL;

  73. David, thanks for letting me know about that. I’ll check into the collation issue while I’m working on the next version.

  74. @Paul – Thanks for the suggestion!

    @Foustrouka – Try breaking it up into chunks, i.e. run with @minPageCount = 8 and @maxPageCount = 1000, then @minPageCount = 1000 and @maxPageCount = 10000, etc. Also, if you have really large tables (i.e. >100GB for a single table), you may want to consider partitioning them. If you can’t partition them, you’ll want to add them to the exclusion list and defrag those manually.

  75. Rob says:

    Michelle – it actually ended up running – I just didn’t know it would take a long time to fill the tables.

  76. John H says:

    Hi Michelle,

    I found your script while looking for tools to help get our SQL server under control. I haven’t had a chance to try it (although I think we were running an earlier version on our previous server), but I wanted to point out a problem for some of us. The section that inserts into dbo.dba_indexDefragLog lists column DATETIMEStart, but the create statement names the column dateTimeStart. In our case we have case-sensitivity on, which caused the script to fail until I tweaked the insert statement.

  77. John G says:

    Thanks for a great script. When I wrapped it in a job I found it was failing in my SharePoint environment. It turns out that a handful of tables have ALLOW_PAGE_LOCKS set to false which means the index cannot be rebuilt so I added a loop to delete the indexes from the to do list when that condition occurs.

    I’ve modified the script quite a bit so I don’t think my “patch” is compatible with your latest version, but the field to check is (dbname).sys.indexes.allow_page_lock which must be true (1) for rebuilding to take place.

  78. John G says:

    Michelle-

    After my last comment I was successfully able to incorporate my patch for checking the ALLOW_PAGE_LOCKS option into version 4.1 of your script and deploy it to a couple of servers. Please contact me if you would like the code for your next version, this is a great tool and I’d love to be able to contribute to its development.

  79. Pingback: A hole in SQL maintenance « nujakcities

  80. Pingback: Database Maintenance for Microsoft SharePoint 2010 Products | SharePoint 2010 Performance Blog

  81. SimonF says:

    Great script, good work Michelle, thank you! :)

  82. Brian says:

    First of all I greatly appreciate the hard work that was done on this script. We do not have any DBA so I am the one in charge of our DB and we have a huge Fragmentation level 99% on most of our database tables.

    I am still struggling to get this going, to your post to rob about

    EXECUTE dbo.dba_indexDefrag_sp
    @executeSQL = 1
    , @printCommands = 1
    , @debugMode = 1
    , @printFragmentation = 1
    , @forceRescan = 1
    , @maxDopRestriction = 1
    , @minPageCount = 8
    , @maxPageCount = NULL
    , @minFragmentation = 1
    , @rebuildThreshold = 30
    , @defragDelay = ’00:00:05′
    , @defragOrderColumn = ‘page_count’
    , @defragSortOrder = ‘DESC’
    , @excludeMaxPartition = 1
    , @timeLimit = NULL;

    Those settings above is it something that I need to add into the script to be functional or is there already a spot to fill in all that information?

    Any help would be appreciated.

    Thanks

  83. Brian says:

    Disregard, I figured it out finally :)

  84. Lasse N says:

    Hi

    I just found your script, and it look promising, i just have one feature request, is it possible to add the ability to switch a database to SIMPLE logging before doing any Rebuild/Reorganize, and then back to FULL logging afterward?

    The reason for this is that, a few of our databases are quite large, and the space on the drive where they are are limited, so if the Databases are in FULL logging mode when running, it will just fill the drive, which would be bad.

  85. ashok says:

    hi dear
    Can u give us any solution which will work in sql azure

    Thanks

  86. Mauricio says:

    Hi Michelle. This is one great script! I had created a simpler one, but will change to yours asap. I hope you don’t mind if I republish this too, of course making a link and giving proper credit to you. Also if I do any improvement I will let you know.

    thanks!

  87. Pingback: Sharing knowledge: Index Defrag Script | The Lonely DBA

  88. Pingback: Database Maintenance Strategies for Dynamics AX | MSDN Blogs

  89. PeterD says:

    Ah. The reason for the error of Object_ID is in the compatibility mode of the database. It has to be over 90

  90. sap4ora says:

    Very good script and thanks for all your work. Could this be run from a central location against a number of servers also what is the @database parameter for multiple databases; having @database=NULL runs the script against all databases including system.

    Thanks again,

  91. Brett says:

    Firstly, thank you for this amazing work!
    I am having a problem , even after running this
    EXECUTE dbo.dba_indexDefrag_sp
    @executeSQL = 1
    , @printCommands = 1
    , @debugMode = 1
    , @printFragmentation = 1
    , @forceRescan = 1
    , @maxDopRestriction = 1
    , @minPageCount = 1
    , @maxPageCount = NULL
    , @minFragmentation = 1
    , @rebuildThreshold = 30
    , @onlineRebuild = 1
    , @sortInTempDB = 1
    , @defragDelay = ’00:00:05′
    , @defragOrderColumn = ‘page_count’
    , @defragSortOrder = ‘DESC’
    , @excludeMaxPartition = 1
    , @timeLimit = NULL
    , @database = ‘EWS_LIVE,RCC_LIVE,RSI_Live,B1if’;

    I still end up with over 20-70% fragmentation in objects with over 20 page, even ones with 200 pages are still reporting fragmentation.

  92. Sergio Pacheco says:

    If the database collation is case sensitive then you will get an invalid column error on the insert into “DATETIMEStart”. Easy fix just change it to “dateTimeStart” and you can run the create store procedure.

    Regards,

    Serg

  93. Pingback: Utility Database | FradenSQL

  94. Paul A says:

    I’m running SQL 2005 Standard. I have some very large tables that need to be defragged but I want to keep the indexes online during the rebuild. Is there another way to accomplish this without upgrading to Enterprise? Thank you for your script and help.

  95. Drew says:

    Michelle, This new version is a thing of beauty. I’ve tried creating several, starting from scratch and other peoples suggestions. The simplicity and effectiveness of version 4.1 made it a no-brainer to just plug into my management db on each instance. The force rescan and exclusion by date options are particularly clever. Bravo!

  96. Kudos to you! I have been using this script in our environment for a few years now and I just wanted to thank you for your efforts on this as it’s saved my TONS of time. Our databases purr like a kitten thanks to you.

  97. Drew says:

    Michelle,
    I have added a tweak that I thought I should mention. We have a couple of archive tables that I was excluding based on Max page count. This put me into a loop where the status table would not truncate, and I had force rescan set to 0 so a rescan would never happen, and the last couple of indexes would never be rebuilt. I added a line to the where clause to force a truncate when all indexes within the page count limits had been exhausted:
    IF NOT EXISTS(SELECT Top 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS NULL AND page_count BETWEEN @minPageCount AND ISNULL(@maxPageCount, page_count) )
    OR @forceRescan = 1

  98. Christophe says:

    Hello,

    Great script! Worked well in 2008R2 SP2 but when I tried it in 2012 RTM, got the message:

    “The user does not have permission to perform this action. (Line Number: 442)”

    even if i ran it as a sysadmin server role member…

    Thanks,

    Chris

  99. Christophe says:

    Hello,

    The problem was not with SQL 2012 but with databases member of a Always On availabily group. I modified the code as below:

    /* Retrieve the list of databases to investigate */
    /* If @database is NULL, it means we want to defrag *all* databases */
    IF @database IS NULL
    BEGIN
    IF @@VERSION LIKE ‘%2012%’
    EXEC (‘INSERT INTO #databaseList
    SELECT database_id
    , name
    , 0 — not scanned yet for fragmentation
    FROM master.sys.databases
    WHERE [name] NOT IN (”master”, ”tempdb”)– exclude system databases
    AND [state] = 0 — state must be ONLINE
    AND is_read_only = 0
    AND replica_id IS NULL
    UNION ALL
    SELECT DISTINCT database_id
    , name
    , 0
    FROM [sys].[dm_hadr_name_id_map] A,
    [sys].[dm_hadr_availability_group_states] B,
    [sys].[dm_hadr_database_replica_states] C,
    [sys].[sysdatabases] D
    WHERE A.ag_id=B.group_id
    AND B.group_id=C.group_id
    AND C.database_id=D.dbid
    AND B.primary_replica=@@servername — if we are on the active node
    AND B.primary_recovery_health=1′)
    ELSE
    INSERT INTO #databaseList
    SELECT database_id
    , name
    , 0 — not scanned yet for fragmentation
    FROM sys.databases
    WHERE [name] NOT IN (‘master’, ‘tempdb’)– exclude system databases
    AND [state] = 0 — state must be ONLINE
    AND is_read_only = 0; — cannot be read_only

    END;

  100. Pingback: 1 dia do evento 24h PASS « Sergio C. Fonseca Weblog

  101. Sean says:

    I have some indexes with page compression enabled. Will running this script over those indexes remove the compression?

  102. jatin says:

    hi Michelle,

    thanks for the script. that fixed my problem , which i was facing with our script .
    but , a question, there were couple of tables which were left fragmented , and they were not even small, most of them were above 1000 pages. what could be the reason for this ??

  103. Pingback: The Default Fillfactor for an Index « Voice of the DBA

  104. Kobus Pretorius says:

    Hi Michelle – thanks for sharing all your awesome scripts!

    I previously used this script and everything worked 100%!

    But now recently when I tried to run the procedure again, I get the following error (I also recreated it to ensure no changes from your version cause the problem):

    Cannot insert the value NULL into column ‘objectName’, table ‘TestDb.dbo.dba_indexDefragLog’; column does not allow nulls. INSERT fails. (Line Number: 732)

    The only thing that I can think of is that the object ID inserted from sys.dm_db_index_physical_stats does not exist in (@databaseName).sys.objects?

    Not long ago one of the guys dropped all of the tables in our master db – and it took quite some time for our ‘DBA’ to get the master db up and running again using some sort of backup. I’m not sure if this may be the reason for the problem? And how to fix this? Or maybe it’s something totally different.

  105. Shireesh says:

    I don’t see fillfactor(@fillfactor) option during the index rebuild. Am i missing or it not there in the code? Can someone please suggest me on this?

  106. Pingback: Top 10 Free SQL Server Tools of 2013 - Work Smarter This Year

  107. RedSil says:

    Hi Michelle,

    Do you already have the new script for SQL 2008 version?

    I tried it on our 2008 and it has an error:
    Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 277
    Incorrect syntax near ‘OBJECT_ID’.

    Thank you in advance.

    Red

  108. Matt W. says:

    Holy wow this is awesome! Thank you so much for sharing this!!

  109. Pingback: Ten Free SQL Server Tools that you Need to Know About | sql Hammer

  110. Pingback: SQL Server

  111. Marcos Galvani says:

    Hello,

    I am not sure if your script does this, but, shouldn’t the clustered indexes be defragmented first?

    Regards.

  112. bahadir says:

    Hi,
    the sp is amazing. It consider almost everything in rebuilding an index. But I have a suggestion. If there is a lob object then you reorganize index. but you can rebuild index as offline. maybe you can add a parameter whether user wants to offline rebuild for only lob object indexes and online rebuild for other tables.

  113. Julee Marcelino says:

    I have used this tremendous tool while at two separate employers. This has been invaluable and the logging is tremendous. I built out a job to simply grab fragmentation levels at various points of the day and then log the results to a new table that is not truncated. What I am being asked to do now is a monthly validation that checks the current fragmentation level and compares that to the dba_indexDefragLog, to validate that any index shown to be fragmented over 30% at a point-in-time at month-end, has been maintained in the last 30 days. I want to automate this on a monthly basis and think I have properly assessed the situation and needs, but don’t necessarily want to re-invent the wheel. Any ideas how to accomplish this or if anyone has had a similar request?

  114. Pingback: Easy automation of SQL Server database maintenance - Paul S. Randal

  115. Hi Michelle,
    Thanks again for doing this for the community! I noticed that ONLINE rebuilds are failing in my case. We’re running SQL Server 2012 SP1 CU3 BTW.

    The Enterprise Edition check needs to have 1872460670 added.

    http://msdn.microsoft.com/en-us/library/ms174396.aspx

    Thanks!

  116. Pingback: SQL Server 2012 defrag job runs interactively, but not as a job – why? | Question and Answer

  117. kriki says:

    Found small problem in my case:

    Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 431
    Cannot resolve the collation conflict between "Latin1_General_100_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

    and fixed it this way:

    --> ALKR
    /*OLD CODE
    ON d.name = x.stringValue
    */
    ON d.name = (x.stringValue COLLATE Latin1_General_100_CI_AS)
    --< ALKR

  118. Ugo says:

    When I run your defrag script, I get the following error collation conflict error:Msg 468, Level 16, State 9, Procedure dba_indexDefrag_sp, Line 411
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

  119. Pingback: The Accidental DBA (Day 14 of 30): Index Maintenance - Jonathan Kehayias

  120. Sumit says:

    Hello Michelle,

    Thanks for sharing defrag script with community. I have a question regarding defrag on Partition tables. It is possible to run defrag indexes only on specific filegroup since it’s a partitioned. Example : XXX_2010, XXX_2011, ……..I want to be able to run defrag indexes only on filegroup XXX_2011.

  121. Brian Jones says:

    I’ve found a small bug in the script. If there isn’t an index to defrag and printCommands is 0 and debugMode is 0, then the previous successful ALTER sql statement is executed again. I’ve solved it in my copy as follows (changes annotated with –4.2), feel free to test and publish.

    IF @printCommands = 1 OR @debugMode = 1
    RAISERROR(‘We are unable to defrag this index.’, 0, 42) WITH NOWAIT;
    ELSE –4.2
    SELECT @sqlCommand = NULL –4.2

    /* Are we executing the SQL? IF so, do it */
    –IF @executeSQL = 1 –4.2
    IF @executeSQL = 1 AND @sqlCommand IS NOT NULL –4.2
    BEGIN

  122. Justin says:

    Hi Michelle,

    Using this script updating the parameters as per our environment requirement. We have noticed significant performance improvement in search results and reduction in fragmentation levels.

    Thanks for your great work and sharing such work with the community.

  123. Mike T. says:

    Hi Michelle, I’m a DBA of MSSQL / App Admin who has been using version 4.0 of your script since 6/2011 on MSSQL Server 2008 x64 and love it. So much so, that I’m worried a day will come when you’re no longer providing it for us and it doesn’t work in a future version. :( We wouldn’t know what to do without it! It’s amazing the level of development you’ve taken this. I depend on running it every month against our 25+ live/production DB’s flawlessly, just before we do our Patch Tuesday updates. I see now I missed 4.1, so will have to test in VM and get going with implementing that. I saw you had posted somewhere that you’d like us to post comments, so I thought it was time to thank you!, thank you!, thank you!!! :) -Mike

  124. Pingback: Getting Fragmentation % of a Table | SQL TAO

  125. Eric A. says:

    Michelle,

    Excellent script! I thought it might even do my laundry!

    On the serious side, there might be a bug in the LOB handling logic: I am actually able to execute an ALTER INDEX REBUILD on a non-clustered index of a table that contains LOB columns, however, the logic in the script does not do so, instead reporting “We are unable to defrag this index.”

    Of course, if the index contained the LOB column, or if the index were a clustered index, it would be a different story — it would not be able to be rebuilt online. In this case, I think it could be rebuilt offline.

    Perhaps for your next version? Fabulous tool!- Eric

  126. Lin says:

    Heaps are excluded, possible to include them?

  127. Martyn says:

    Hi there!

    Thank you for a fantastic script it’s been working perfectly for a good while but in the last week it’s failed.

    I’ve found a problem with the script where it fails not allowing a null to be inserted into the log table.

    On review it appears that the job that calls the script has continuously succeeded but the script itself has failed without warning. When it first failed, it passed back a message “…Database x cannot be opened. It is in the middle of a restore…. The step succeeded.”. It appears that it still passed certain values into the dba_DefragStatus table, some of which were null. When the script ran again it immediately failed.

    I’ve since cleared this table where the schema, table and index names were null (related to the db that was in a restoring state) and it now works fine again.

    Many thanks for all the effort you’ve put into this and I very much look forward to the next version.

    Martyn

    P.S. I have extra info if needed so please feel free to email me if needed and I can pop some examples across. Thanks

  128. Martyn says:

    I’ve just noticed that you’re script does check for the state of the db so perhaps that’s a red herring….

  129. Sarjen Haque says:

    Lin: You can not defrag HEAP as it is not a Index.

  130. Leon Orlov says:

    Michelle you rock! Many thanks. I’ve used this code on some very large test databases and it works great.

  131. Leon Orlov says:

    Once again thank you Michelle for such a great script!
    I found that one of my sql server instances with a particular binary sort order doesn’t like name of the columns in different case… specifically for column “dateTimeStart” when it was referenced by “DATETIMEStart” on line 927. Not a bug but a minor nuance.
    Cheers
    ~Leon

  132. Baba D says:

    I’ve tried it and it’s giving the error below. I’m a newb at SQL scripting. What should i do
    Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 464
    Incorrect syntax near ‘OBJECT_ID’.

  133. Jeyakumar says:

    You can use this script for SQL 2000 databases (Compatibility level 80)

    Just install this sp in master (which Compatibility level is greater than 80) and run from there against old Compatibility level 80 databases.

  134. Robert B. says:

    When I ran the script, I kept getting “string or binary data would be truncated” errors. Here’s why:

    There is a statement that updates table the dba_indexDefragStatus table. The update statement gets a returned object name using the “QUOTENAME” function. That means that any objectname returned will be enclosed in the default of brackets ([objectname]). That adds two additional characters to the returned object name. So, if your object name uses the maximum length of 128, 130 characters are returned (the name and the brackets). Within the script and on the three database tables (dba_indexDefragExclusion,dba_indexDefragLog,dba_indexDefragStatus), some object name columns use the max length for an object name column of NVCARCHAR(128). Whoops. That won’t hold an object name with a length of 128 that includes the additional brackets.

    So, in the script, change any element defined as NVARCHAR(128) to NVARCHAR(130). Do the same in the tables. That should solve the truncation error.

  135. Paul says:

    Hi Michelle,

    I’m bit of a newbie DBA. Is there any instructions on how to use this script?

    Cheers
    Paul

  136. Pingback: SQL Index Defragmentation « Scar's Ramblings

  137. Pingback: Less Than Dot - Blog - Awesome

  138. Pingback: Less Than Dot - Blog - Awesome

  139. Hans Molin says:

    If you start a defrag which doesn’t finish (so that you get an entry in the status table with NULL date) and then add that index to exclusion. Any time you run the script after that it will not do anything unless you use forcerescan.

  140. AS says:

    Michelle,
    Great work! I’ve used an older version (v3.0) in our corporate internal databases. I see that now there are some new improvements. Can we use this script in a costumer server?
    Thanks!

  141. vacius2 says:

    Hi Michelle,
    Thanks for script.
    I have one question, recently I’ve dropped and created one index and it doesn’t seem to appear in the dba_indexDefragStatus table and defrag doesn’t occur for new index created
    , @forceRescan = 1
    Can You help me?

  142. Pingback: DATABASE MAINTENANCE STRATEGIES FOR DYNAMICS AX | ismailozcan68

  143. Pingback: Database Maintenance Strategies for Dynamics AX | ismail ozcan Erp(Dynamics Ax, Sap)

  144. adhi says:

    I have impleted successully , job also got success , But still index fragmenation showing very high in all the table. any clue to fix

  145. Rashedul Chowdhury says:

    I am defragmenting about 15 databases and few databases are not defragmenting where as the fragmentation level is between 64% to 98%. Is there any limitation of databases that can be defragmented in a single query?

  146. Ken says:

    Thanks for the script, saves me from re-writing one from scratch. Your site was recommended at a LINKEDIN site. FYI, when I wrote the script I used dates to determine if I should do something about the index. (Last defrag was over a day old) First time I ran it, 10 indexes were rebuilt, a few minutes later 6 were. (On a basically idle personal server) I couldn’t resist modifying some things, basically:
    IF ISNULL(@scanMode,’x') NOT IN (‘LIMITED’, ‘SAMPLED’, ‘DETAILED’)
    SET @scanMode = ‘LIMITED’;
    SELECT @debugMode = ISNULL(@debugMode,0), @forceRescan = ISNULL(@forceRescan,0), @sortInTempDB = ISNULL(@sortInTempDB,1);

  147. Wolfgang K. says:

    Thx for this wonderful script.

    Do you think it would be an option to make an other parameter like @showProgress and print after each reorg something like :

    Progress : 15 index of 245 index in Database [name] done

    Greetings from Austria,

    Wolfgang

Leave a Reply

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

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