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.
End of Article
Prev. page
1
[2]
next page -->
