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');
—
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:
Non Clustered or Clustered Index
Sqljunkieshare 