Executive Summary:
You can use this job to rebuild your indexes online to avoid the blocking caused by reorganizing indexes and the table locking caused by offline index rebuilds. You can customize this job for your environment by enabling and disabling various steps and parameters. Learn how to implement this job and what each step in the job does.
|
Recently, I was tasked with maintaining indexes under a five nines model. I was told by my boss that both the blocking caused by reorganizing indexes and the table locking caused by offline index rebuilds are unacceptable. Because our SQL Server environment is made up of all SQL Server 2008 and 2005 Enterprise Edition systems, I decided to avoid these problems by performing online index rebuilds. I set about researching in hopes of finding an appropriate customizable solution. Ultimately, I wound up creating my own solution, which I’ll share in this article in hopes you find it as useful in your environment as it is in mine. I’ll show you how I created the job, how to implement the job, and the various job parameters.
Creating the Online Index Job
My starting requirements for the job were simple. I first needed to identify which indexes could be rebuilt online. A quick look at MSDN revealed certain guidelines. Large object (LOB) data types (e.g., image, ntext, text, varchar(max), nvarchar(max), varbinary(max), XML) must be created, rebuilt, or dropped offline. Nonunique, nonclustered indexes can be created online when the table contains LOB data types, but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline. It’s important to identify which indexes can be rebuilt online because issuing a rebuild with the online index option on an index that fits the above offline criteria will result in an error, leaving your index unprocessed.
After identifying which indexes could be properly rebuilt online, I added various parameters to different steps of the index job. For indexes that can’t be rebuilt online, you can choose whether to reorganize them, ignore them, or rebuild them offline (if your business model allows). I also added parameters that let you reorganize indexes if they’re under a certain fragmentation percentage and rebuild them if they’re over a certain fragmentation percentage. Then I built in features to track each index’s duration, start time and end time, progress, fragmentation before and after running the job, and any resulting blocking. Lastly, I included an option to exclude databases and tables from index operations.
But didn’t my boss say blocking caused by reorganizing was unacceptable? To address this problem, I tracked the SPID of the index operation and created a sub-job to monitor any blocking that this operation might cause. If blocking exists for more than x minutes in a row and blocks more than x processes, the SPID is killed, a period of time passes, and the index operation is retried. According to Microsoft, reorganizes don’t hold long-term blocking locks; therefore, SQL Server won’t block running queries or updates. However, in high-transaction environments that can exceed 1,800 transactions per second, as in my environment, you have to be very careful with reorganizations, hence my blocking detector.
So what happens if the blocking detector kills a SPID that’s in the process of performing a rebuild? Because rebuild operations occur as single transactions, the rollback incurred from killing an offline rebuild on a large index that’s near completion would be far worse than simply letting the operation continue. For this reason, I don’t target them with the blocking detector. I also don’t target online rebuilds, but for different reasons such as possible performance implications related to reverting mapping indexes. According to "Microsoft SQL Server 2005 Online Index Operations" (http://technet.microsoft.com/en-us/library/cc966402.aspx) "There are only very short periods of time in which concurrent DML and select operations are prevented." Any blocking should be tolerable given the alternatives. A reorganize is a different story. Because a reorganize acts only on a pair of pages at a time, if it’s killed, only the current swap operation is affected. The next time a reorganize is issued, the job picks up where it left off.
Implementing the Index Job
To create the index job, run the executables, which you can download by clicking the Download the Code link at the top of the article page, in the following order:
- usp_update_schedule_enabler.sql
- Index_BuildIndexKey.sql
- Index_ProcessIndexes.sql
- Index_PostCleanup.sql
- Index_PostFragCalc.sql
- Index_HandleBlocking.sql
- job_Blocking_Killer.sql
- job_Indexing_Master.sql
Note I use a database called "Common" to house common functionality amongst multiple servers. Before you run these executable files, you’ll need to replace Common in each script with the references to suit your environment. Also, in the Index_BuildIndexKey procedure I create tables on a "Tables" file group. You’ll need to change Tables to the name of your file group or the job will fail. When you run job_Indexing_Master.sql, you’ll get four non-existent step warnings. They’re safe to ignore. Each step in a job has to be created sequentially, and some steps are set up to skip to future steps in the event of a step failure. For example, if Step 2 fails, the job goes to Step 8. Well when Step 2 is created, Step 8 obviously doesn’t exist yet, and a warning is issued.