Index Physical Stats Analysis (sys.dm_db_index_physical_stats)

mattthehackdev SQL

The following DMV’s (Dynamic Management Views) can be extremely useful for both analyzing existing indexes and identifying new indexes.

Overview and Usage

Is a Dynamic Management Function that returns fragmentation details of Indexes on tables in a particular database. This function takes the following parameters:

sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }, 
    { object_id | NULL | 0 | DEFAULT }, 
    { index_id | NULL | 0 | -1 | DEFAULT },  
    { partition_number | NULL | 0 | DEFAULT }, 
    { mode | NULL | DEFAULT } )
  • Database_ID: The database we want to turn physical stats for. Leave this as NULL to return stats for all DBs on a DB Server. The build in function DB_ID() can be passed instead of NULL to return info for just the current DB (If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number)
  • Object_ID: Is the object ID of the Table or View an Index is related to (If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.)
  • Index_ID: Is the Index ID (If you specify NULL for index_id, you must also specify NULL for partition_number)
  • Partition_Number: Is the partition number in the object
  • Mode: Mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED
SELECT object_name(IPS.object_id) AS [TableName], 
SI.name AS [IndexName], 
IPS.Index_type_desc, 
IPS.avg_fragmentation_in_percent, 
IPS.avg_fragment_size_in_pages, 
IPS.avg_page_space_used_in_percent, 
IPS.record_count, 
IPS.ghost_record_count,
IPS.fragment_count, 
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'RCOMDev'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0

The above query returns a Fragementation percentage as well as Fragmentation size in pages. The above function call can be modified to analyse details of indexes on a particular object like follows:

FROM sys.dm_db_index_physical_stats(db_id(N'RCOMDev'), OBJECT_ID(N'dbo.Drivers'), NULL, NULL , 'DETAILED') IPS

Where the Database is RCOMDev and the Object is the Drivers table in the dbo Namespace.

Explanation of Columns Returned

  • avg_fragmentation_in_percent – Indicates the average fragmentation of data across all pages in the Index. Ideally, this figure should be as low as possible
  • avg_page_space_used_in_percent – Returns the average density of the pages in an index. Ideally, this figure should be as close to 100% as possible.
  • fragment_count – The lower the number of fragements, the greater the indication that the data is stored consistently
  • avg_fragment_size_in_pages – Larger fragment size is better as it means less Disk I/O is involved to read the same number of pages

 

Scanning Modes Explained

  • LIMITED – Fastest mode that scans the lowest number of pages. Only scans parent level pages
  • SAMPLED – Slower than LIMITED as it also scans Leaf pages, but just scans 1% of all pages, thus values are merely approximate. Great for giving a ball park of Parent and Leaf level page statistics without running DETAILED
  • DETAILED – Scans all Parents and Leaf Pages. Essentially does a LIMITED mode scan for Parent pages and then scans the Leaf pages of all parents.

Leave a comment