Relevant T-SQL Stored Procedures

To set up the environment for the maintenance stored procedures, you start by creating a database to hold the statistics gathered during each execution. The Create Admin Database script creates a database named AdminData containing two tables. The DBCCStatistics table holds the results of the DBCC SHOWCONTIG WITH TABLERESULTS command that the uspBuildFragList stored procedure uses. You can delete the TableRowCounts table; the stored procedures in this article don't use it. Be aware that if you change the database name, you must modify the setup scripts of the stored procedures I discuss here and replace all references to AdminData with the correct database name.

The next step is to load and run the uspBuildFragList script. It creates the uspBuildFragList stored procedure in the AdminData database. If you changed the database name, do a search for AdminData and replace it with the new database name.

Repeat the previous step with the uspDefragTables, uspRecompile, and the DBMaintenance scripts. You must create uspDefragTables and uspRecompile before DBMaintenance because DBMaintenance has references to the first two stored procedures.

DBMaintenance is the stored procedure that does all the work. Note the use of DBCC UPDATEUSAGE at the beginning of the proceudre. This command performs a step that's essential to obtain accurate results for DBCC SHOWCONTIG. Create a job that includes a T-SQL step for each database that you want to monitor and/or defragment by using the following code:

Exec DBMaintenance 10, ‘database name' 

Database name is the name of the database you want to defragment, and 10 is the maximum fragmentation allowed before uspDefragTables runs DBCC DBREINDEX. In my tests, less than 10 percent fragmentation didn't seem to help performance, and it usually caused all my tables to be defragmented each night, which almost doubled the time the maintenance procedure took to finish. Remember that these maintenance tasks create heavy disk activity; therefore, you should schedule the job during a time when you can afford the drag on performance.

All these tasks are well suited to multithreaded, parallel processing. In my tests, run individually in sequence, my five databases took about 90 minutes, on average, to defragment. Run as five separate jobs executing in parallel, it took about 55 minutes on average. Additionally, try to schedule the task so that it finishes before a full backup of the database starts. Defragmentation and backups compete for almost every resource that SQL Server uses; therefore, it's best if they don't run at the same time.