Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Declare @DatabaseId Int = DB_ID('ODS')
- SELECT
- OBJECT_NAME(T.OBJECT_ID) as TableName,
- T2.Name as IndexName,
- T.index_id as IndexId,
- index_type_desc as IndexType,
- index_level as IndexLevel,
- avg_fragmentation_in_percent as AverageFragmentationPercent,
- avg_page_space_used_in_percent as AveragePageSpaceUsedPercent,
- page_count as PageCount
- FROM sys.dm_db_index_physical_stats (@DatabaseId, NULL, NULL, NULL, 'DETAILED') T
- INNER JOIN [sys].[indexes] T2 ON T.index_id = T2.index_id And T.object_id = T2.object_id
- ORDER BY avg_fragmentation_in_percent DESC
- Declare @DatabaseId Int = DB_ID('ODS')
- SELECT
- OBJECT_NAME(T.OBJECT_ID) as TableName,
- T2.Name as IndexName,
- T.index_id as IndexId,
- index_type_desc as IndexType,
- index_level as IndexLevel,
- avg_fragmentation_in_percent as AverageFragmentationPercent,
- avg_page_space_used_in_percent as AveragePageSpaceUsedPercent,
- page_count as PageCount
- FROM sys.dm_db_index_physical_stats (@DatabaseId, NULL, NULL, NULL, 'DETAILED') T
- INNER JOIN [sys].[indexes] T2 ON T.index_id = T2.index_id And T.object_id = T2.object_id
- where page_count >1500--this would filter out irrelevant index frag.
- ORDER BY avg_fragmentation_in_percent DESC
Add Comment
Please, Sign In to add comment