After running the provided scripts, you’ll see two new disabled SQL Server Agent jobs: DB Maint – Blocking Killer and DB Maint—Index Maint. I named the steps of the DB Maint – Index Maint job to be pretty self-explanatory. The following is a quick overview of the steps:
- Step 1 builds a tracking table that’s consumed by all subsequent steps to perform their operations. This step contains both an edition check and version check. If you aren’t using the appropriate edition and version for online rebuilds, all of the indexes targeted for rebuild will be done so offline.
- Step 2 and Step 8 are put in place to adjust the duration of your log backups if necessary. This functionality helps keep your transaction logs from growing too large and/or filling depending on your autogrowth and drive configurations. Keep in mind that your transaction logs won’t clear during a rebuild operation because, as I stated earlier, a rebuild operation occurs as a single transaction.
- Step 3 and Step 7 enable and disable the previously created DB Maint – Blocking Killer job to monitor the amount of blocking caused by a particular index operation. If you don’t want this feature to run, simply delete these steps.
- Step 4 and Step 9 are in place if you can switch your recovery model in your environment. I have included the Microsoft best practice method in each job step regarding switching recovery models on the fly. I included this feature because rebuild is a bulk logged operation, meaning it’s minimally logged when your database is in the bulk-logged recovery model. According to "SQL Server 2005 Online Index Operations," when rebuilding an index in a bulk-logged database, the log-to-data ratio is roughly 10 percent of the full recovery log-to-data ratio. Minimal logging is helpful when you want to keep transaction log sizes manageable, especially in replicated environments.
- Step 5 processes all the indexes in the table created by Step 1. You can change the retry interval and retry attempts on this step to the desired minutes to wait in the event the blocking killer terminates this step.
- Step 6 logs any failed or incomplete operations to the SQL Server error log.
- Step 10 goes back and recalculates fragmentation so that you can see a before and after snapshot.
- Step 10 and Step 1 use the sys.dm_db_index_physical_stats dynamic management view (DMV) to gather information about indexes, which requires only an intent-shared (IS) table lock, regardless of the mode that it runs in.
Figure 1 shows all of the above steps listed in SQL Server Management Studio (SSMS).
Job Parameters
You need to understand how all of various input parameters are utilized to custom tailor the index job to your environment. The stored procedure in the first step of the DB Maint – Index Maint job (Build Indexing Key) accepts a few parameters. @minFragPercent is the minimum percentage of fragmentation an index must possess before it’s considered for an index operation. You can set the minimum percentage of fragmentation according to your specific needs. Anything below this threshold will be ignored. @maxAttempts are the number of times an index operation will be retried that was previously killed by the DB Maint – Blocking Killer job for causing excessive blocking. If you want to target only one or two databases, use @databaseIncludeList, which is a comma delimited varchar. If you want to exclude only a few databases, use @databaseExcludeList, which is also a comma delimited varchar. Lastly, if you want to exclude only a particular table, you can do so by using @tableExcludeList.
The stored procedure in Step 5 of the index job also accepts a few parameters, which Figure 2 shows.
@reorgMinFragPercent is the minimum amount of fragmentation that must be present for a reorganization to occur. This value shouldn’t be below the @minFragPercent value in the Build Index Key step. @rebuildMinFragPercent is the minimum fragmentation that must be present for a rebuild to occur. For example, if you set these values to 20 percent and 30 percent, respectively, anything below 20 percent fragmentation will be ignored, anything between 20 and 30 percent fragmentation will be reorganized, and anything greater than or equal to 30 percent will be rebuilt online if possible.
If you set the two percentages to equal each other, only rebuilds will occur. @onlineOnly set to 1 means you’re going to perform only online rebuilds. Setting this value to 0 will result in indexes being built online where possible and offline where it’s not possible. @reorgNonOnlines when set to 1 in conjunction with @onlineOnly set to 1 lets indexes that can’t be rebuilt online be reorganized. I added @globalAllowReorgs to be a final fail safe that lets reorganizations occur on a global scale. In my environment it’s absolutely critical that some indexes not be reorganized. If all of your indexes can be reorganized, @globalAllowReorgs can be set to 1.
Lastly, the DB Maint – Blocking Killer contains one step that includes a stored procedure that accepts two parameters. @blockingMins is the number of minutes a SPID must be detected as blocking. This job runs every minute and performs the blocking check. If it finds the SPID performing an index operation blocking @blockingMins in a row, it will kill the SPID. To define blocking I added the @blockedProcesses parameter. If the blocking SPID is blocking more than @blockedProcesses processes, it’s considered to be an offender and its blocking count is iterated.
For the sake of time and space in this article, I have included extensive documentation in the executables describing what each step performs and the parameters they take. Once you’ve configured DB Maint – Index Maint to your liking, including specifying a schedule, be sure to enable it in SSMS by right-clicking the job and selecting Enable.
Maximize Index Performance
This solution lets you get maximum performance out of your indexes without having to sacrifice uptime. I could have added functionality to this job seemingly forever. These are a few extra jobs that can be utilized, expanded upon, or deleted to tailor this job to your environment. In the code, you’ll notice a few comments hinting at possible expansion. I tried to make it very easy to have a solid foundation to build upon. I realize no job is ever written perfectly or applied to all applications, but I hope my index job helps at least a few of you out there.