Originally published on DMMaxwell.WordPress.com.
The scenario here is, you’ve just performed some massively fragmenting operation, like a shrink. (Don’t look at me like that – it happens, just don’t do it as regular maintenance ) You know you need to get in and do some index maintenance. You could run your nightly index maintenance job, but in this case, you want to do this now, instead of waiting for off hours or running that job in the middle of the day. Furthermore, you want to see what indexes need maintenance and maybe run some of the commands independently. Here’s a quick script to make that easy for you. It outputs the table and index name, page count, and percent fragmentation, as well as a one line command to rebuild or defrag the index, depending on what thresholds you want to use for that.
/* Quick script to create index defrag / rebuild commands. Useful for when you KNOW you have the work to do. Not so useful for ongoing index maintenance, and should not be used for such. The usual cautions about running this in production, fitness for a particular purpose, or guarantee that this will fix anything apply. DMMaxwell, October 2012. */ SELECT TableName = SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ips.object_id) ,IndexName = ix.name ,Pages = ips.page_count ,Fragmentation = ips.avg_fragmentation_in_percent ,ReindexCmd = 'ALTER INDEX [' + ix.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME(ips.object_id) + '] ' + CASE WHEN ips.avg_fragmentation_in_percent > 15 THEN 'REBUILD; ' /* BOL recommendation is 30 - being more aggressive. */ WHEN ips.avg_fragmentation_in_percent > 5 THEN 'REORGANIZE; ' /* BOL recommendation I actually like. */ END FROM sys.dm_db_index_physical_stats( DB_ID('AdventureWorks'), /* Your database name here. */ NULL, /* Specify object ID or NULL for all of them.*/ NULL, /* Specify index ID or NULL for all of them.*/ NULL, /* Specify partition ID or NULL for all of them.*/ 'LIMITED' /* Going for speed, rather than depth of info. */ ) ips INNER JOIN sys.tables t ON ips.object_id = t.object_id INNER JOIN sys.indexes ix ON ips.object_id = ix.object_id AND ips.index_id = ix.index_id WHERE ips.page_count > 500 /* Usually 1000 */ AND ips.avg_fragmentation_in_percent > 5 /* Ignore fragmentation less than 5%. */ /* These numbers vary, based on a multitude of factors. I'm being a bit aggressive today. */ AND ips.index_type_desc != 'HEAP' /* Can't defragment a heap... */ GO
Thanks for reading.