Changing MaxDop For Resumable Index Create Operations

I’ll admit it, sometimes I’m wrong.

Recently I blogged about the usefulness of a newer feature that was enhanced with the release of SQL Server 2019. Resumable Indexes operations was introduced with SQL Server 2017 however it was limited to only REBUILD operations. With the release of SQL Server 2019, Microsoft has extended that ability to CREATE operations now. Two features that accompany these new abilities, which I consider very versatile, is the ability to specify a maximum degree of parallelism (MAXDOP) or a maximum duration. Both provide database administrators more granular control over their index operations and how much impact they will affect to their production environment.

In my previous blog, I discussed how you could use the ability to resume an index create operation with a different specified MAXDOP value. However, in the course of observing additional behavior related to the new system view, sys.resumable_index_operations along with a discussion with Microsoft, it turns out that it doesn’t behave the way I originally thought. According to Microsoft, the command will accept the syntax of a higher MAXDOP value but internally it is reverted to the originally specified value. Therefore, my previous blog post was slightly incorrect. While you can adjust the MAXDOP value during a REBUILD operation, you cannot do the same with a resumable CREATE operation.

Let’s see it in action, but first let’s talk a little bit about my test environment to make sure everyone gets on the same page

In both examples, I’ll be using the AdventureWorksDWCTP3 sample database along with the dbo.FactResellerSalesXL_PageCompressed table. This has approximately 11.6 million rows in it.

Also, since my laptop has 8 cores available to it, I will be limiting my test SQL Server instance to only 2 cores by using processor affinity. I will also use the % Processor Time metric in Performance Monitor to view CPU utilization.

Here is what my processor affinity looks like:

WARNING – DO NOT DO USE PROCESSOR AFINITY IN PRODUCTION!! MODIFYING PROCESSOR AFFINITY CAN CAUSE SERIOUS ISSUES!!

I have also limited the cost threshold for parallelism to 0 and the maximum degree of parallelism. I did this because I wanted to ensure that the CREATE operation would go parallel if allowed to do so and to limit it to only two cores.

Alright now let’s get started.

Increasing MAXDOP

In this example, first, I’ll create a new non-clustered index by specifying ONLINE, RESUMABLE, and a MAXDOP of two. With the given configuration of process affinity, a very low-cost threshold of parallelism, and an appropriate maximum degree of parallelism, SQL Server will utilize two cores to build this index.

Here is the synax:

CREATE NONCLUSTERED INDEX ix_factresellersalesXL_pagecompressed_OrderDate
ON dbo.FactResellerSalesXL_PageCompressed (OrderDate)
WITH (ONLINE=ON,RESUMABLE=ON,MAXDOP=2)
GO

We can see in the above video that SQL Server does indeed take the two cores available to it in order to build the index. Once the CREATE operation has completed, we can see CPU utilization drop to within normal limits.

Now, I’ll repeat that process, however, this time I will specify a MAXDOP of one. In the middle of the CREATE operation I will pause it. Once the process is paused, I’ll verify that it is in a resumable state and then attempt to restart it with a higher MAXDOP value of two.

CREATE NONCLUSTERED INDEX ix_factresellersalesXL_pagecompressed_OrderDate
ON dbo.FactResellerSalesXL_PageCompressed (OrderDate)
WITH (ONLINE=ON,RESUMABLE=ON,MAXDOP=1)
GO

During this process we can see that only a single core is used even though we’ve specified to resume the operation with a maxdop of two.

Decreasing MAXDOP

If you attempt to lower the MAXDOP value when you resume the operation, it will throw an error:

Msg 8622, Level 16, State 1, Line 15

Query processor could not produce a query plan because of the hints defined in this query.

Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Since it’s a CREATE operation, it must reuse the original execution plan so therefore it cannot adjust the MAXDOP value in either case.

Summary

While resumable index operations are still a powerful addition to the database administrator arsenal of tools to help maintain their environments, this is a gotcha that might sneak up on you if you aren’t aware. If you were planning on utilizing this feature to utilize off hours to speed up the creation of new indexes, you might need to adjust accordingly. Remember that index rebuilds do not show this behavior so you can adjust as needed!

Make sure to subscribe below so that you don’t miss out on future posts!