These reports were taken after only a small amount of processing had occurred on this system, yet already we can see that there has been a fair amount of fragmentation of the data. The table MyTable1 has already begun to show signs of performance degradation. When there is an unusually large amount of new data being inserted into the tables, these numbers will quickly begin to resemble the those that we see in the previous report.
In the table MyTable2, we see a stark difference from MyTable1.This is because of some index tuning that I had done on that table. As I said earlier, SQL Server uses the clustered indexes in order to understand how data should be ordered. To prevent page splits, I had SQL Server leave each page only 50% full. This allows for multiple inserts to occur without generating page splits, allowing our scan density to remain high for a longer period of time. But this also comes at the cost of reducing the quantity of contiguous records on each page and doubles the amount of space consumed by the table, hence the now much larger number of pages and extents scanned.
Conclusion
From examining the output of DBCC SHOWCONTIG, we were able to locate several key issues. First, we saw that our database was heavily fragmented, and required defragmentation using DBCC DBREINDEX. Next, we were able to tell what percentage of the allocated pages were actually being used by SQL Server. Finally, we saw that by modifying the fillfactor on an index, we had a tremendous affect on page splitting at the cost of more page I/O for each read.
Copyright Dean Thompson, 2002, All Rights Reserved. Reprinted with permission.]]>