SQL Server Database Maintenance

First, debris view maintenance

DBCC showcontig (' table name ')

DBCC showcontig ('t_nofitstudy')

The results are as follows:

DBCCShowcontig is scanning.'T_nofitstudy'table ... Table:'T_nofitstudy'(2078630448); Index ID:1, Database ID:7has been executedTABLElevel of scanning. -The number of pages scanned ...................:1-Number of scan zones ...................:1-The number of zone switches ..................:0-The average number of pages in each district ............:1.0-Scan Density[Best Count: Actual count].......:100.00% [1:1]-Logical scan fragment ...........:0.00%-Area Scan fragment ...........:0.00%-The average number of bytes available per page ..............:8094.0-Avg. page density (full) ............:0.00%DBCCExecution is complete. IfDBCCThe error message is output, please contact your system administrator.

Metrics for Fragmentation processing:
① If the logical scan fragment exceeds 20% , the database will be processed
② Scan Density [best count: Actual count] If smaller than 80% , the database will be fragmented
③  area scan fragments more than 20%


Solutions for fragmentation

Basically all of the solutions are based on the reconstruction and collation of the index, but in a different way

1. Delete the index and rebuild

This is not a good way. The index is not available during the drop index. Causes blocking to occur. In the case of dropping a clustered index, it causes the corresponding nonclustered index to be rebuilt two times (when the deletion is rebuilt, and then rebuilt at build time). Although this method is not good, it is most effective for index collation.

2. Rebuilding an index using the DROP_EXISTING statement

To avoid rebuilding two indexes, use the DROP_EXISTING statement to rebuild the index, because the statement is atomic and does not cause nonclustered indexes to be rebuilt two times, but the same way it can cause blocking

3. Rebuilding an index using the ALTER INDEX REBUILD statement

Using this statement also rebuilds the index, but it does not need to unload and rebuild the index by dynamically rebuilding the index. is superior to the first two methods, but will still cause blocking. You can reduce the lock by using the online keyword, but it will cause the rebuild time to be extended.

4. Using the Alter INDEX REORGANIZE

This method does not rebuild the index, nor does it generate a new page, it simply organizes and skips when a locked page is encountered, so it does not cause blocking. But at the same time, the finishing effect will be worse than the first three kinds.

SQL Server Database Maintenance