SQL Server Maintenance Plans and Parallelism – Index Rebuilds

In my previous post, SQL Server Maintenance Plans and Parallelism – CHECKDB, we looked at the degree of parallelism used when CHECKDB is run.  It ultimately depends on SQL Server Edition and the max degree of parallelism setting for the instance, which is not the case for index rebuilds (today’s topic, as you probably surmised!).

Index Rebuilds

The max degree of parallelism can be configured for index rebuilds using WITH (MAXDOP = n):

USE [AdventureWords2012];
GO

ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]
     REBUILD WITH (MAXDOP = 8);
GO

If this option is included, it overrides the max degree of parallelism value configured for the instance. For example, I can rebuild the IX_SalesOrderDetail_ProductID index on Sales.SalesOrderDetail with MAXDOP set to 8, even though MAXDOP is set to 4 for the instance.  If WITH (MAXDOP = n) is not specified for an ALTER INDEX … REBUILD statement, then SQL Server will use the MAXDOP value set for the instance.

Now, unfortunately, parallel index operations are only permitted in Enterprise Edition.  If you’re running Standard Edition, you’re stuck with single threaded rebuilds, just like you’re stuck with single threaded integrity checks.  Despite this sad news, I thought I’d run through a demo that shows the max degree of parallelism used during the index rebuild. I’m going to run ALTER INDEX REBUILD for a selected index in the AdventureWorks2012 database, and I’ll use Extended Events to capture each statement executed (sp_statement_completed event), and the actual query plan for the statement (query_post_execution_showplan event).

**Important note here again: it is NOT recommended to capture the query_post_execution_showplan event against a live, production system.  This event generates significant performance overhead, and you are warned of this when configuring the session via the GUI.  If you repeat any of the demos here, please make sure to execute them against a test environment.  It’s very important to me that you do not bring down your production environment.**

Here are the statements to create the event session, start it, run the ALTER INDEX … REBUILD statements, then stop the event session.  As in my previous post, I am using a file target to capture the output, and the path is C:\temp.  You may need to modify this path for your environment.  I still have max degree of parallelism set to 4 for my instance, but we’ll set it before we run anything just for good measure.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 4;
GO
RECONFIGURE WITH OVERRIDE;
GO

CREATE EVENT SESSION [CapturePlans] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
     ACTION(sqlserver.plan_handle,sqlserver.sql_text)),
ADD EVENT sqlserver.sp_statement_completed(
     ACTION(sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\temp\CapturePlans.xel'),
ADD TARGET package0.ring_buffer(SET max_memory=(102400))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
     MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

ALTER EVENT SESSION [CapturePlans]
     ON SERVER
     STATE=START;
GO

USE [AdventureWords2012];
GO

ALTER INDEX [IX_SalesOrderDetailEnlarged_ProductID] ON [Sales].[SalesOrderDetailEnlarged]
     REBUILD WITH (MAXDOP = 8);
GO

ALTER INDEX [IX_SalesOrderDetailEnlarged_ProductID] ON [Sales].[SalesOrderDetailEnlarged]
     REBUILD;
GO

ALTER EVENT SESSION [CapturePlans]
     ON SERVER
     STATE=STOP;
GO

Note that I used a different version of the SalesOrderDetail table named SalesOrderDetailEnlarged.  This table has over 4 million rows in it and was populated using Jonathan’s Create Enlarged AdventureWorks Table script to ensure I’d have a table large enough to warrant a parallel rebuild.  After I stopped the event session I opened the .xel file from C:\temp in Management Studio and added the sql_text column to the display so I could easily find the ALTER INDEX statements.

The screen shot below is from the ALTER INDEX statement with MAXDOP = 8 included.  The query_post_execution_showplan event is highlighted, you can see the sql_text, and I hovered over the showplan_xml to show the first part of the xml version of the plan.  Note the red box around QueryPlan DegreeofParallelism…it’s 8, as expected:

ALTER INDEX ... REBUILD WITH (MAXDOP=8)
ALTER INDEX … REBUILD WITH (MAXDOP = 8)

If you’re playing along at home in your test environment, you can click on the Query Plan to see the graphical view, or double-click the XML to view that plan that way.  Now check out the screen capture below, which is for the ALTER INDEX statement that did not include the MAXDOP option:

ALTER INDEX ... REBUILD (default option)
ALTER INDEX … REBUILD (default option)

The max degree of parallelism for the plan is 4 because if the MAXDOP option is not included, SQL Server uses the max degree of parallelism set for the instance.  Note that this holds true when parallelism is disabled for an instance (max degree of parallelism = 1):

sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

ALTER EVENT SESSION [CapturePlans]
 ON SERVER
 STATE=START;
GO

USE [AdventureWords2012];
GO

ALTER INDEX [IX_SalesOrderDetailEnlarged_ProductID] ON [Sales].[SalesOrderDetailEnlarged]
     REBUILD;
GO

ALTER EVENT SESSION [CapturePlans]
 ON SERVER
 STATE=STOP;
GO
ALTER INDEX ... REBUILD (default option) - MAXDOP=1 for instance
ALTER INDEX … REBUILD (default option) – MAXDOP = 1 for instance
The plan shows a DegreeOfParallelism of 0 – this means that the query did not use parallelism – and that the plan includes a NonParallelPlanReason* of “MaxDOPSetToOne”.  Therefore, if MAXDOP is set to 1 for an instance, and the default ALTER INDEX … REBUILD statements are used to rebuild indexes – where the MAXDOP option is not included – then rebuilds will be single-threaded.  For some well-known applications (e.g. SharePoint, SAP, BizTalk)  it is recommended to set the max degree of parallelism to 1 for the instance.  While that option may be appropriate for application-specific queries, it means that your index rebuild operations may run longer than if parallelism was enabled.  It may be worth modifying your index maintenance script to include the MAXDOP option for ALTER INDEX REBUILD statements.

In the event that you have a max degree of parallelism value above 1 specified for the instance, but you’re not sure what the “right” MAXDOP value should be for your index rebuilds, you can let SQL Server decide.  If you include the WITH (MAXDOP = 0) option in your rebuild syntax, then the optimizer will determine how many CPUs to use, which could be anywhere from 1 to all of the CPUs available to SQL Server.  This is the recommended setting per Books Online, but I would caution you to use this option only if you’re comfortable with SQL Server potentially using all CPUs for a rebuild.  If you happen to be running other tasks or processes in the database while the rebuilds run – not ideal, but for a 24×7 solution you often don’t have a choice – then you should specify a MAXDOP value below the total number of CPUs available.

Finally, in case you’re wondering about parallelism and reorganizing indexes…the WITH (MAXDOP = n) option is not available for ALTER INDEX REORGANIZE, as index reorganization is always a single-threaded operation.  The final post in this series will cover parallelism and the UPDATE STATISTICS command, and if you’re manually managing statistics and specifying the sample, you don’t want to miss it!

*If you’re interested, Joe talks about the NonParallelPlanReason attribute  in his post, SQL Server 2012 Execution Plan’s NonParallelPlanReason, which may be useful when you’re digging into execution plans in SQL Server 2012 and higher.

11 thoughts on “SQL Server Maintenance Plans and Parallelism – Index Rebuilds

  1. Excellent article, is there anyway i can check if my Update Statistics is using all the CPU’s in SQL Server 2008 Ent Edition.

    1. Amit, that’s an excellent question and answering that exact question has been in my queue for way too long now 🙂 I have been working to use Extended Events, in the same manner outlined in this post, to determine the DOP for UPDATE STATISTICS. So far, I’ve only been able to see DOP = 1…but from what I’ve heard from the SQL Server team, the update will go parallel. I am planning to increase database size and then re-test, and once I’ve done that I’ll write it up in a post. Thanks for the question, and for reading!

      Erin

      1. I’m in the process of writing a stored procedure to handle statistics for the company that I work for and I just went through the statistics question that is being asked.

        SQL Server will use MAXDOP while gathering statistics, but only when the “WITH FULLSCAN” option is invoked. Note: this behavior can also be invoked using “WITH SAMPLE 100 PERCENT” as well.

        If the sample percent is less than 100 or the option ‘WITH FULLSCAN’, the hint is not used and therefore statistics are not gathered in parallel and that may explain why you are only seeing DOP=1.

        Hope this helps.

  2. I’m curious. Wouldn’t rebuilding an index with MAXDOP=4 on a heavily utilized SQL Server result in the index being (again) fragmented after the rebuild?

    This could happen in the case where they’re a lot of statements waiting to be processed and the database engine assingning the subtasks of the index reorganize in portions to different queues (scheduler_id in the sys.dm_os_tasks view). In the end this would result in the index being fragmented again.

    1. John-

      To be clear, you’re asking if an index would be immediately fragmented after a rebuild, because of the activity that would start occurring AFTER the rebuild finished?

      If so, then yes, there would probably be some level of fragmentation – it’s the nature of indexes. It will depend on several factors (rate of inserts, updates, deletes, index key), but I wouldn’t expect an index to be IMMEDIATELY fragmented after a rebuild finishes.

      Please let me know if I’m not understanding your question.

      Thanks,

      Erin

  3. I’ve found a reference to my above statement in the MSDN blogs:

    SQL Server Books Online alludes to the fragmentation possibility but does not fully explain that the Online Index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives.

    So should we take into account that online index rebuilding bis not an option other than with MAXDOP=1 or OFFLINE?

    Thanks.

    1. Ok, reading serially, now I see what you mean.

      The link didn’t come through in your second comment, so just to include it:

      http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx

      The article talks about increased fragmentation when ALLOW_PAGE_LOCKS = OFF, and you see the fragmentation (after the rebuild) with it set to OFF. This is not the default, so my first question would be to ask why you’re using that option? What problem are your trying to solve?

      Based on the post from Bob Dorr, with ALLOW_PAGE_LOCKS=OFF, yes, you can have logical fragmentation after an ONLINE rebuild, unless you use MAXDOP=1 or run it offline.

      Otherwise, if ALLOW_PAGE_LOCKS=ON, then online index rebuilds are still an option.

      Erin

      Erin

      1. I remember seeing fragmentation issuss happening during a live demonstration while indexes were being recreated and the fragmentation was higher than before re-indexing. The speaker was not using any ALLOW_PAGE_LOCKS setting so I presume the default was set.

        Here is a reference I found on Microsoft’s pages: http://support.microsoft.com/kb/2292737

        Sorry for the slow reply. Just recently came across the article again, after having to reply to some SAP system managers regarding re-indexing and their reference to out out-of-date KB articles.

  4. Thank you for the article. Its very informative. This may sound like a trivial question. I had a situation where I added a new index to existing table when I was performance tuning. When I scripted the create of non clustered index script with SORT_IN_TEMPDB = ON and MAXDOP=4 option set. I wanted to verify if these settings are applied as these are optional options in create of index and was wondering if there is another other alternatives. I have also tried to re-script the create index but when I rescript or see it on GUI I never see these options are set. Also when I query indexes this to check properties on my index I don’t see those values.

    SELECT *
    FROM sys.indexes AS i
    INNER JOIN sys.data_spaces AS ds
    ON i.data_space_id = ds.data_space_id
    WHERE is_hypothetical = 0 AND i.index_id 0
    AND i.object_id = OBJECT_ID(‘dbo.MyTable’)

    Currently I just monitor the wait stats to verify these options are applied.

  5. Hi-
    The MAXDOP option isn’t something that “persists” for an index. You have to specify it specifically in the ALTER INDEX script. Otherwise it will automatically determine what MAXDOP should be, based on what is set for the instance or database (depending on your version) and the number of processors it decides to use.

    Erin

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.