Let SQL Server Tell You Which Indexes to Rebuild

When index fragmentation becomes too high, indexes will be very inefficient. Other than planning a good index design, you should rebuild / reorganize your indexes every once in a while.

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +
       CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END
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, NULL)) 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

The above query will give you a list of recommended index rebuild / reorganize statements for your database, according Pinal Dave’s 10-40 rule, although you are welcome to tweak it to your liking. It supports non-partitioned as well as partitioned indexes. If you want a more intense check for fragmentation, change the last NULL in the dm_db_index_physical_stats call to 'SAMPLED' or even 'DETAILED' (include quotes).

It’s a handy little tool for database administrators and saves a lot of the hassle of monitoring index fragmentation.

Update: Added multi-schema support as suggested by MJ12 and another check for null index names.

6 Comments

  • Great script, can't wait to test it out at work in the morning. Since we don't really have a DBA, this type of script will be very useful to us.

    Thanks a mil!

  • When I try to run it against SQL 2008 Developer I get these errors:

    Msg 102, Level 15, State 1, Line 7
    Incorrect syntax near '('.
    Msg 102, Level 15, State 1, Line 11
    Incorrect syntax near 'pc'.

  • This works but I am not sure if there is a nicer way to do it:

    DECLARE @db_id int;
    SET @db_id = DB_ID();
    SELECT 'ALTER INDEX [' + ix.name + '] ON [' + t.name + '] ' +
    CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +
    CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END
    FROM sys.indexes AS ix INNER JOIN sys.tables t
    ON t.object_id = ix.object_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, NULL)) 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

  • Modification to pull in the schema...

    SELECT
    'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
    CASE
    WHEN ps.avg_fragmentation_in_percent > 40
    THEN 'REBUILD WITH (ONLINE = ON)'
    ELSE
    'REORGANIZE'
    END +
    CASE
    WHEN pc.partition_count > 1
    THEN ' PARTITION = ' + CAST(ps.partition_number AS NVARCHAR(MAX))
    ELSE
    ''
    END
    FROM
    sys.indexes AS ix
    INNER JOIN sys.tables t
    ON t.object_id = ix.object_id
    INNER JOIN sys.schemas s
    ON s.schema_id = t.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, NULL)
    )
    AS 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
    )
    AS pc
    ON
    t.object_id = pc.object_id
    AND ix.index_id = pc.index_id
    WHERE
    ps.avg_fragmentation_in_percent > 10

  • Chris,
    I'm not really sure what went wrong when you tried to run it. I'm using SQL Server 2008 Developer myself.

    MJ12,
    I never worked with more than one schema. Thanks for the update.

  • Omer,
    Neither have I. I stumbled upon this "gotcha" when I attempted to run the script on the AdventureWorks database. Feel free to "clean up" the code, it's scattered all over the place :(

Comments have been disabled for this content.