碎片整理（Defragmentation）.docVIP

碎片整理（Defragmentation） Solving fragmentation problems Once you have a problem with the table or index, then you have 4 choices to solve those problems: 1. delete and rebuild indexes 2. use the DROP_EXISTING clause to rebuild the index 3. execute DBCC DBREINDEX 4. execute DBCC INDEXDEFRAG Although each technique can achieve the ultimate goal of sorting your index fragments, each has its own advantages and disadvantages. Deleting and rebuilding indexes Using DROP INDEX and CREATE INDEX or ALTER TABLE to delete and rebuild indexes has some drawbacks, including the index disappearing during the deletion of the rebuild. In index deletion reconstruction, query performance can be significantly affected until the query is not available until the index is rebuilt. Another potential flaw is that when both requests are indexed, the blocks are blocked until the index is rebuilt. Blocking is also handled by other means, that is, indexes are not dropped when used. Another major flaw is that when clustered indexes are rebuilt using DROP, INDEX, and CREATE INDEX, non clustered indexes are rebuilt two times. When the clustered index is deleted, the row index of the non clustered index points to the data heap. When the clustered index is rebuilt, the row pointer of the non clustered index also refers to the row location of the clustered index. One good thing about deleting and rebuilding indexes is to rebuild the index completely by reordering the index pages, compact the index pages, and removing unnecessary index pages. You may need to consider those internal and external shards that are high enough to make the indexes return to where they should be. Rebuilding indexes using the DROP_EXISTING clause In order to avoid rebuilding the clustered index without a clustered index on the table two times, you can use the CREATE INDEX statement with the DROP_EXISTING clause. This clause preserves the clustered index key to avoid the non clustered index being rebuilt two times. Like deleting a