Reduce Time for SQL Server Index Rebuilds and Update Statistics

Problem

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.

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.

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:

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:

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 fullscanGO

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 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

Next Steps

The concepts and ideas are not revolutionary, however they can be easily overlooked. By making small changes to SQL Server maintenance for rebuilding indexes and updating statistics we can take advantage of features available in SQL Server to reduce
the maintenance time.

@Mario, thanks for the link - sounds interesting I will have take a look. @Jim I captured the values between runs by using something similar to this Mssqltip: http://www.mssqltips.com/sqlservertip/1360/clearing-cache-for-sql-server-performance-testing/ Then plugging the values into MS Excel to chart. @Rafael, that is a great point but as Jeff pointed out the article is intentional to discuss needs for offline rebuilds. @Jeff thanks for the comments I agree it's interesting that Microsoft's Best practice/recommendation matches up. The server I used has over 32 processors, but the sweet spot overall was 8 procs. I would be interested to hear if your tests are the same. @AlexB thank you for referencing this tip here, I'm working on an article on the resource governor, from different perspective and includes my suggestions mentioned here as well. Thanks for sharing your blog will take a look. @Kishore thanks for reading, I always find useful information on this website myself. @Zen not sure if you have tested what ALZDBA has referenced, but it does hold true. Also as Filipe comments on update of statistics may be of interest for you.

I wrote a post explaining how to use Resource Governor to achieve a custom MaxDOP for specific SQL Server jobs: http://dbazen.net/2014/02/03/using-resource-governor-to-set-maxdop-for-specific-sql-server-jobs/

I referenced your article, as I think there is some great information here. Hope you don't mind. If you prefer, I can remove the link.

What I'm most impressed with in this article is that it proves that 8 processors is the proverbial "sweet spot" for that particular system and task. Considering Grant Fritchey's recommendations on setting the Threshold of Parallelism, I wonder how many other heavy-lifting tasks share that same "Sweet Spot'.

He didn't forget about being able to rebuild indexes online. He specifically stated that it's for rebuilding indexes that must be rebuilt offline (anything with a blob in it including the Clustered Index or Non-Clustered Indexes that INCLUDE a blob column).

Thanks for the article. I'm curious of how you created the data, table and charts. Primarily figures 2 & 3. for me to evaluate the results of the changed number of processors how did you capture the values?

Interesting that people are concerned about clearing the plan cache when the procedure in question is rebuild indexes and recompute statistics.

Both of those operations will force procedure re-compiles anyway and rightfully so. If you have rebuilt indexes and/or recomputed statistics is because you want new plans that use them.

Of course mileage will vary depending on your system and everything should be tested.

Another thing to think about is that using parallelism you will end up with indexes that are not fully defragmented, because they are being created in parallel threads. You really need to test and verify what is the most important part for you.

Thanks everyone for the comments, great to hear from you Victor-come visit sometime!

ALZDBA good pointing that out, and as JohnL pointed out yes milage will vary depending on your environment. Unless the SQL Server has a planned server reboot around the same time(which will clear the cache as well) ...the recommendation for creating a job changing MAXDOP would be a very specific case. Darek great idea on using sys.configurations however as ALZDBA pointed out it can lead to some unitentional consequences playing with parallelism at the instance level of SQL Server. But by using ALTER INDEX and specifying MAXDOP instead we can still take advantage of benefits. Also an alternative solution is to use resource governor to manage parallelism.

Hi Wilfred thanks for sharing your link with everyone, true for cases of non maintenance during normal database operation on MAXDOP usage, unfortunately it is outside scope of this article. I've seen MAXDOP settings for SQL Server based on vendor recommendation, workload, as well as based on some testing with the SQL Server to know what works best. MSSQLTIPS has serveral great articles for further reading.

Hi Krystian, the number of parallel threads for indexes by default will be based on what is in "sp_configure" for max degrees of parallelism. SQL Server automatically will use them. Example if your database has max degree of parallelism value of 1. Then Index maintenance will use "1" If it's "4" then it will use "4" If it's "8" then it will use "8"

Maybe someone knows that. How to check how many parallel threads were used to create an index? Using explain plan doesn't show the information. I also checked Estimated Execution Plan and Actual Execution Plan.

What is funny, If I run the Create Index with Actual Execution Plan I see that reading/sorting/writing has the Parallelism icon, but when I use SET SHOWPLAN_ALL ON and run the create index I see Parallel=0

You should NOT play with Maxdop at instance level just to improve your index rebuild. beause it controls the number of processors that are used for the execution of a query in a parallel plan. Depending of the type of your database, the setting of Maxdop has a huge impact on your performance. See http://support.microsoft.com/kb/2806535

I like the article, straightforward and useful. I also appreciate the comments, especially from alzdba regarding the procedure cache clearing. A great illustration of some many things in the SQL Server world that are like the ads for new cars: "Your mileage may vary".

While this may seem a good idea, and certainly serves the aimed purpose for your index rebuilds,

I wouldn't play around with Max Degree Of Parallelism at instance level because of 1 simple reason:

The whole procedure cache is cleared if one of the following server options is changed by the RECONFIGURE statement:

cross db ownership chaining

index create memory (KB)

remote query timeout (s)

user options

max text repl size (B)

cost threshold for parallelism

max degree of parallelism <<<<<<<<<<<<<<<<----- heeeeere's Johnny

min memory per query (KB)

query wait (s)

min server memory (MB)

max server memory (MB)

query governor cost limit

Note Procedure cache will not be cleared if the actual value does not change or if the new value for the max server memory server option is set to 0.

ref: http://support.microsoft.com/kb/917828

I think the overhead of your system having to recompile each and every query/proc, .. isn't worht the advantage for your rebuild indexes stuff, let alone sqlplans may change due to this causing queries to slow down, hence helpdesk red phones.

I forgot to tell you this. Instead of hard coding the MAXDOP to which one should revert after the update to statistics/indexes has been made, one could use the [sys].[configurations] system view to obtain the current value of the 'max degree of parallelism' setting. The script would then be flexible and would not have to rely on the hard-coded value that could change in the future. Simply saying, if you temporarily change something on the system, make it so that you always revert back to the original value afterwards, whatever the value is, without having to change the code. Once again - thank you for the article.