question

C0verf1re avatar image
C0verf1re asked

Query to show total fragmentation of a database.

I have plenty of queries that tell me average fragmentation for indexes and such but I have not been able to find anything that would give me a total fragmentation (basically a single number). I can see when I run a re-index that the average fragmentation has improved but I want an overall picture.

This is what I have been using and I can't find much different from this:

SELECT OBJECT_NAME(ips.OBJECT_ID)

,i.NAME ,ips.index_id

,index_type_desc

,avg_fragmentation_in_percent

,avg_page_space_used_in_percent

,page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)

AND (ips.index_id = i.index_id)

ORDER BY avg_fragmentation_in_percent DESC

I'm new to the DB Admin game so any help is appreciated.

query hints
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jon Crawford avatar image
Jon Crawford answered

Pretty sure there is more out there from Jeff Moden or Paul Randall or some other heavy hitters, but I found Brent first, so that's what you get.

https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.