Reduce Time for SQL Server Index Rebuilds and Update Statistics

We have been asked to reduce our maintenance window for the SQL Server off-line index rebuilds and updating statistics in order to keep our systems online longer. Is there a way to speed up off-line index rebuilds or updating statistics in SQL Server?  Check out this tip to learn more.

Solution

This tip will explore two features to speed up SQL Server index and statistics maintenance.  The first part of this tip focuses on SQL Server Enterprise Edition to reduce the duration for index maintenance for off-line rebuilds. The second part of this tip is intended to reduce the duration for update statistics as it pertains to both SQL Server Standard and Enterprise Edition.  

Rebuilding SQL Server Indexes with Enterprise Edition

Before beginning, we’ll briefly review the term maximum degree of parallelism for processors. The maximum degree of parallelism (MAXDOP) is defined as the maximum number of logical processors (CPUs) that SQL Server can use in a single query. By changing the number of processors SQL Server can use in parallel, in other words the maximum degree of parallelism (MAXDOP), we can improve index rebuild performance.  This option is by default set to zero instance-wide on SQL Server, it does not mean use zero processors. It means SQL Server will use all available processors.
We can check the degree of parallelism currently set instance-wide on a SQL Server instance by running the following command:

sp_configure 'max degree of parallelism'

Figure 1

As a database administrator, our goal is to optimize SQL Server for the day to day workload. This may require configuring "maximum degree of parallelism" (MAXDOP) to a value different than the default.  In some circumstances the MAXDOP value can be less than 8, even though modern servers may have more than 8 processors. The image shown in Figure 1, indicates the run_value  is set  to "1," which allows SQL Server one processor to be utilized per query/statement. Due to the type of workload for figure 1, the maximum degree of parallelism is not configured to take advantage of multiple processors for parallel index rebuild operations in SQL Server Enterprise Edition.  We should take advantage of increasing the "maximum degree of parallelism" (MAXDOP) during maintenance for rebuilding indexes.
Figure 2 below is used to measure performance differences by changing the MAXDOP during index rebuilds for a 30MB table in the Adventure Works database. The table for this example includes a clustered index, 2 non-clustered indexes, 2 XML indexes, and secondary XML indexes.  The MAXDOP value is changed and measured for 1, 2, 4, 8, 16 and 32 processors.  See the sample index rebuild script below using the MAXDOP query hint.  The MAXDOP query hint ignores the instance-wide maximum degree of parallelism set by SQL Server and uses the explicit value in the T-SQL code.

ALTER INDEX ALL ON Person.Person REBUILD OPTION (MAXDOP 1)
GO
ALTER INDEX ALL ON Person.Person REBUILD OPTION (MAXDOP 2)
GO
ALTER INDEX ALL ON Person.Person REBUILD OPTION (MAXDOP 4)
GO
ALTER INDEX ALL ON Person.Person REBUILD OPTION (MAXDOP 8)
GO
ALTER INDEX ALL ON Person.Person REBUILD OPTION (MAXDOP 16)
GO
ALTER INDEX ALL ON Person.Person REBUILD OPTION (MAXDOP 32)
GO

Figure 2
Alternatively we can change the setting using sp_configure, adjusting the parallelism. Below is an example to change it instance-wide to a value of 8 without requiring the MAXDOP hint. Re-starting SQL Server is not needed for this instance-wide change.

sp_configure
'max degree of parallelism',8

GO

RECONFIGURE WITH OVERRIDE;

GO

ALTER INDEX ALL ON Person.Person REBUILD  

GO

Shown in Figure 3 below, a MAXDOP value of 8 for index rebuilds performs over 50% better than configured using a MAXDOP value of 1 in this test environment. In test environments when SQL Server is configured system–wide (sp_configure) with a MAXDOP value of 1, 2, or 4 due to SQL Server workload requirements, there can be improvements.  Test in your environment to determine which MAXDOP value performs the best.
Be sure to properly plan the maintenance and associated MAXDOP settings if the server is not a dedicated SQL Server instance, but contains multiple SQL Server instances or a multi-purpose server with other applications. Be sure when changing MAXDOP on a shared server you will not cause CPU issues for other applications.  If you believe there could be an issue, try adjusting the maintenance schedule or other system processing to reduce processor utilization between different applications.

Figure 3

This demonstration shows a MAXDOP value of 8 is ideal for my test environment. However, proper testing should be done to validate your own findings. To take advantage of this feature requires:

• SQL Server Enterprise Edition.
• Modifying existing Index Rebuilds to utilize MAXDOP value of 8

As a final note, keep in mind parallel index rebuilds is an Enterprise Edition feature available for: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

Modify SQL Server Index Rebuild Logic

Index Maintenance is typically performed through a SQL Server Agent Job on a scheduled basis. The maintenance can be custom scripts or commonly a SQL Server Maintenance Plan.   In either scenario, the one method to change the MAXDOP is to insert a job step before index rebuilds to change the MAXDOP value instance-wide and another job step after the index rebuilds complete to change the value back to the original setting.  MAXDOP can also be changed individually for each index rebuild statement with a MAXDOP query hint as shown earlier.
Earlier in Figure 1, the SQL Server environment is configured using a MAXDOP value of 1, through testing we found changing the MAXDOP to a value of 8 during index maintenance yielded the best performance.  Let's demonstrate how to setup a SQL Server Agent Job to do so:
1.  In SQL Server Management Studio, identify the Index Rebuild SQL Agent Job for your SQL Server:

Figure 4

2. Insert a new Job Step before the Index Rebuilds that will increase the MAXDOP value instance-wide to 8 as shown below in Figures 5 and 6.  This is accomplished by clicking on the "Steps" option on the left menu, then pressing the "Insert..." button on the bottom of the screen.  On the New Job Step interface configure as shown below.
Keep in mind this setting takes place dynamically and does not require a reboot or service restart.

Figure 5

Figure 6

3. Insert a new Job Step at end of Index Rebuilds to decrease the MAXDOP value back to the original configured value as shown below in figure 7.  This is accomplished by clicking on the "Steps" option on the left menu, then pressing the "Insert..." button on the bottom of the screen.  On the New Job Step interface configure as shown below.
(In this example Figure 1 shows configured an original MAXDOP value of 1, however the default setting is 0.)

Figure 7

4. Once changes are made to a job, be sure to verify the SQL Agent Job Steps from the beginning to the end. When inserting new Job Steps, it is possible that the Job "On Success" values will be incorrect as shown in Figure 8 below. In our circumstance, we need to change the Job Step “On Success” value for the second step to "Go to the next Step" to continue to the Job.

Figure 8

SQL Server Update Statistics

Updating statistics helps the SQL Server query optimizer create more optimal execution plans to improve query performance. Statistics are maintained on indexes and columns, by default statistics are updated on both indexes and columns. If a SQL Server maintenance window includes updating statistics and rebuilding indexes, we can shorten the time by updating only column statistics.

When index rebuilds occur, index statistics are automatically updated. Using this information we can change statistics maintenance to only update columns statistics. Below is a comparison between two statements updating all and only column statistics:

Original default updating column and index statistics (All):

update statistics Person.Person with fullscan
GO

Updating column statistics only:

update statistics Person.Person with fullscan, COLUMNS
GO

Figure 9

Figure 10

As indicated by Figure 10, there is potentially a substantial gain by skipping unnecessary index statistics updates. A common method for updating SQL Server statistics is scheduling a SQL Server Agent Job with custom scripts or a SQL Server Maintenance Plan.

Update Statistics with Custom T-SQL Scripts for SQL Server

If your SQL Server Agent Job uses custom scripts, it will be necessary to modify the existing code to explicitly specify column statistics only as shown below:
Template:

Update statistics [YOURTABLE] with fullscan, COLUMNS
GO

Example:

Update statistics Person.Person with fullscan, COLUMNS
GO

Update Statistics using a SQL Server Maintenance Plan

When using a maintenance plan for update statistics, by default "All existing statistics" are selected. 
If we already know we are rebuilding indexes, then we are already updating index statistics. We can modify the update Statistics Task to update "Column statistics only" as show in Figure 11  

Figure 11