How to Find the Amount of Fragmentation on Your SQL Server Instance

Need to find the amount of fragmentation in all your indexes in all databases for a given instance of SQL Server 2005 or later? You can use the undocumented/unsupported sp_MSforeachdb to loop through all the databases. For more information on fragmentation see my previous blog http://blogs.msdn.com/cindygross/archive/2009/11/20/sql-server-and-fragmentation.aspx.

 

— Cindy Gross 2009

— find fragmentation on all indexes in all databases on this instances

— to find fragmentation on just one db comment out the exec master… line and the last quote

— you must use the db_id() rather than NULL as the first parameter of the DMV or it will try to do a join across all dbs

SELECT @@SERVERNAME, @@VERSION, GETDATE() as BatchStartTime

exec master.sys.sp_MSforeachdb ‘ USE [?];

 

DECLARE @starttime datetime, @endtime datetime

SELECT @starttime = GETDATE()

SELECT db_name() as CurrentDB, @starttime as DBStartTime

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id, DB_ID()) as ObjectName,

a.index_id, b.name as IndexName,

avg_fragmentation_in_percent, page_count, index_depth, index_type_desc, alloc_unit_type_desc

— , record_count, avg_page_space_used_in_percent –(null in limited)

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

–WHERE index_id > 0 — exclude heaps

ORDER BY DatabaseName asc, avg_fragmentation_in_percent desc, ObjectName asc, IndexName asc

SELECT @endtime = GETDATE()

SELECT @starttime as StartTime, @endtime as EndTime, DATEDIFF(MINUTE,@starttime,@endtime) as TotalMinutes

Published by Cindy Gross | She-Her | Befriending Dragons

I am a Generative Coach, a Race+Gender Equity Adaptive Leader, an ex-techie, & the founder of Befriending Dragons. I lead & coach with empathy & an intersectional social equity perspective so you can unleash the change & impact you want to have in the world. I am a chaos dragon, injecting just enough disruption around me to open new perspectives and new harmonies. I seek wise justice.

Leave a Reply

%d bloggers like this: