Automatic Reindexing

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.

Executing the Job

You now have all of the tables, stored procedures, and jobs necessary to use this utility and rebuild your table indexes as necessary. The DBCC SHOWCONTIGDEMO job that Listing 2 creates performs five steps:

1.Inserts Northwind table and index data into the SHOWCONTIGDEMO database.

2.Deletes obsolete index names and IDs from the OBJECT_DATA table.

3.Inserts new index names and IDs into the OBJECT_LIST_DATA table.

4.Inserts information about the tables and indexes you're going to scan into the OBJECT_DATA table by using DBCC SHOWCONTIG.

5.Runs the reindex check and rebuilds any indexes that need it.

The first time this utility runs, it has to build the initial catalog of tables and indexes; it executes DBCC SHOWCONTIG against all the tables in Northwind and populates the STAGE_OBJECT_DATA table with the resulting data about Northwind's tables and indexes. I put all the data in this new table because it lets the DBA check multiple databases on the same SQL Server instance. Also, using a separate table means I don't have to store maintenance data in my production databases.

The utility doesn't delete any rows on its first run because all the tables are empty. But in subsequent runs, Step 2 of the job checks that the name of each index and its associated ID in the STAGE_OBJECT_DATA table match the current corresponding values in the OBJECT_LIST table. If the values in the two tables are different, the job deletes the data for that index from OBJECT_LIST and OBJECT_DATA. For example, if index 5 on the Orders table has been deleted and replaced by a new index, the job deletes the existing data for index 5 and collects data for the new index 5. Then, steps 3 and 4 insert the new index data into the SHOWCONTIGDEMO database tables OBJECT_LIST and OBJECT_DATA. The job rebuilds the existing index in Step 5 as required, according to the current threshold values in the OBJECT_LIST table.

The job uses the stored procedure p_ins_OBJECT_LIST to populate the OBJECT_LIST table with the default thresholds for Scan Density (80 percent) and Logical Scan Fragmentation (10 percent). These default values are a generic baseline for all tables; you can change the values after the OBJECT_LIST table is populated. The OBJECT_LIST table contains only one record for each index and doesn't contain historical records.

The job then populates the OBJECT_DATA table with the results from DBCC SHOWCONTIG that were stored in STAGE_OBJECT_DATA in Step 1 of the job. The OBJECT_DATA table contains a new record for each index every time you run the DBCC SHOWCONTIGDEMO job, and the STAGE_OBJECT_DATA table contains the current view of the tables and indexes. Each time it runs, the reindexing utility compares the staging table to the OBJECT_DATA table to determine whether any indexes have been dropped or are obsolete and to update OBJECT_DATA with new records to maintain an index history.

Finally, the job uses the p_REINDEX_CHECK stored procedure, which Web Listing 3 created, to check the Scan Density and Logical Scan Fragmentation DBCC SHOWCONTIG results for each index against the default thresholds in the OBJECT_LIST table. This stored procedure uses a cursor to select every table and index in Northwind, then compares the current table values to the threshold levels you set. The job uses the DBCC REINDEX command to rebuild any index that has a Scan Density value below 80 percent and a Logical Scan Fragmentation value greater than 10 percent. Then, the job writes the comparison results to a text file that you can view in Enterprise Manager; you just select Step 5 of the job, click the Advanced tab, then click View. Figure 1, page 42, shows part of the output of DBCC SHOWCONTIG for index ID 3 of Northwind's Orders table, which is an example of the kind of results you'll see when you run the job.

In Step 5, the DBCC SHOWCONTIGDEMO job automatically reindexes the Orders table because the results of DBCC SHOWCONTIG are outside the default thresholds you set in the p_ins_OBJECT_LIST stored procedure. Remember that if the default threshold of 80 percent for Scan Density isn't appropriate for a certain index, you can change the threshold in the OBJECT_LIST table.

Track Table Growth

In addition to being a repository for DBCC SHOWCONTIG data for the utility, the OBJECT_DATA table is also useful for table-growth analysis. Because the DBCC SHOWCONTIGDEMO job inserts a new record into this table every time it runs, you can track the usage of tables and indexes over time. For example, the count_rows field in the OBJECT_DATA table shows the number of rows for each table in the Northwind database, so as you run the job and collect records, you can calculate the growth of a given table. If you scheduled the job to run weekly, you could calculate the average number of rows added to each table weekly, monthly, and yearly.

Using this utility on your production systems will help you minimize maintenance-related downtime and track table growth in your database. This article's example job uses the DBCC SHOWCONTIG Scan Density and Logical Scan Fragmentation values to determine when to rebuild an index. But you could easily modify the job to include another field or fields as determining criteria. Or you could filter out any table that doesn't meet certain requirements, such as a minimum number of pages, so you don't rebuild small tables that have only one extent.