How to configure maxdop for the instance and see the threads in execution plans (10 minutes)

Free Preview

Transcript

Welcome to Max Degree of Confusion: Configuring Max Degree of Parallelism, otherwise known as MAXDOP in SQL Server. I’m Kendra Little from SQLWorkbooks.com. MAXDOP is one of the more confusing things to set on a SQL Server and it is very much a balancing act.

There is no single right setting for this, there is no “just set it to x” that is perfect on every SQL Server instance.

What we’re going to talk about today is the different ways that you can configure the max degree of parallelism…

And there are a lot of different ways you can set this. And you can set it at different levels on the same SQL Server instance! The first task is just figuring out: what are all the different ways I can configure, it and what do I like and what do I not like about those different ways?

When I change this setting, what does it look like in a query execution plan? How do I see how many threads and how many CPUs are being used in these plans?

Let’s dive into a demo and look at MAXDOP and execution plans.

I’m in SQL Server 2017, and I have restored a database and made some changes to it. The changes essentially gave me a table that has a bunch of rows in it so I can get parallel queries on it.

The main way that we configure MAXDOP is at the instance level

The default that SQL Server currently ships with saying max degree of parallelism is set to zero

So we can use all of the available logical processors up to 64. I don’t have 64 CPUs on my little VM here, SQL Server is smart enough to know how many cores I have, and I have MAXDOP of zero, it’s not going to be like “okay I’ll try to use 64 cores on your query.” It looks at “What do you have available, and I’m going to default to using all of them up to 64.” That can be really slow if you have a lot of cores on there, because like I said, using all the cores is not necessarily faster. Or rather, using all the logical processors is not necessarily faster. Logical processors are what it thinks about here. That includes hyper-threaded logical processors. I’m going to check what my configuration is right now in sys.configurations. I can see the value configured as well as the value_in_use.

When we change this configuration, we have to run a reconfigure statement to say “Okay, use this value.” Max degree of parallelism is considered an advanced value, but this is a value that I think should be changed from the default because the default of zero is just not good on every SQL Server instance. So we all should be advanced, we are all advanced. So right now I’m using all four cores on my little VM, I can change this if I want. Since it is an advanced option, I need to make sure that I have advanced options enabled. And I want to go ahead and configure the max degree of parallelism to two. It says: OK, I changed this, this is now the value, but it’s not the value in use. I need to run the reconfigure statement to make it happen, to make it active. I’m going to look at, if I ever want to see do I have pending changes, I can do that easily by looking at sys.configurations and say: who has a value that isn’t the same as the value in use?

This is the only thing I have that’s pending configuration and once I run reconfigure, my value_in_use will go to two. I’m going to go ahead and reconfigure that, and now I’m going to go ahead and take a look at a query that uses parallelism.

I’m going to turn on my Actual Execution Plan with this little button up here so that I can see what is does the parallelism look like in this query. And just to remind you, I am currently at, if we run our query and we’re currently at max degree of parallelism equals two.

How does ‘maxdop 2’ look in the actual execution plan, and what can we see in it?

My query, running at MAXDOP two, this time it takes about seven seconds. I’ve just set up this database and it had to read things from disk. That’s a little bit of a long runtime, I’m going to run it again. We are going to be looking at some query time stats, there in the query, so I want to give it a fair chance where it doesn’t necessarily have to bring the data in. And it did run about seven seconds that time too against the warm cache.

Looking at my Select operator here, my left-most operator on the query, if I go into the Properties of the Select operator, and I have to click around so that it actually is showing me the Select properties, there are different things I can see in here about the degree of parallelism.

There is down here a little thread stat piece of information. I can see information on the number of Branches in the execution plan, the number of Reserved Threads, and the number of Used Threads. I am running at MAXDOP two so my query is only going to use two of my logical processors, but I may have more threads used than logical processors. These threads will be sharing those CPUs.

When it ran this query, it said “We’re at MAXDOP two, I’m going to reserve four threads.” But it didn’t end up needing to use all of them.

SQL Server is able to reuse some of those threads if they finish up their work in one part of the plan and then it needs them in another. So we won’t always see in our actual plan, we won’t always see all of them being used, but it reserved four threads for this query running at MAXDOP two. Looking at our total Query Time Stats running at MAXDOP two, our Elapsed Time was just over seven seconds and we used almost 11 seconds of CPU time. And notice that on the Select operator, on the Root operator here, I can’t expand this out. On other operators in the plan, I am going to be able to expand this out. So I’m going to go over and look at, let’s look at a Parallel operator.

Operators that use parallelism are indicated by these double arrows here

This is an operator that went parallel. This is a Clustered Index Scan operator and if we look at the width of this line, SQL Server thought “Hey, I’m going to have to pull a lot “of rows out of this operator.” Looking at this Clustered Index Scan and this table, First Name by Birth Date is one of our larger tables.

I’m going to expand the actual Time Stats, and I can see the CPU time for the threads that did the work on this. Two threads, I’m at MAXDOP two, so this operator had two threads who could go do the work and they were sort of even in the amount of CPU time they did. One of them took a little longer than the other.

Very cool that I can see, “Okay, my query took about seven seconds, “most of the time was spent doing this.” This one thread took more than six seconds, almost six and a half seconds of CPU time. This is our Elapsed CPU time, I can also look, of course, at our Elapsed time, which is a little bit higher, right? There’s some overhead here, it has to yield periodically, not all of the time was able to be spent doing work. So the Elapsed time on thread one in here was just over seven seconds and thread two here actually finished up notably faster, and that’s really cool. Most of tuning this and saying “Do I have to do this clustered index scan?” is clearly the first stop for me if I wanted to make this query faster.

We can see for different operators if I go up here in my top branch, we can see whether or not they’re parallel. It doesn’t have to make the entire plan parallel when the plan goes parallel. This Clustered Index Scan on the ref.FirstName table it knows this is a small table. So there’s no parallel operators here, it just went ahead and said “Oh, I can use a single thread for that.”

It then has a parallelism operator because it is going to be joining the information to, it has to have this interaction with the query with parallelism so it says: Okay, I can do a single thread here, I will then distribute the streams and I can happily carry on. Later on I gather my streams together before I return information back.

SQL Server is able to decide what parts of the plan it wants to be parallel. It doesn’t have to make every operator in the plan parallel. Well, that’s really cool. Let’s go back to MAXDOP four.

Our biggest way to set max degree of parallelism for the instance is to set that instance setting using sp_configure. So I’ve gone back to max degree of parallelism four, and I have reconfigured to make it take effect.

Back to: How to Configure Max Degree of Parallelism: MAXDOP! (49 minutes) > Learn