A query for the level of fragmentation of indexes in the current database, with usage since last restart of SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT ps.index_id AS [Index ID], OBJECT_NAME(b.OBJECT_ID) AS [Table Name], b.name AS [Index Name], ps.avg_fragmentation_in_percent AS [Fragmentation (%)], ps.avg_page_space_used_in_percent AS [Page Space (%)], st.USER_SEEKS AS [User Seeks], st.USER_SCANS AS [User Scans], st.USER_LOOKUPS AS [User Lookups], st.USER_UPDATES AS [User Updates], ps.page_count AS [Page Count] FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id INNER JOIN sys.databases d ON ps.database_id = d.database_id INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS st ON ps.OBJECT_ID = st.OBJECT_ID AND ps.index_id = st.index_id AND ps.database_id = st.database_id WHERE ps.database_id = DB_ID() and ps.index_id >0 --and b.Name = 'PK_FilterInformationGroup' ORDER BY ps.avg_fragmentation_in_percent DESC GO |
I can’t take credit for this code, but it has proven useful, so I’m posting it here.