SQL Server database defragmentation--BCC showcontig

SQL Server provides a database command--DBCC showcontig--to determine whether a specified table or index is fragmented.   Example: Displaying fragmentation information for all indexes in a database DBCC SHOWCONTIG with all_indexes  displays fragmentation information for all indexes of the specified table DBCC SHOWCONTIG (authors) with All_ indexes  Display fragmentation information for the specified index DBCC showcontig (authors,aunmind)  DBCC execution Results: Number of pages scanned: If you know the approximate size of the row and the row count in the 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.    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.    Expansion Switch Number: The number should be equal to the number of scan extents minus 1. High, the external fragments are indicated.    average pages per 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 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: Percentage of unordered pages. The percentage should be between 0% and 10%, and high will indicate an external fragment.    Extents Scan Fragmentation: the percentage of unordered extents that are in the scan index leaf-level pages. The percentage should be 0%, and high will indicate an external fragment.    average number of bytes available per page: The average number of bytes available on the page being scanned. 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): the inverse of the average percentage of bytes available on each page. A low percentage indicates an internal fragment.    Solving Fragmentation issues: 1. Delete and rebuild the index  2. Use the DROP_EXISTING clause to rebuild the index  3. Execute DBCC DBREINDEX  4. Execute DBCC indexdefrag   Delete and rebuild the index:  with drop Index and CREATE INDEX or ALTER TABLE to delete and rebuild the index some defects include the index disappearing during the delete rebuild. When an index is removed from a rebuild, query performance may be significantly affected until the index is rebuilt, for queries that are not available. Another potential drawback is that when the index is requested, it causes blocking until the index is rebuilt. Other processing can also solve the blocking, that is, the index is used when the index is not deleted. Another major drawback is that the nonclustered index is rebuilt two times when the clustered index is rebuilt with DROP index and create index. When you delete a clustered index, the row pointer of the nonclustered index points to the data heap, and the row pointer of the nonclustered index when the clustered index is rebuilt also refers back to the row position of the clustered index.    deleting and rebuilding an index it is a good thing to rebuild the index completely by reordering the index pages, making the index pages compact, and deleting the unwanted index pages. You might want to consider those situations where both internal and external fragments are very high, so that those indexes return to where they should be.   Rebuilding an index with the DROP_EXISTING clause:  to avoid rebuilding a nonclustered index on a table two times when rebuilding a clustered index, you can use the CREATE INDEX statement with the DROP_EXISTING clause. This clause preserves the clustered index key value to prevent nonclustered indexes from being rebuilt two times. As with deleting and rebuilding indexes, this method can also cause problems with blocking and indexing disappearing. Another drawback of this approach is that you are also forced to find and fix each index on the table separately.    In addition to the same benefits as the previous method, the benefit of this approach is that you do not have to rebuild the nonclustered index two times. This provides the correct index definition for those constrained indexes to meet the requirements of the constraint.    Execute DBCC dbreindex:  DBCC Dbreindex is similar to the second method, but it physically rebuilds the index, allowing SQL Server to allocate new pages to the index to reduce internal and external fragmentation. DBCC Dbreindex can also dynamically reconstruct constrained indexes, unlike the second method. The defect of   DBCC Dbreindex is that it encounters or causes blocking problems. DBCC Dbreindex is run as a transaction, so if it is interrupted before it is complete, you will lose all the fragments that have been executed.    Execute DBCC Indexdefrag:  DBCC indexdefrag (available in SQLServer2000) according to the cableThe logical order of the keys is to reduce the external fragmentation by re-organizing the leaf pages that exist in the index to reduce the internal fragmentation by compressing the rows in the index page and then deleting the unwanted pages that are generated. It does not experience blocking problems but it does not result in several other methods altogether. This is because DBCC INDEXDEFRAG skips the locked page and does not use any new pages to reorder the indexes. If the number of fragments in the index is large, you may find that DBCC INDEXDEFRAG takes longer than rebuilding the index. DBCC Indexdefrag is certainly better than other methods to defragment other procedures while accessing the index, without causing blocking problems for other methods.   Source: http://blog.sina.com.cn/s/blog_6d2675450101ks6i.html

SQL Server database defragmentation--BCC showcontig