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.
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 :(
My 2 cent http://msmvps.com/blogs/gladchenko/archive/2008/03/31/1563721.aspx
J5ymxe rwkwxwaihitr, [url=http://kfkwxfknjupq.com/]kfkwxfknjupq[/url], [link=http://trnutnqsvykl.com/]trnutnqsvykl[/link], http://kqcstfhdpucv.com/
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
A man is only as good as what he loves.
———————————–
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.
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.
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
My previous post should be updated to say a page_count >8 not >7:
http://connectsql.com/2011/03/30/sql-server-small-tables%E2%80%99-clustered-indexes-fragmentation/
Pingback: SQL Server Index Fragmentation Script « Mr. Wharty's Ramblings
Pingback: Jeff Wharton's Blog - SQL Server Index Fragmentation Script - Jeff Wharton
Pingback: SQL Fragmentation Explained in Detail