Incremental Index Maintenance with T-SQL

A fundamental duty of production DBAs is to ensure that indexes are periodically maintained. Often there's a desire to reorganize the index if a certain level of fragmentation is detected (e.g., between 10 percent and 20 percent) and rebuild the index completely if the fragmentation is greater than a certain percentage (e.g., greater than 20 percent). Measuring fragmentation and taking the appropriate action when necessary is pretty straightforward for databases with no resource constraints.

In a resource-constrained environment (e.g., an environment in which there are large databases), you really don't want to do the fragmentation measurement and index defragmentation all at once for an entire database. With the focus on availability and performance (especially in mission-critical systems), you want to both determine fragmentation levels and defragment indexes in a non-impactful way.

Index Maintenance Solution

We developed an index maintenance solution that measures index fragmentation and performs index defragmentation in a highly incremental and iterative manner. First, the solution measures the fragmentation level of a specified number of indexes at a time, recording the information, until all the measurements are taken. Then, it reorganizes or rebuilds indexes as appropriate, again working on a specified number of indexes at a time. After a complete cycle of measurement and defragmentation has completed, the whole process starts all over again.

The solution is comprised of three major components: a table, a T-SQL stored procedure, and a scheduled job to run the stored procedure. A single table stores the table and index details (e.g., table and index names), the fragmentation information for the indexes (e.g., level of fragmentation), and when the last refresh occurred. If the table doesn't exist, it will be created by the second major component of the solution, a T-SQL stored procedure named usp_IndexDefrag. Listing 1 shows the code in usp_IndexDefrag that creates the table.

Besides creating the table, the usp_IndexDefrag stored procedure measures and records the level of fragmentation. You can configure how many indexes to process at a time with the @HowManyIndexInfoToPull parameter. Setting this parameter to 0 results in the job processing all the indexes. Setting it to 20, for example, results in 20 indexes being processed at one time.

After the solution determines that the fragmentation information has been gathered for all the indexes, it switches to performing index maintenance, reorganizing or rebuilding indexes when appropriate. You can configure how many indexes to possibly defragment at a time with the @HowManyIndexToDefrag parameter.

If the fragmentation level is below what is specified in the @avg_fragmentation_in_percent_limit_Reorg parameter, no defragmentation will occur. If the fragmentation is above the level specified in the @avg_fragmentation_in_percent_limit_Rebuild parameter, the index is rebuilt. If the fragmentation is between the levels specified in these two parameters, the index is reorganized.

Configure a Time Limit

To ensure that the solution doesn't adversely affect database availability and performance, you can configure a time limit that will override the @HowManyIndexToDefrag and @HowManyIndexInfoToPull values. You set the @DurationH and @DurationM parameters to indicate how many hours and minutes, respectively, to limit the measurement and defragment operations.

For example, suppose you set the parameters as follows:

@HowManyIndexToDefrag=2@HowManyIndexInfoToPull=20@DurationH='01'@DurationM='30' 

With these parameter values, the measurement operation will stop after 90 minutes, even if all 20 indexes haven't been processed. Similarly, the defragmentation operation will stop after 90 minutes, even if the two indexes haven't been processed. If the time limit is reached in the middle of an operation, the solution will complete the operation for that index. The indexes that weren't processed because the operation was terminated will be addressed by a future execution of the stored procedure. Eventually all the indexes needing defragmentation will be defragmented, assuming their level of fragmentation falls within the specified ranges for reorganizing and rebuilding.

The solution's third component is the scheduled job to run the usp_IndexDefrag stored procedure. It calls the stored procedure using the various parameter values you set. Listing 2 shows a sample call. You need to set up a separate job for each database. Typically, the stored procedure would be scheduled to run almost every night. Some exceptions might be those nights when the organization performs other types of maintenance, such as new code deployment.

You can download the solution by clicking the link at the top of this page. This solution works on SQL Server 2008 and SQL Server 2005.