SQL Server database table index defragmentation

In the SQL Server database, the index fragmentation is checked through DBCC SHOWCONTIG or DBCC SHOWCONTIG (table name), which guides us through the scheduled rebuild.

The results of the operation are as follows:

1234567891011

dbcc showcontig scanning ' Tbmodule '   table ... table: ' Tbmodule ' (1845581613); index id:0, database Id:9 A TABLE-level scan has been performed. -number of scans ..... ... ............................: -scans the extents ...........: 9------the number of the ... -expansion panel switch number ......: 8.-------------- -The average number of pages on each extents ...: 5.7 ........ -scanning density [best value: Actual value] .....: 77.78%[7:9] ".". -extents scan fragments .........: 77.78%----------- -The average number of bytes available on each page ...: 351.1 ....... -avg. page density (full) .....: 95.66% , ..........

The relevant explanations are as follows:

Page scanned-Scan pages: If you know the approximate size of a row and the number of rows in a table or index, you can estimate how many pages are in the index. Look at the number of pages scanned, if it is significantly higher than the number of pages you estimate, there is internal fragmentation.
Extents scanned-scan extents: Divide the number of scanned pages by 8, rounding to the next highest value. The value should match the number of scan extents returned by DBCC SHOWCONTIG. If DBCC SHOWCONTIG returns a high number, it indicates that there is an external fragment. The severity of the fragment depends on how much higher the value you just showed than the estimated value.
Extent switches-Expansion Switch Number: This number should be equal to the number of scan extents minus 1. High, the external fragments are indicated.
Avg. pages per extent-average pages on each extents: this is the number of scanned pages divided by the number of extents scanned, typically 8. Less than 8 indicates an external fragment.
Scan Density [best count:actual count]-scanning density [optimal value: Actual value]:DBCC Showcontig returns the most useful percentage. This is the ratio of the best and actual values of the extents. The percentage should be as close to 100% as possible. Low, it means that there are external fragments.

Logical Scan fragmentation-Logical Scan Fragmentation: Percentage of unordered pages. The percentage should be between 0% and 10%, and high will indicate an external fragment.
Extent Scan fragmentation-Extents scanning fragment: the percentage of the unordered extents in the scan index leaf level page. The percentage should be 0%, and high will indicate an external fragment.
Avg. Bytes free per page-The average number of bytes available on each page: the average number of bytes available on the scanned page. The higher is the internal fragment, but you should consider fill factor (fill factor) before you use this number to determine if there are internal fragments.
Avg. page Density (full)-average page density (complete): the inverse of the average percentage of bytes available on each page. A low percentage indicates an internal fragment.

By analyzing the result of scanning density (too low) and scanning fragmentation (too high), it is necessary to determine whether the index reconstruction is needed.

Processing: One is to use DBCC INDEXDEFRAG to organize index fragmentation, and the second is to use DBCC DBREINDEX to rebuild the index. Each has its advantages and disadvantages.

The following words are called Microsoft:
The DBCC indexdefrag command is an online operation, so the index is available only when the command is running and can be interrupted without losing the completed work. The disadvantage of this approach is that the drop/re-create operation without a clustered index is valid in terms of data re-organization.
Re-creating the clustered index will reorganize the data, with the result that the data page fills up. The fill level can be configured using the FILLFACTOR option. The disadvantage of this approach is that the index is offline in the drop/recreate cycle, and the operation is atomic level. If the index creation is interrupted, the index is not recreated. In other words, to achieve good results, you still have to rebuild the index, so you decide to rebuild the index.
DBCC dbreindex (table, index name, fill factor)
The first parameter, either a table name or a table ID.
The second parameter, if "," represents all indexes that affect the table.
The third parameter, the fill factor, is the extent to which the index page's data is populated. If it is 100, it means that every index page fills up and the select is the most efficient, but when you want to insert the index later, you have to move all the pages behind it, which is inefficient. If 0, indicates that the previous fill factor value is used.

Such as

12	--对表tbModule的所有索引进行重建，填充因子比例为80%DBCC DBREINDEX（tbModule,‘‘,80)

SQL Server database table index defragmentation