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.

Advertisements

17 thoughts on “Let SQL Server Tell You Which Indexes to Rebuild

  1. 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!

  2. 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’.

  3. 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

  4. 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

  5. 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.

  6. 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 :(

  7. Very elegant script, much simpler than the example in the sys.dm_db_index_physical_stats BOL Entry. Here’s a modification to take care of SQL Edition (in case you need to rebuild online).
    SELECT ‘ALTER INDEX [‘ + ix.name + ‘] ON [‘ + s.name + ‘].[‘ + t.name + ‘] ‘ +
    CASE
    WHEN ps.avg_fragmentation_in_percent > 20 THEN
    CASE
    WHEN CAST(SERVERPROPERTY(‘Edition’) AS VARCHAR) like ‘Enterprise%’ THEN ‘REBUILD WITH (ONLINE = ON, FILLFACTOR = 50) ‘
    ELSE ‘REBUILD WITH (FILLFACTOR = 50) ‘
    END
    ELSE ‘REORGANIZE ‘
    END +
    CASE
    WHEN pc.partition_count > 1 THEN ‘ PARTITION = ‘ + cast(ps.partition_number as nvarchar(max))
    ELSE ”
    END + ‘– FRAGMENTATION: ‘ + CAST(PS.AVG_FRAGMENTATION_IN_PERCENT AS VARCHAR(30))
    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, ‘LIMITED’) where page_count > 500) 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

  8. It’s good script to implement indexing required in your tables. I tried in your production server and it’s really helpful. Just wanted to add that please check the DMV – dm_db_index_physical_stats before implementing.

  9. After running the mentioned index rebuild script. If it is executed again. It shows the same index to rebuild which has already rebuilt. Am I missing something.

  10. If there are a small number of pages that make up the index, then the avg_fragmentation_in_percent can be very high even if it impact performance. Even if the index is rebuilt, the fragmentation will stay high because there are very few pages in the index. Here is an updated query to only report indexes that had a page count greater than 7.

    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, page_count
    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 and page_count > 7

  11. Pingback: SQL Server Index Fragmentation Script « Mr. Wharty's Ramblings

  12. Pingback: Jeff Wharton's Blog - SQL Server Index Fragmentation Script - Jeff Wharton

  13. Pingback: SQL Fragmentation Explained in Detail

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