Index Fragmentation Detect

1,fragmentation is divided into internal fragmentation and External fragmentation.

Internal fragmentation refers to the fragmentation within the page (index or data), where there is no space inside the page.

Internal fragmentation (often called physical fragmentation or page desnsity) means that index pages has wasted space, bo Th at the leaf and non-leaf levels. Internal fragmentation means the index is taking more space than necessary, leading to increased disk space usage, more PA GES to read to process the data, and more memory used to hold the pages in the buffer pool.

External fragmentation means that the Logical order and physical order of pages or extents are inconsistent and Logical order is defined by index key, physical ord Er is the order that page or extent actually stores in disk space. If the logically contiguous page or extent is physically contiguous, then there is no external fragmentation.

External fragmentation (often called logical fragmentation or extent fragmentation) means the pages or extents comprising The leaf level of a clustered or nonclustered index aren ' t in the most efficient order. The most efficient order are where the logical order of the pages and extents (as defined by the index keys, following the N Ext-page pointers from the page headers) was the same as the physical order of the pages and extents with the data files. In other words, the index Leaf-lelvel page and the row with the next index key is also the next physical contiguous p Age int the data file.

SQL Server provides DMF:sys.dm_db_index_physical_stats for viewing External fragmentation,external fragmentation divided into Logical and Extent Fragmentation. If the scanned table has clustered index (BTree Structure), then the fragmentation returned by DMF is the number of pages, if the scanned table does not have clustered index (Heap Structure), then the fragmentation returned by DMF is the number of extents, because the heap Pages are not sequential. In the Page header of the heap, fields Next_page and pre_page pointer are null.

Logical Fragmentation

This is the percentage of out-of-order pages in the leaf pages of an index. An Out-of-order page was a page for which the next physical page allocated to the index are not the page pointed to by the N Ext-page pointer in the current leaf page.

Extent Fragmentation

This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent was one for which the extent that contains the current page for a heap was not physically the next ex Tent After the extent that contains the previous page.

Fragmentation is due to the updating of data (insert,update and update), resulting in inconsistent physical order of data storage and the logical order defined by index key, a small amount of fragmentation is advantageous and can reduce page Split to improve update performance; A large number of fragmentation are harmful, increasing the number of IO and reducing query performance.

Fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that is made again St The table and, therefore, to the indexes defined on the table. Because These modifications is not ordinarily distributed equally among the rows of the table and indexes, the fullness O F Each page can vary over time. For queries that scan part or any of the indexes of a table, this kind of fragmentation can cause additional page reads. This hinders parallel scanning of data.

2,dmf:sys.dm_db_index_physical_stats usage

2.1 Syntax

Returns size and fragmentation information for the data and indexes of the specified table or view.

For a index, one row is returned for each level of the b-tree in each partition.

For a heap, one row are returned for the In_row_data allocation unit of each partition.

For large object (LOB) data, one row was returned for the Lob_data allocation unit of each partition.

If Row-overflow data exists in the table, one row is returned for the Row_overflow_data allocation unit in each partition.

Does not return information about xvelocity memory optimized Columnstore indexes.

Syntax

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 })

mode | NULL | DEFAULT

is the name of the mode. mode specifies the scan level, that's used to obtain statistics. mode is sysname. Valid inputs is DEFAULT, NULL, LIMITED, SAMPLED, or detailed. The default (NULL) is LIMITED.

2.2 Scanning Modes

The mode parameter indicate the verbosity of the scan mode and return information. During the scanning process, the IS table lock needs to be requested on the scanned object.

Limited Mode does not scan data pages, and for index, scans the leaf level for parent-level pages, and for heap, scans the IAM and PFS pages for object.

Detailed Mode scans all papges for the longest time and returns the most detailed information.

The mode in which the function was executed determines the level of scanning performed to obtain the statistical data is used by the function. mode is specified as LIMITED, SAMPLED, or detailed. The function traverses the page chains for the allocation units that make up the specified partitions of the table or Inde X. sys.dm_db_index_physical_stats requires only a intent-shared (IS) table lock, regardless of the mode, it runs in.

The LIMITED mode is the fastest mode and scans the smallest number of pages. For a index, only the parent-level pages of the B-tree (which is, the pages above the leaf level) are scanned. For a heap, the associated PFS and IAM pages is examined and the data pages of a heap aren ' t scanned in LIMITED mode.

With LIMITED mode, Compressed_page_count are NULL because the Database Engine only scans non-leaf pages of the B-tree and T He IAM and PFS pages of the heap.

Use SAMPLED mode to get a estimated value for Compressed_page_count, and use detailed mode to get the actual value for CO Mpressed_page_count. The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the the or heap. Results in SAMPLED mode should is regarded as approximate. If the index or heap has fewer than pages, detailed mode is used instead of SAMPLED.

The detailed mode scans all pages and returns all statistics. The modes was progressively slower from LIMITED to detailed, and because more work was performed in each mode. To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. It is the fastest and won't return a row for each nonleaf level in the In_row_data allocation unit of the index.

2.3 Critical Columns

Avg_fragmentation_in_percent

Logical fragmentation for indexes, or extent fragmentation for heaps in the In_row_data allocation unit.

Identifies the percentage of fragmentation, and the recommended value for MS is 0-10.

Fragment_count

Number of fragments in the leaf a In_row_data allocation unit.

Avg_fragment_size_in_pages

Average number of pages in one fragment in the leaf level of a In_row_data allocation unit.

Avg_page_space_used_in_percent

Average percentage of available data storage space used in all pages.

For a index, average applies to the current level of the B-tree in the In_row_data allocation unit.

For a heap, the average of any data pages in the In_row_data allocation unit.

3,detect fragmentation

SCRIPT1 used to detect fragmentations

Select Ps.database_id,ps.object_id, Ps.index_id,ps.partition_number,ps.index_type_desc, Ps.alloc_unit_type_desc, ps.index_depth, Ps.index_level, Ps.avg_fragmentation_in_percent, Ps.fragment_count, Ps.avg_fragment_size_in_pages, Ps.page_count, Ps.avg_page_ Space_used_in_percent, Ps.record_count, Ps.ghost_record_count, Ps.version_ghost_record_count, Ps.min_record_si Ze_in_bytes, Ps.max_record_size_in_bytes, Ps.avg_record_size_in_bytes, Ps.forwarded_record_count, Ps.compresse D_page_countFrom sys.dm_db_index_physical_stats (db_id (),object_id ('dbo. Factproduct'),1,1,'detailed') as PSORDER by Ps.index_level 

The fragmentation level of a index or heap is shown in the avg_fragmentation_in_percent column.

For heaps, the value represents the extent fragmentation of the heap. For indexes, the value represents the logical fragmentation of the index. Unlike DBCC Showcontig, the fragmentation calculation algorithms in both cases consider storage that spans multiple files And, therefore, are accurate.

The value for avg_fragmentation_in_percent should is as close to zero as possible for maximum performance. however, values from 0 percent through percent may be acceptable. All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can is used to reduce these value S.

Reference Documentation:

https://technet.microsoft.com/en-us/library/ms188917 (v=sql.110). aspx

Index Fragmentation Detect