SQL Server Index Fragmentation Script

Just sharing a cool script I stumbled across while searching for SQL Server index fragmentation scripts (Omer van Kloeten’s Blog). I’ve cleaned it up a bit and turned it into a Stored Procedure so that I can execute it as a SQL Server Agent Job (Not sure if this is a good idea or not so feel free to leave comments).

So why do we need to worry about Index Fragmentation?

*The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. For more information, see this Microsoft Web site.

Please Note: Defragmentation does not yield performance gains in every case. Every scenario is different. Similarly, determining when you should run the defragmentation statements requires analysis.

*Reorganizing and Rebuilding Indexes (http://technet.microsoft.com/en-us/library/ms189858.aspx)

MSDN Gallery: http://code.msdn.microsoft.com/SQL-Server-Index-6c12e7d4

CREATE PROC usp_FixIndexFragmentation
(
    /* Use online rebuild if detected  edition is Enterprise, Developer or Evaluation.*/
    @Online BIT = 1,

    /* Log recommendations Only */    
    @LogOnly BIT = 1,

    /* Specifies a percentage that indicates how full the Database Engine should 
    make the leaf level of each index page during index creation or alteration. 
    Fillfactor must be an integer value from 1 to 100. The default is 0. */
    @FillFactor TINYINT = 50,

   /* Specifies the scan level that is used to obtain statistics. Valid inputs are 
    DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. */
    @Index_physical_stats_mode VARCHAR(20) = 'Limited',


    /* Total number of index or data pages. */
    @Index_physical_stats_page_count BIGINT = 500,


    /* Logical fragmentation for indexes, or extent fragmentation for heaps in the 
    IN_ROW_DATA allocation unit. The value is measured as a percentage and 
    takes into account multiple files */
    @Avg_fragmentation_percent TINYINT = 40
)
AS
BEGIN
    SET NOCOUNT ON

    DEClARE @Rows BIGINT = 0
    DEClARE @Counter BIGINT = 1
    DECLARE @TSQLStatement NVARCHAR(MAX)

    IF OBJECT_ID('tempdb..#TmpTable') IS NOT NULL 
        DROP TABLE #TmpTable 
          
    CREATE TABLE #TmpTable 
    (
        RowNo BIGINT IDENTITY(1,1), TSQLStatement VARCHAR(MAX)
    )
          
    IF NOT EXISTS 
    (
        SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogTable]') 
        AND type in (N'U')
    )
    BEGIN
        CREATE TABLE [dbo].[LogTable]
        (
            [TSQLStatement] [varchar](max) NULL,
            [FRAGMENTATION] [float] NULL,
            [Executed] [datetime] NULL,
            [ExecutedBy] [varchar](100) NULL
        ) ON [PRIMARY]
    END
                    
    INSERT INTO LogTable
    OUTPUT INSERTED.TSQLStatement INTO #TmpTable
    SELECT    'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
    CASE
        WHEN ps.avg_fragmentation_in_percent > @Avg_fragmentation_percent THEN
        CASE
            WHEN @Online = 1 AND 
                (
                    CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Enterprise%'
                    OR
                    CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Developer%'

                    OR
                    CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Evaluation%'
                )
                THEN 'REBUILD WITH (ONLINE = ON, FILLFACTOR = ' 
                    + CAST(@FillFactor AS VARCHAR(10)) + ') '
            ELSE 'REBUILD WITH (FILLFACTOR = ' 
                + CAST(@FillFactor AS VARCHAR(10)) + ') '
        END
        ELSE 'REORGANIZE '
    END +
    CASE
        WHEN pc.partition_count > 1 
            THEN ' PARTITION = ' + CAST(ps.partition_number AS NVARCHAR(MAX))
        ELSE ''
    END,
    PS.AVG_FRAGMENTATION_IN_PERCENT, GETDATE(), SUSER_NAME()
    FROM sys.indexes AS ix INNER JOIN sys.tables t
        ON t.object_id = ix.object_id     INNER JOIN sys.schemas s
        ON t.schema_id = s.schema_id INNER JOIN 
        (         
            SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
            FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 
                @Index_physical_stats_mode) 
            WHERE page_count > @Index_physical_stats_page_count
        ) ps
            ON t.object_id = ps.object_id AND ix.index_id = ps.index_id INNER JOIN 
    (
        SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
        FROM sys.partitions
        GROUP BY  object_id, index_id
    ) pc
            ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
    WHERE ps.avg_fragmentation_in_percent > 10 AND ix.name IS NOT NULL
    ORDER BY  ps.avg_fragmentation_in_percent DESC

    SET @Rows = @@ROWCOUNT

    IF @LogOnly = 0
    BEGIN
        WHILE (@Counter <= @Rows)
            BEGIN
            SELECT @TSQLStatement = TSQLStatement
            FROM #TmpTable
            WHERE RowNo = @Counter

            EXECUTE sp_executesql @TSQLStatement

            SET @Counter += 1
            END
    END
    
  SET NOCOUNT OFF
END