Tuesday, April 23, 2013

SQL Server - Index Defragmentation Stored Procedure

I don't remember who wrote the original code for this stored procedure; however, I found it useful enough I wanted to share it with my readers.

The following script does a fantastic job of allowing the user to defragment table indexes with a high degree of control. I have tested the procedure with SQL Server 2008 and 2008 R2. Your mileage on other versions of SQL Server may vary.

Example:

EXECUTE dbo.IndexDefrag
             @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';

Source:

-------------------------------------------------------------------------------
-- Date:   October 05, 2011
-- Author: Unknown
-- Edits:  Keith R. Davis 
-- Desc:   Defragments indexes in the specified databases using the supplied 
--         options. CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY 
--         WHEN DEFRAGMENTING. DO NOT RUN UNATTENDED ON LARGE DATABASES DURING
--         NORMAL HOURS OF OPERATION. Tested on SQL Server 2008 R2.
--
-- Supporting Objects:
-          CREATE TABLE dbo.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
--           PRIMARY KEY CLUSTERED (databaseID, objectID, indexID, partitionNumber)
--       );
--
--       CREATE TABLE dbo.indexDefragExclusion
--       (
--           databaseID         INT             NOT NULL
--  , databaseName       NVARCHAR(128)   NOT NULL
--  , objectID           INT             NOT NULL
--  , objectName         NVARCHAR(128)   NOT NULL
--  , indexID            INT             NOT NULL
--  , indexName          NVARCHAR(128)   NOT NULL
--  , exclusionMask      INT             NOT NULL
--              /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */
-- 
--  CONSTRAINT PK_indexDefragExclusion 
--      PRIMARY KEY CLUSTERED (databaseID, objectID, indexID)
--         );
--  
--  CREATE TABLE dbo.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 
--        PRIMARY KEY CLUSTERED (indexDefrag_id)
--           );   
--
--   CREATE FUNCTION dbo.parseString
--   (
--      @stringToParse VARCHAR(8000)  
--    , @delimiter     CHAR(1)
--   )
--   RETURNS @parsedString TABLE (stringValue VARCHAR(128))
--   AS
--   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
--
--         Example:
--
--        EXECUTE dbo.IndexDefrag
--             @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';
--
-- Param:  @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 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 
--                                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
-- Return: 0 on success
-------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[IndexDefrag]
(    
    @MinFragmentation       FLOAT               = 10.0  
  , @RebuildThreshold       FLOAT               = 30.0  
  , @ExecuteSQL             BIT                 = 1     
  , @DefragOrderColumn      NVARCHAR(20)        = 'range_scan_count'
  , @DefragSortOrder        NVARCHAR(4)         = 'DESC'
  , @TimeLimit              INT                 = 720   -- Defaulted to 12 hours
  , @Database               VARCHAR(128)        = NULL
  , @TableName              VARCHAR(4000)       = NULL  -- databaseName.schema.tableName
  , @ForceRescan            BIT                 = 0
  , @ScanMode               VARCHAR(10)         = N'LIMITED'
  , @MinPageCount           INT                 = 8 
  , @MaxPageCount           INT                 = NULL
  , @ExcludeMaxPartition    BIT                 = 0
  , @OnlineRebuild          BIT                 = 1     
  , @SortInTempDB           BIT                 = 1
  , @MaxDopRestriction      TINYINT             = NULL
  , @PrintCommands          BIT                 = 0     
  , @PrintFragmentation     BIT                 = 0
  , @DefragDelay            CHAR(8)             = '00:00:05'
  , @DebugMode              BIT                 = 0
)
AS
                
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET QUOTED_IDENTIFIER ON;

BEGIN
    BEGIN TRY
 -- 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);
                
        -- Parameter 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('Starting up...', 0, 42) WITH NOWAIT;

        -- Initialize variables
        SELECT @StartDatetime = GETDATE()
            , @EndDatetime = DATEADD(minute, @TimeLimit, GETDATE());

        -- Create 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 the max number of processors is not being exceeded
        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 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
        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.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('Getting a list of databases...', 0, 42) WITH NOWAIT;

        -- Retrieve the list of databases to investigate
        -- If @Database is NULL, 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 the list of databases
        BEGIN

            INSERT INTO #databaseList
            SELECT database_id
                , name
                , 0 -- Not scanned yet for fragmentation
            FROM sys.databases AS d
            JOIN dbo.ParseString(@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 there are indexes in need of defrag; otherwise, re-scan the database(s)
        IF NOT EXISTS(SELECT Top 1 * FROM dbo.IndexDefragStatus WHERE defragDate IS NULL)
            OR @ForceRescan = 1
        BEGIN

            -- Truncate the list of indexes to prepare for a new scan
            TRUNCATE TABLE dbo.IndexDefragStatus;

            IF @DebugMode = 1 RAISERROR('Looping through databases and checking for fragmentation...', 0, 42) WITH NOWAIT;

            -- Loop through the 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 the user-defined parameters
                INSERT INTO dbo.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);

                -- Exclude right-most populated partition of 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

            -- Don't 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.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 the 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.IndexDefragStatus AS ids
            JOIN dbo.IndexDefragExclusion AS ide
                ON ids.databaseID = ide.databaseID
                AND ids.objectID = ide.objectID
                AND ids.indexID = ide.indexID;
         
        END

        SELECT @DebugMessage = 'Looping through the index list... there are ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!'
        FROM dbo.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 defragging loop
        WHILE (SELECT COUNT(*) 
               FROM dbo.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 the need to exit the loop because of the specified time limit
            IF ISNULL(@EndDatetime, GETDATE()) < GETDATE()
            BEGIN
                RAISERROR('Time limit has been exceeded!', 11, 42) WITH NOWAIT;
            END;

            IF @DebugMode = 1 RAISERROR('  Picking an index to defragment...', 0, 42) WITH NOWAIT;

            -- Get 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.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 the 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.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;

            -- Get object names
            SELECT @ObjectName  = objectName
                , @SchemaName   = schemaName
                , @IndexName    = indexName
            FROM dbo.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('  Checking 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, 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, 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, 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;

            -- IF executing the SQL
            IF @ExecuteSQL = 1
            BEGIN

                SET @DebugMessage = 'Executing: ' + @SqlCommand;
                
                -- Print the commands that are executing if specified to do so
                IF @PrintCommands = 1 OR @DebugMode = 1
                    RAISERROR(@DebugMessage, 0, 42) WITH NOWAIT;

                -- Get the time for logging purposes
                SET @DatetimeStart  = GETDATE();

                -- Log actions
                INSERT INTO dbo.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 the execution attempt in a TRY/CATCH and log any errors that occur
                BEGIN TRY

                    -- Execute the defrag!
                    EXECUTE sp_executesql @SqlCommand;
                    SET @DatetimeEnd = GETDATE();
                    
                    -- Update the log with the completion time
                    UPDATE dbo.IndexDefragLog
                    SET dateTimeEnd = @DatetimeEnd
                        , durationSeconds = DATEDIFF(second, @DatetimeStart, @DatetimeEnd)
                    WHERE indexDefrag_id = @IndexDefrag_ID;

                END TRY
                BEGIN CATCH

                    -- Update the log with the error message
                    UPDATE dbo.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 IndexDefragLog table for details.'
                            , 0, 42) WITH NOWAIT;

                END CATCH

                -- Pause
                WAITFOR DELAY @DefragDelay;

                UPDATE dbo.IndexDefragStatus
                SET defragDate = GETDATE()
                    , printStatus = 1
                WHERE databaseID       = @DatabaseID
                  AND objectID         = @ObjectID
                  AND indexID          = @IndexID
                  AND partitionNumber  = @PartitionNumber;

            END
            ELSE
            -- 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.IndexDefragStatus
                SET printStatus = 1
                WHERE databaseID       = @DatabaseID
                  AND objectID         = @ObjectID
                  AND indexID          = @IndexID
                  AND partitionNumber  = @PartitionNumber;
            END

        END

        -- Output fragmentation results?
        IF @PrintFragmentation = 1
        BEGIN

            IF @DebugMode = 1 RAISERROR('  Summary of actions...', 0, 42) WITH NOWAIT;

            SELECT databaseID
                , databaseName
                , objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , range_scan_count
            FROM dbo.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;

    -- Drop temp tables
    DROP TABLE #databaseList;
    DROP TABLE #processor;
    DROP TABLE #maxPartitionList;

    IF @DebugMode = 1 RAISERROR('DONE!', 0, 42) WITH NOWAIT;

    SET NOCOUNT OFF;
    RETURN 0;
END