This post looks at how to check if an index is fragmented in a Microsoft SQL Server table and how to rebuild the index. The advice offered in this post works for both SQL Server 2000 and SQL Server 2005 although the methods are deprecated for SQL Server 2005 and are likely to be dropped in the next release. I will look at the alternate way of doing this for SQL Server 2005 in a later post.
The DBCC SHOWCONTIG command shows you the index information. The example below checks the “indexname” index on the table “tablename” in the database “dbname”.
DBCC SHOWCONTIG('dbname.dbo.tablename', 'indexname')
Some example output is shown below. This particular example was from a very fragmented index from a table with around 10 million records. Each day around 1 million records are updated/and or inserted and records 60 days or older are deleted.
DBCC SHOWCONTIG scanning 'tablename' table... Table: 'tablename' (949578421); index ID: 1, database ID: 12 TABLE level scan performed. - Pages Scanned................................: 333557 - Extents Scanned..............................: 41805 - Extent Switches..............................: 262556 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 15.88% [41695:262557] - Logical Scan Fragmentation ..................: 75.11% - Extent Scan Fragmentation ...................: 13.04% - Avg. Bytes Free per Page.....................: 2853.9 - Avg. Page Density (full).....................: 64.74%
A low percentage for scan density is bad. A high percentage for logical scan fragmentation is bad.
To defragment your index you need to use the DBCC DBREINDEX or DBCC INDEXDEFRAG commands. DBCC DBREINDEX allows you to rebuild all indexes at once, but is an offline operation so the tables cannot be used while it is running. DBCC INDEXDEFRAG must be called for each index you want to defragment but you can continue to use the tables.
The syntax for DBCC DBREINDEX is as follows to rebuild the above index:
DBCC DBREINDEX('dbname.dbo.tablename', 'indexname')
or all indexes:
DBCC DBREINDEX('dbname.dbo.tablename')
You can also change the fill factor for the index, which affects how much space is left in each page with a third parameter. To make it e.g. 70, you could do this for all indexes in the table:
DBCC DBREINDEX('dbname.dbo.tablename', '', 70)
After doing the above on the example table and rebuilding the index with a fill factor of 70, running DBCC SHOWCONTIG again gave the following, much better, result:
DBCC SHOWCONTIG scanning 'tablename' table... Table: 'tablename' (949578421); index ID: 1, database ID: 12 TABLE level scan performed. - Pages Scanned................................: 297679 - Extents Scanned..............................: 37210 - Extent Switches..............................: 37209 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 100.00% [37210:37210] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.16% - Avg. Bytes Free per Page.....................: 2335.3 - Avg. Page Density (full).....................: 71.15%
Further reading
Take a look at the Microsoft SQL Server 2000 Index Defragmentation Best Practices article on TechNet and the reference pages for DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG for more information about how to use thes functions and their parameters.
These functions are for checking and defragementing indexes in Microsoft SQL Server 2000 and 2005 databases. As noted at the start of this post they are deprecated from SQL Server 2005 and will likely be removed in the next release. I will write another post at some stage looking at how to do the same things as in this post for SQL Server 2005.
