In the maintenance project, we often encounter problems with index maintenance, through the statement, we can determine whether the index of a table needs to be rebuilt.
Execute the statement: Parse the index of the table first
Index establishment of the analysis table: DBCC showcontig (' table ')
DBCC showcontig is scanning ' table ' tables ... Table: ' Table ' (53575229); index id:1, database id:14 has performed a Table-level scan. -Scan pages .....: 228-Number of scanned areas .....: 52-zone switching times...............---------------# # # # # # # .....................: 225-average number of pages per zone ....: 4.4-Scan density [best count: Actual Count] ...: 12.83% [29:226]-(in)--------- Logical scan fragment ...........: 97.37%-zone Scan fragment ....: 98.08%-The average Available bytes per page ...: 2686.3-Avg.-Page density (PP))... £ º (average) of the pages in the same area ..... (full) .....: 66.81%.... £ º (a)
When you find that the scan density line, the ratio of the best count to the actual count has been severely misaligned, the logical scan fragments accounted for a very large percentage of the average available bytes per page is very large, indicating
Your index needs to be re-organized.
Perform the Rebuild Index command: Post analysis after DBCC dbreindex (' Table ')
DBCC showcontig is scanning ' table ' tables ... Table: ' Table ' (53575229); index id:1, database id:14 has performed a Table-level scan. -Scan pages .....: 154-Number of scanned areas .....: 20-zone switching times...............---------------# # # # # # # .....................: 19-average number of pages per zone ....: 7.7-Scan Density [best count: Actual Count] ...: 100% [20:20]-logos, and so on ......----- Scan fragments ............: 0%-zone Scan fragment ....: 55%-The average Available bytes per page ...: 86.8-Average page density (full). \ \ \ \ \ \ \ \ \ \ \ (+/-) '. * * * * * *. ..........: 98.93%.
SQL Server rebuilds indexes before and after comparison