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.