Defragment Indices

As records are added to or removed from a table, indices on the table will become fragmented, thus reducing their efficiency both in terms of disk usage and performance. This fragmentation is especially severe if a database is shrunk (one reason why a database should NOT be shrunk, barring exceptional circumstances). SQL Server ships with maintenance tasks that can be configured to defragment indices. However, there are situations when running a maintenance task is not practical, or a greater degree of customization is desired beyond what is configurable within the maintenance task. In such circumstances, a stored procedure can be used to manage the defragmentation.

The example procedure below is created within a maintenance schema named “mx”. Furthermore, it uses the system stored procedure sp_MSforeachdb to span all databases on a server when looking for indices to defragment; it only looks for user tables, even though it includes the system databases; it specifies a fragmentation threshold to qualify a table for defragmentation; and it uses the degree of fragmentation to decide whether to REORGANIZE or REBUILD the index.

-- Create a stored procedure to defragment indices
CREATE PROCEDURE [mx].[uspDefragmentIndices]
AS
BEGIN
    -- Declare variables
    DECLARE
        @DBName NVARCHAR(255)
        , @TableName NVARCHAR(255)
        , @SchemaName NVARCHAR(255)
        , @IndexName NVARCHAR(255)
        , @PctFrag DECIMAL
        , @Defrag NVARCHAR(MAX)
    ;
    -- If the temporary table already exists, drop it
    IF OBJECT_ID('tempdb..#FragTables') IS NOT NULL
        DROP TABLE #FragTables
    ;
    -- Create the temporary table
    CREATE TABLE #FragTables (
        DBName NVARCHAR(255)
        , TableName NVARCHAR(255)
        , SchemaName NVARCHAR(255)
        , IndexName NVARCHAR(255)
        , AvgFragment TINYINT)
    ;
    -- Insert fragmented indices into the temporary table
    EXECUTE sp_MSforeachdb
        'INSERT INTO #FragTables (
            DBName
            , TableName
            , SchemaName
            , IndexName
            , AvgFragment)
        SELECT
            ''?'' AS DBName
            , t.Name AS TableName
            , sc.Name AS SchemaName
            , i.name AS IndexName
            , s.avg_fragmentation_in_percent
        FROM
            ?.sys.dm_db_index_physical_stats(
                DB_ID(''?'')
                , NULL
                , NULL
                , NULL
                , ''Sampled'') AS s
            INNER JOIN ?.sys.indexes AS i
                ON s.Object_Id = i.Object_id
                AND s.Index_id = i.Index_id
            INNER JOIN ?.sys.tables AS t
                ON i.Object_id = t.Object_Id
            INNER JOIN ?.sys.schemas AS sc
                ON t.schema_id = sc.SCHEMA_ID
        WHERE
            s.avg_fragmentation_in_percent > 20
            AND t.TYPE = ''U''
        ;'
    ;
    -- Declare a cursor to scroll through the fragmented indices
    DECLARE cIndices CURSOR
    FOR
    SELECT
        DBName
        , TableName
        , SchemaName
        , IndexName
        , AvgFragment
    FROM #FragTables
    ;
    -- Open the cursor
    OPEN cIndices;
    -- Populate the cursor
    FETCH NEXT FROM cIndices
    INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag
    ;
    -- Scroll through the fragmented indices
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Begin building the TSQL statement to defragment the index
        SET @Defrag = N'ALTER INDEX [' + @IndexName
            + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] '
        ;
        -- Set the defrag type based on the degree of fragmentation
        IF @PctFrag <= 40.0
            SET @Defrag += 'REORGANIZE';
        ELSE IF @PctFrag > 40.0
            SET @Defrag += 'REBUILD';
        -- Display the TSQL statement that was built
        PRINT @Defrag;
        -- Execute the TSQL statement
        EXECUTE sp_executesql @Defrag;
        -- Populate the next record into the cursor
        FETCH NEXT FROM cIndices
        INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag
        ;
    END
    -- Close the cursor
    CLOSE cIndices;
    -- Release the cursor
    DEALLOCATE cIndices;
    -- Drop the temporary table
    IF OBJECT_ID('tempdb..#FragTables') IS NOT NULL
        DROP TABLE #FragTables
    ;
END

To execute this stored procedure, simply run the following TSQL command. Note that it does not matter from which database the stored procedure is run, as the procedure itself references all databases on the server. This can be modified, of course, by removing the code that captures the indices to be defragmented from within the sp_MSforeachdb stored procedure.

-- Defragment the indices
EXECUTE DB.mx.uspDefragmentIndices;

As this procedure runs, it will display a list of TSQL statements that were executed, giving you an overview of the state of indexes on the server prior to defragmentation. It is often worth saving this list along with other maintenance and change logs.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

  • lextonr  On 16 Aug 2012 at 1949

    Thanks to a sharp-eyed reader, I’ve added brackets around the index name, database name, schema name, and table name to prevent errors related to objects names using reserved keywords.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: