Comparing sys.dm_db_index_physical_stats mode levels with Heap and Clustered Index

Create a table with some data

drop table t3
CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
GO
--BEGIN TRAN
DECLARE @idx int = (select max(isnuLL(col1,0))+1 from t3);
WHILE @idx < 1000000
BEGIN
BEGIN TRAN
    INSERT INTO t3 (col1, col2) 
    VALUES (@idx+convert(int,rand() * 100), 
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))
    SET @idx = @idx + 1
	COMMIT
END
--COMMIT;
GO

 

Check Fragmentation using sys.dm_db_index_physical_stats

 

SELECT 'Limited' as Mode,page_count, compressed_page_count as cm_page_count,index_type_desc
,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'LIMITED');

SELECT 'Sampled' as Mode,page_count, compressed_page_count as cm_page_count
,index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'SAMPLED');

SELECT 'Detailed' as Mode,page_count, compressed_page_count as cm_page_count
,index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'DETAILED');

Fragmentation output

Mode: Limited

  • Only looks at leaf level pages
  • Checks the logical fragmentation at leaf level (Out of Order pages)/ out-of-order extents in the leaf pages of a heap
  • Doesn’t look into the actual pages.

Mode: Sampled

  • Only looks at leaf level pages
  • Checks the logical fragmentation at leaf level (Out of Order pages) out-of-order extents in the leaf pages of a heap
  • Scans a sample of leaf pages (10 % approximately)  it may randomly these pages.

Mode: Detailed

  • Does what limited mode normally do but scans every page in the index including the intermediate and root pages.
  • Check the logical fragmentation at every level of Index (Out of Order pages)/ out-of-order extents in the leaf pages of a heap
  • Scans the entire index.

Detailed mode is more accurate than any other mode because it scans every page of the index but this  might create some performance bottle neck. It depends on where each of this mode can be used if an index with high fanout (index key length) which is directly proportional to the number of levels , it makes sense to check the fragmentation at intermediate levels using detailed mode at least once a week as part of Index Maint plan.

 

Terms : 

  •  Out of Order pages : An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
  • Out-of-order extent: An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

 

B-Tree Index Structure:

Heap:

Heap

Non Clustered or Clustered Index

Non Clustered or Clustered Index

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s