Reduce Time for SQL Server Index Rebuilds and Update Statistics

By:   |   Comments (21)   |   Related: > Maintenance


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'

SpConfigure Image Figure1
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
Re-starting SQL Server is not needed for this instance-wide change.

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.

Multiple SQL Server instances or a multi-purpose Server with other applications/processes.
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:

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


Insert a new Job Step at the beginning before Index Rebuilds
Figure 5


increase the MAXDOP value instance-wide to 8
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.)

Insert a new Job Step at end of Index Rebuilds Step
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.

Once changes are made, be sure to verify the SQL Agent Job Steps processes from the beginning to the end
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

Original default updating column and index statistics (All)
Figure 9

Updating column statistics only
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  

Update Statistics using Maintenance Plan
Figure 11

 

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Norman Chan Norman Chan is a SQL Server DBA with over 12 years of IT experience as a software developer and DBA.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, August 3, 2018 - 4:23:22 AM - Greg Back To Top (76968)

Hello.

I've tested my MS SQL 2016 Enterprise server like in your post. The best time for alter table is with maxdop=0 on every database we use. What is the point of setting non default parameters?

Thursday, June 18, 2015 - 2:42:33 PM - JoshRotert Back To Top (37953)
Why not just use Ola's process? It has a ton of features and you can set the MAXDOP setting at a much lower level. https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Wednesday, March 5, 2014 - 3:38:10 AM - NormC Back To Top (29641)

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


Monday, February 17, 2014 - 11:38:35 PM - ZEN Back To Top (29483)

Thanks Norman, good article. very informative

 

ALZDBA, thanks for shareing the article. However this article is for SQL SERVER 2005 only. I will verify it in SQL SERVer 2008+ next week and update us. 

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

APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Express Edition with Advanced Services

Marios, thanks for sharing the two links. Will read later. 

 

 

Saturday, February 15, 2014 - 4:56:21 PM - Kishore Back To Top (29465)

Great article. And especially thanks a lot for showing how to set-up them up. It is very useful for Jr DBAs like me.

Alzdba, Marios thanks for adding valuable comments. AlexB, thanks for artcile about usage of RG.

Monday, February 3, 2014 - 12:40:18 AM - AlexB Back To Top (29314)

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. 

Saturday, February 1, 2014 - 7:32:03 PM - Jeff Moden Back To Top (29307)

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

I sense testing in my near future. ;-)

Saturday, February 1, 2014 - 7:21:12 PM - Jeff Moden Back To Top (29306)

@Rafael,

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

Friday, January 31, 2014 - 11:47:27 AM - Rafael Plácido Back To Top (29298)

Thanks

This is really helpful, but I think you are forgetting the rebuild index online, I know this could be helpful.

 

Friday, January 31, 2014 - 11:00:06 AM - Jim Back To Top (29297)

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?

 

Thanks,

Jim

Friday, January 31, 2014 - 8:35:17 AM - Filipe Back To Top (29294)

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.

Good article though.

Thursday, January 23, 2014 - 2:57:33 PM - norman chan Back To Top (28200)

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" 

Wednesday, January 22, 2014 - 1:02:15 PM - Krystian Back To Top (28179)

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

Tuesday, January 21, 2014 - 3:09:47 AM - Wilfred van Dijk Back To Top (28156)

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 

Friday, January 17, 2014 - 12:27:53 PM - Victor Shevchyk Back To Top (28122)

Good and  interesting article Norman. I shared with our DBA group.  I see you are doing well.

Friday, January 17, 2014 - 11:59:28 AM - Marios Philippopoulos Back To Top (28121)

Hi, thank you for the very interesting and useful info in this article.

Another way of speeding up maintenance procedures is through use of the Service Broker; I have found it useful for update-stats runs.

Pls see links below:

http://www.sqlservercentral.com/articles/Service+Broker/76715/

http://www.sqlservercentral.com/articles/Service+Broker/93393/

Friday, January 17, 2014 - 9:23:36 AM - John L Back To Top (28118)

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

Friday, January 17, 2014 - 8:39:50 AM - alzdba Back To Top (28117)

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.

 

Friday, January 17, 2014 - 8:33:52 AM - umar iqbal Back To Top (28116)

That was very good article and very informative. Thanks for sharing this information. 

Friday, January 17, 2014 - 7:59:46 AM - Darek Back To Top (28115)

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.

Friday, January 17, 2014 - 7:52:54 AM - Darek Back To Top (28113)

Very useful article, Norman. Clear, concise and to the point. Thanks for your time and effort.














get free sql tips
agree to terms