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.
-David.
About the Author
David Maxwell
UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.
Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions. David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.
Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.
In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.
Learn more about David Maxwell at https://upsearch.com/david-maxwell/.
About UpSearch
UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.