Sqlserver: Query of index fragments

Index fragmentation: internal fragmentation (or leaf filling rate): reflects the space usage or idle rate of data leaf level external fragmentation: since sqlserver uses eight consecutive pages as a database block (Zone) extent as the read unit, the physical storage and logical partitions are inconsistent (discontinuous) this results in io read switching zone fragmentation: This is a heap.

Index fragmentation: internal fragmentation (or leaf filling rate): reflects the space usage or idle rate of data leaf level external fragmentation: since sqlserver uses eight consecutive pages as a database block (Zone) extent as the read unit, the physical storage and logical partitions are inconsistent (discontinuous) this results in io read switching zone fragmentation: This is a heap.

IndexFragment:

• InternalFragment(Or leaf filling rate): reflects the space usage or idle rate of the Data leaf.
• ExternalFragment: Since sqlserver uses eight consecutive pages as a database block (Zone) extent as the read unit, the physical storage partition and logical partition are inconsistent (discontinuous) io read Switching

• ZoneFragment: This is the percentage of error zones on the heap leaf page. Error zone refers to the zone where the current page containing the heap is not physically the next zone after the previous page. (Microsoft does not really explain the concept :(

QueryFragmentSituation:

1. Dbcc showcontig: four part object names ,【IndexName | 【IndexId]
2. Dbcc showcontig: id of the current library object ,【IndexName | 【IndexId]
3. Sys. dm_db_index_physical_stats: Database id, Object id,IndexId, partition id, scan mode

• • • Five parameters, basically, [0 (Special, index can be 0, so this is-1)] | [null] | [default] The meaning is the same

Basic metrics:

1. Scan density (%) [optimal count: actual count]: This is the ratio of "optimal count" to "actual count. If all content is continuous, the value is 100. If the value is less than 100, someFragment. "Optimal count" refers to the ideal number of zone changes when everything is continuously linked. "Actual count" refers to the actual number of changes in the partition.
2. Area ScanFragment(%): ScanIndexThe percentage of error zones on the leaf level page. This number is irrelevant to the heap. For error zones, including the currentIndexThe page area does not physically contain the previousIndexPage. NOTE: IfIndexThis number is meaningless if it spans multiple files.
3. Avg_page_space_used_in_percent: Average page space usage. Related Concepts: Page splitting and page filling rate
4. Avg_fragment_size_in_pages: the average number of pages has oneFragmentThe greater the value, the better.
5. Avg_fragmentation_in_percent:FragmentRate, not explained. The smaller the value, the better. It is inversely proportional to avg_fragment_size_in_pages!
6. Page_count: Total number of pages scanned
7. Record_count: Total number of scanned records. Note: It is the number of records relative to the current scan, not necessarily a row of data in your user table.
8. Forwarded_record_count: number of records split by PAGE

Scan Method

　　IndexHeap, because its essence is the B number structure, the B number is hierarchical, so you can choose a variety of scanning: Non-page level? Or is it just a generation of samples? Or full scan?

The function Execution Mode determines the scanning level to obtain the statistical data used by the function. Mode is specified as LIMITED, SAMPLED, or DETAILED. This function traverses the page chains of allocation units, which constitute a table orIndex. Sys. dm_db_index_physical_stats only requires an intention to share (IS) Table lock and ignores the mode in which it runs. For more information about locking, see lock mode.


The LIMITED mode runs the fastest, and the number of scanned pages is the least. ForIndexOnly scan the parent page of Tree B (that is, the page above the leaf level ). For the heap, only the associated PFS and IAM pages are checked, and the heap data pages are not scanned. In SQL Server 2005, all heap pages are scanned in LIMITED mode.


In LIMITED mode, compressed_page_count is NULL because the database engine can only Scan non-leaf pages and heap IAM and PFS pages of Tree B. You can use the SAMPLED mode to obtain the estimated value of compressed_page_count. You can use the DETAILED mode to obtain the actual value of compressed_page_count. SAMPLED mode will return based onIndexOr the statistical information of 1% samples on all pages in the heap. IfIndexOr if the heap is less than 10,000 pages, use DETAILED mode instead of SAMPLED.


The DETAILED mode scans all pages and returns all statistics.


From the LIMITED mode to the DETAILED mode, the speed will be slower, because more and more tasks are executed in each mode. To quickly measure the table orIndexOrFragmentLevel. Use the LIMITED mode. It is the fastest andIndexIn the IN_ROW_DATA allocation unit, no corresponding row is returned for each non-leaf level.

Best practices
Always ensure that a valid ID is returned when DB_ID or OBJECT_ID is used. For example, when using OBJECT_ID, specify the names of three parts, such as OBJECT_ID (n' AdventureWorks2008R2. person. address '), or in sys. the dm_db_index_physical_stats function tests these values before using the values returned by the function. The following example A and B demonstrate A security method for specifying the database and Object ID.


DetectionFragment
TheIndexDuring data modification (INSERT, UPDATE, and DELETE statements ),Fragment. Because these modifications are not used in tables andIndexSo the fill degree of each page changes with time. For part or all of the scanned tablesIndexOfQueryFragmentThis will cause additional page reading. This delay Data Parallel scanning.


In SQL Server 2008FragmentThe computing algorithm is more accurate than SQL Server 2000. Therefore,FragmentThe value is higher. For example, in SQL Server 2000, if the 11th and 13th pages of a table are in the same partition and the 12th page is not in this partition, the table is not considered to containFragment. However, two physical I/O operations are required to access these pages. Therefore, in SQL Server 2008Fragment.


IndexOr heapFragmentThe level is displayed in the avg_fragmentation_in_percent column. This value indicates the heap zone.Fragment. ForIndex, This value indicatesIndexLogicFragment. Different from dbcc showcontigFragmentComputing algorithms all consider storage across multiple files, so the results are accurate.


LogicFragment



ZoneFragment
This is the percentage of error zones on the heap leaf page. Error zone refers to the zone where the current page containing the heap is not physically the next zone after the previous page.


For optimal performance, avg_fragmentation_in_percent should be as close as possible to zero. However, values from 0 to 10% are acceptable. All reductionsFragmentMethods (such as re-generation, re-organization, or re-creation) can be used to reduce these values. How to analyzeIndexMediumFragmentFor details about the degree, see reorganizing and regenerateIndex.


DecreaseIndexInFragment
WhenIndexSegmentationFragmentImpactQueryThere are three ways to reduce performanceFragment:


1. Delete and recreate the ClusterIndex.
Recreate a clusterIndexData is re-distributed to fill up the data page. Fill level can be configured using the FILLFACTOR option in create index. The disadvantage of this method is thatIndexThe operation is offline and atomic during the deletion and re-creation period. If interruptedIndexCannot be created againIndex. For more information, see create index (Transact-SQL ).


2. Use alter index reorganize (instead of dbcc indexdefrag) to reorder by logicIndex. Because this is an online operation, you can still useIndex. When this operation is interrupted, completed tasks are not lost. The disadvantage of this method is that it is not as good as reorganizing data.IndexThe regenerate operation is effective without updating the statistics.




3. Use alter index rebuild (instead of dbcc dbreindex) to generate online or offlineIndex. For more information, see alter index (Transact-SQL ).




Not becauseFragmentOrganization or re-generationIndex.FragmentInIndexThe pre-read throughput of the page is reduced during the scanning process. This leads to a longer response time. IfFragmentOrIndexInQueryThe workload does not involve scanning (because the workload is mainly searched separately), it is deletedFragmentIt may not work. For more information, see this Microsoft website.
Note:
IfIndexRunning dbcc shrinkfile or dbcc shrinkdatabase may generateFragment. Therefore, if you must perform the contraction operation, you should not deleteFragment.





Reduce heapFragment
To reduce the heap ZoneFragment, Create a cluster for the tableIndexAnd then deleteIndex. Create a clusterIndexData will be re-distributed. At the same time, we will consider the distribution of available space in the database to optimize it as much as possible. When a cluster is deletedIndexWhen the heap is re-created, the data is not moved and the best position is maintained. For information about how to perform these operations, see create index and drop index.


Compressing large object data
By default, the alter index reorganize statement compresses pages that contain large objects (LOB) data. Because empty LOB pages are not released, compressing this data can improve disk space usage when deleting a large number of LOB data or LOB columns.


Reorganizes the specified clusterIndexCompress and aggregateIndexContains all the LOB columns. Reorganizing non-ClusteringIndexCompressIndexAll LOB columns of Non-key (included) columns. If ALL is specified in the statement, ALLIndexRe-organize. In addition, the compression and aggregationIndex, Basic table or non-clustering with columnsIndexAll associated LOB columns.


Evaluate disk space usage
The avg_page_space_used_in_percent column indicates the page filling degree. To optimize disk usageIndex, The value should be close to 100%. However, for those with many random inserts and full pagesIndex, The page splitting score will continue to increase. This will lead to moreFragment. Therefore, to reduce page splitting, This value must be less than 100%. Use the specified FILLFACTOR option to regenerateIndexYou can change the page filling level to conformIndexInQueryMode. For more information about fill factor, see fill factor. In addition, alter index reorganize tries to compress by filling the page to the last specified FILLFACTOR.Index. This increases the value of avg_space_used_in_percent. Note that alter index reorganize does not reduce page filling. Instead, you must executeIndexRegenerate.


EvaluationIndexFragment
FragmentConsists of physical continuous leaf-level pages in the same file in the allocation unit. OneIndexAt least oneFragment.IndexMaximum value that can be includedFragmentEqualIndexPage-level pages.FragmentThe larger the disk size, the less disk I/O required to read the same page number. Therefore, the larger the value of avg_fragment_size_in_pages, the better the range scanning performance. Avg_fragment_size_in_pages is inversely proportional to avg_fragmentation_in_percent. Therefore, re-generate or re-organizeIndexWill decreaseFragmentNumber, but increase at the same timeFragmentSize.