Get fragmentation info for the tables

 

Analyzing Index Fragmentation in SQL Server

In SQL Server, understanding how your indexes and data are physically stored can help improve performance. The dm_db_index_physical_stats dynamic management function is a powerful tool that provides detailed information about index fragmentation and storage usage.

This function returns statistics about the size and fragmentation of indexes and data within a specified table or view. For each index, it outputs a row for every level of the B-tree structure in each partition.

Sample Query to Retrieve Fragmentation Details

The following SQL query provides fragmentation data for all user-defined tables and their indexes at the leaf level (excluding heaps). It uses the 'LIMITED' mode to reduce CPU usage, which is sufficient for general maintenance purposes.

-- Get fragmentation info for the tables
SELECT 
  ss.[Name] AS [Schema], 
  OBJECT_NAME(ddips.OBJECT_ID) AS [Table_name], 
  ISNULL(si.[Name], '') AS [Index_name],
  si.Index_id,
  si.[Type_desc],
  ISNULL(ddips.avg_fragmentation_in_percent, 0) AS [Ext_fragmentation], 
  ddips.page_count AS [Pages],
  si.Fill_factor,
  ISNULL(ddips.avg_page_space_used_in_percent, 0) AS [Page_fullness_pct]
FROM [sys].dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ddips
  JOIN [sys].indexes si 
    ON ddips.index_id = si.index_id 
    AND ddips.OBJECT_ID = si.OBJECT_ID
  JOIN [sys].tables st 
    ON ddips.OBJECT_ID = st.OBJECT_ID
  JOIN [sys].schemas ss 
    ON st.SCHEMA_ID = ss.SCHEMA_ID
WHERE ddips.index_level = 0 
  AND si.index_id > 0 
  AND st.[Type] = N'U' -- Only user-defined tables
GROUP BY 
  ss.[Name], ddips.OBJECT_ID, si.[Name], si.index_id, 
  si.type_desc, avg_fragmentation_in_percent, ddips.page_count, 
  avg_page_space_used_in_percent, si.fill_factor
ORDER BY ddips.page_count DESC;

This query helps database administrators (DBAs) identify indexes that may benefit from maintenance actions such as reorganizing or rebuilding based on fragmentation and page usage.

Further Reading

For more details on the sys.dm_db_index_physical_stats function and its parameters, visit the official Microsoft documentation:

Microsoft Docs - sys.dm_db_index_physical_stats