SQL Server Index Fragmentation and Its Resolution
DBCC SHOWCONTIG is really only useful for large tables. Smaller tables will show results that do not meet the normal standards simply because they may not be made up of more than 8 pages. This small size will throw off what you should look for in the results of DBCC SHOWCONTIG. You should only worry about the results shown for: Extent Switches, Logical Scan Fragmentation, Avg. Bytes Free per Page, and Avg. Page Density (full) when dealing with smaller tables.
The information output by DBCC SHOWCONTIG defaults to pages scanned, extents scanned, extent switches, avg. pages per extent, scan density [best count : actual count], logical scan fragmentation, extent scan fragmentation, avg. bytes free per page and avg. page density (full); this output can be controlled with the FAST and TABLERESULTS options.
The FAST option specifies whether to perform a fast scan of the index and output minimal information, this option does not read the leaf or data level pages of the index and will only return pages scanned, extent switches, scan density [best count : actual count], and logical scan fragmentation.
The TABLERESULTS option will display the information as a rowset and will return extent switches, avg. bytes free per page, avg. page density (full), scan density, best count, actual count, logical fragmentation, and extent fragmentation.
Specifying both the FAST and TABLERESULTS options will return object name, object id, index name, index id, pages, extent switches, scan density, best count, actual count, and logical fragmentation.
The ALL_INDEXES option will display results for all the indexes for the specified tables and views, even if a particular index is specified.
The ALL_LEVELS option will specifies whether to produce output for each level of each index processed (default is to output only the index leaf level or table data level) and can only be used with the TABLERESULTS option.
Resolving Fragmentation Issues
Once you determine that you table or index has fragmentation issues, you have four choices to resolve those issues:
- Drop and Recreate the Index
- Recreate the Index with the DROP_EXISTING clause
- Execute DBCC DBREINDEX
- Execute DBCC INDEXDEFRAG
While each of these techniques will achieve your ultimate purpose of defragmenting your index, each have their own pros and cons.
Drop and Recreate the Index
Some of the disadvantages of dropping and recreating an index with either DROP INDEX and CREATE INDEX or ALTER TABLE includes the disappearance of the index while you are dropping and recreating it. As the index is dropped and recreated, it is no longer available for queries and query performance may suffer dramatically until you can rebuild the index. Another disadvantage of dropping and recreating an index is the potential to cause blocking as all requests to the index are blocked until the index is rebuilt. This technique can also face blocking by other processes, as the process of dropping the index cannot take place while other processes are using the index. Another major disadvantage of this technique is that rebuilding a cluster index with DROP INDEX and CREATE INDEX has the effect of rebuilding all of the nonclustered indexes twice. Once as the clustered index is dropped and the nonclustered index row pointers are pointed to the data heap and again as the clustered index is rebuilt and the nonclustered index row pointers are pointed back to the clustered index row locations.
Dropping and rebuilding an index does have the advantage of completely rebuilding an index which does reorders the index pages, compacting the pages, and dropping any unneeded pages. You may need to consider dropping and rebuilding indexes that show high levels of both internal and external fragmentation to get those indexes back to where they should be.
Recreate the Index With the DROP_EXISTING Clause
To avoid the processing it takes to rebuild the nonclustered indexes twice on a table that you rebuild a clustered index, you can use the CREATE INDEX with DROP_EXISTING clause. This clause will keep the clustered index key values, avoiding the need to rebuild the nonclustered indexes twice. Like the regular DROP INDEX and CREATE INDEX technique this technique can cause/face blocking problems and index disappearance problems. Another disadvantage is that using the CREATE INDEX with DROP_EXISTING technique also forces you to find and repair each index on the table separately.
Besides the advantages associated with the regular drop and recreate technique and the advantage of not having to rebuild nonclustered indexes twice using the CREATE INDEX with DROP_EXISTING clause can be used for indexes with constraints provided that the index definition exactly matches the requirements of the constraints.
Execute DBCC DBREINDEX
DBCC DBREINDEX is similar to CREATE INDEX with DROP_EXISTING, but it will rebuild the index physically allowing SQL Server to assign new pages to the index and reduce both internal and external fragmentation. DBCC DBREINDEX also has the ability to recreate indexes with constraints dynamically, unlike CREATE INDEX with DROP_EXISTING.
The disadvantages of DBCC DBREINDEX is that it faces the problem of causing/facing blocking and DBCC DBREINDEX is executed within a transaction so if it is stopped before completion, you lose all the defragmentation that had been performed.
Execute DBCC INDEXDEFRAG
DBCC INDEXDEFRAG (available in SQL Server 2000) reduces external fragmentation by rearranging the existing leaf pages of an index to the logical order of the index key and internal fragmentation by compacting the rows within index pages then discarding unneeded pages. It does not face the blocking problems of the other techniques but the results of DBCC INDEXDEFRAG are not as complete as the other techniques. This is because DBCC INDEXDEFRAG skips locked pages due to its dynamic nature and does not use any new pages to reorder the index. You may also discover that the time needed by DBCC INDEXDEFRAG is longer than recreating an index if the amount of fragmentation is large. DBCC INDEXDEFRAG does have the advantage over the other techniques due to its ability to work on defragmenting an index while other processes are accessing the index, eliminating the blocking problems of the other techniques.