Performing routine database maintenance such as reindexing is important for keeping your databases running at peak performance. When you use INSERT, UPDATE, and DELETE statements to modify table data, indexes can become fragmented. Index fragmentation can happen when the logical sequence of pages is disrupted or when an index page contains less than its maximum amount of data, creating a gap in the data page or index. As indexes become fragmented, you get inefficient data reads when accessing tables and slower database performance.
But reindexing database tables takes time, and many DBAs already have limited time to complete such necessary maintenance jobs. One way DBAs can decrease the time required for maintenance is to reindex database tables according to set thresholds or limits. For instance, in a table that has 10 indexes, only two might be fragmented and require reindexing. If you run the DBCC DBREINDEX command without specifying individual indexes to be rebuilt or as part of the SQL Server scheduled maintenance plan job, the command will rebuild all 10 indexes. But if you rebuild only the two fragmented indexes, you save time and resources. By using the utility I describe in this article, you can rebuild only indexes that meet certain requirements.
You can determine when an index needs to be rebuilt by running the DBCC SHOWCONTIG command and looking at the values the command returns for Logical Scan Fragmentation, Extent Scan Fragmentation, and Scan Density. Logical Scan Fragmentation represents the percentage of pages that DBCC SHOWCONTIG finds out of order when it scans an index's leaf pages. Extent Scan Fragmentation represents the percentage of out-of-order extents DBCC SHOWCONTIG finds when it scans the leaf pages of an index. For both the Logical Scan Fragmentation and Extent Scan Fragmentation values, the lower the percentage the better. Conversely, the Scan Density value is the ideal number of extent changes if all pages and extents are contiguously linked and ordered properly. The closer this value is to 100 percent, the better. So if eight of a table's 10 indexes have greater than 95 percent Scan Density and less than 1 percent Logical Scan Fragmentation, you probably don't need to rebuild those eight indexes because the index fragmentation is low.
The threshold values that you use to determine whether to rebuild an index can vary from index to index based on such factors as the size of the table and frequency of use. For example, a table that has 10 million rows might have great performance if its Logical Scan Fragmentation is less than 5 percent but start experiencing performance problems if that value rises above 5 percent. However, a table that has 10,000 rows might not experience performance problems until the Logical Scan Fragmentation value is greater than 15 percent, at which time you need to rebuild the index to regain lost performance. Because of this variation, you need to set thresholds for each index to minimize table-reindexing time.
This article's example shows how I use the results of the DBCC SHOWCONTIG command—specifically the Scan Density and Logical Scan Fragmentation values—to determine which indexes to rebuild on tables in the SQL Server 2000 Northwind sample database. For this example, I use a stored procedure to set each index's default threshold values to 80 percent for Scan Density and 10 percent for Logical Scan Fragmentation. Any index that has a Scan Density of less than 80 percent and a Logical Scan Fragmentation greater than 10 percent needs to be rebuilt. I store these threshold values in a table that I can update when I need to streamline database maintenance.
Setting a Baseline
The first step in automating reindexing is to determine a baseline of appropriate DBCC SHOWCONTIG values for each table index by rebuilding all indexes. The time you spend on this step will save you time later when you've automated index maintenance. After you have a baseline, you can use the results of DBCC SHOWCONTIG to determine appropriate threshold values for each index.
Smaller tables might require different thresholds than larger tables because the difference in the number of extents affects the results you get when you reindex a table. For example, a small table that has only two extents and one primary key index might produce a Scan Density value of 50 percent and a Logical Scan Fragmentation value of 0 percent even after you rebuild it. Therefore, reindexing this table would yield no benefit. But a large table that has hundreds of extents and one primary key index might produce a Scan Density value of 95 percent and a Logical Scan Fragmentation value of 11 percent after you reindex it. If you set the thresholds of both these tables to a Scan Density value of 80 percent and a Logical Scan Fragmentation value of 10 percent, every time the reindexing job executes, it will rebuild the indexes for both tables because the smaller table will always have a Scan Density value of less than 80 percent. Establishing a baseline lets you determine the best DBCC SHOWCONTIG values for each index. And as your tables change, you can update the threshold values to maximize performance.
Assembling the Parts
Once you have a baseline and have decided what threshold you want to set for your indexes, you can create a SQL Server job that determines which indexes the utility needs to rebuild. The utility I use for reindexing requires a new database (which I've named SHOWCONTIGDEMO in this example) and three tables (OBJECT_LIST, STAGE_OBJECT_DATA, and OBJECT_
DATA) to work. I've provided listings that contain the scripts you need to set up the job and the database and tables it uses. You can download the Web listings at http://www.sqlmag.com, InstantDoc ID 43783.
Web Listing 1 creates the SHOWCONTIGDEMO database to store the DBCC SHOWCONTIG values that the example stored procedures will collect from Northwind's tables. I put this new database on the same server as Northwind. Next, when you execute Web Listing 2's scripts in the SHOWCONTIGDEMO database, the scripts build the OBJECT_LIST, STAGE_OBJECT_DATA, and OBJECT_DATA tables. The scripts in Listing 1 build three stored procedures that insert and delete index data in these three tables and compare threshold levels to determine whether you need to rebuild an index. Web Listing 3's two scripts create Listing 1's stored procedures in the Northwind database. Finally, Listing 2's script, page 42, creates on the server a new SQL Server job named DBCC SHOWCONTIGDEMO.