What is cost? Plus, how to set cost threshold at the instance level (4 minutes)



Thank you for joining me. This is Cost Threshold for Parallelism, that we are talking about today. I’m Kendra Little, from SQLWorkbooks.com.

What we’re going to talk about with cost threshold in this session, just what it first… Big picture, what is this concept of cost in SQL Server?

What does it mean? And when we talk about this threshold, we have a setting called Cost Threshold for Parallelism. Well, which cost is it looking at when it compares it to the threshold, because only costs, only queries with a specific kind of cost, get to go parallel. But it can be a little confusing as to how it works, and you may see some weird things. And there is a reason why.

We’ll also talk about forcing parallelism.

There may be times when I want to raise the bar, and set a high threshold for parallelism, so that cheap queries don’t go parallel. But what if I’ve got a problem query, that’s just costing really low, and I want it to go parallel, and I don’t have time immediately to do a bunch of tuning on it. Do I have to lower the threshold for the whole server? Well no, there are some ways you can get parallelism for queries under the threshold, but it’s kind of weird. And we’ll look at demos of all those ways. And then, finally, how do I go about tuning this setting on the instance level for a whole group of queries. We want to know what those tricks are about forcing parallelism, and what we can use in our version of SQL Server in order to make that easier.

First up, what is this whole cost thing that we’re talking about?

When we run a query in SQL Server, the optimizer looks at a bunch of different ways that it could run the query. So, for an example query, here is a picture of an execution plan that SQL Server could use.

This particular query plan just uses one core. It has a degree of parallelism of one and it’s chosen to do a hash match interjoin and a hash match aggregate. doing this with just one thread. What have to represent…well we don’t have to, but the SQL Server optimizer is a cost-base optimizer. So it looks at how many rows it thinks are going to flow through this thing, and how much work the different operators are. And it estimates out a cost using an arbitrary currency for doing work in SQL Server for this plan.

We could execute this query in different ways.

This plan is for the exact same query

This plan uses multiple cores, so to use multiple cores, we need to handle that and account for it in our cost. So this query has some extra operators in it. It’s still doing a hash match interjoin. It’s still doing a hash match partial aggregate, but it has to have some things about distribute streams and gather streams, because of where it has decided to place the parallel zones of the query.

This query has a different cost

This exact same query, this is another way it could be done. This query plan uses a different non-clustered index for part of the query. And that non-clustered index isn’t perfect for it, so if it was to use that non-clustered index in the bottom right of the plan, it has now a nested loop operator in there so say, I could find some information from that non-clustered index, but then I’d have to go look up some rows or columns that aren’t included in that non-clustered index. I’d have to go look them up in the clustered index of the table. And this query has a different cost as well. It also happens to be parallel. These are not the only three ways this query could be run but they’re all different, and they all have different costs that represent them. And the optimizer says, okay I want to execute the query that is going to be easiest. When we’re looking at cost in SQL Server, even if we are looking at an actual execution plan, cost is always an estimate in the plan. It isn’t that after we run the query that the query plan is not updated, with here’s an actual cost. Whenever we are looking at cost in query plans, whether they are cached in the procedure cache in SQL Server whether they are in query store in SQL Server, we are looking at estimated cost, even if we have run the query and asked for an actual plan. Okay well that’s what cost is.

What is this threshold?

This is a instance level setting in SQL Server called Cost Threshold for Parallelism. And let’s take a look at how we set this and what cost it’s looking at when it comes to these queries I can see the current value for Cost Threshold for Parallelism in the sys.configurations view, default value for this is 5. Now when you’re not used to looking at cost, it’s a foreign currency. You’re like, okay well how much is five dinares?

Well how much does five cost in SQL Server? The more that you work for this, or the more that you work with this, and the more you look at the cost of queries on your instances, you’ll get used to seeing, well this query has a cost of .007, it’s cheap or on this instance, we run a lot of larger queries. We have a lot of queries that have cost 500.

We can have a variety of different costs.

The bar, and this bar is for who gets considered to go parallel, not who is guaranteed to go parallel, the bar for this is at a cost of five. And it has been that way for a very long time in SQL Server and five, I would say, is a very cheap estimation. Cost is always an estimation.

We have a question: is cost threshold for parallelism available at the database level in SQL Server 2016 and higher?

We do not have the ability to set the threshold at the database level. We have the ability to set the DOP at the database level. Let’s go ahead and for fun, let’s look at this in the properties of the database. We can do it with T SQL, and I look to do it with T SQL, but now I’m like, let’s find this in the database options. What we have now in the database options is we have Max DOP, the degree of parallelism.

If somebody goes parallel, what is the degree of parallelism they get?

We can set that for the database and if there is a secondary we can set it for the secondary.

But notice that there’s nothing in here that is the threshold for parallelism for a database, not in that list.

Let’s take a look now at how to set it

If we did want to change this, this is an advanced value. This is considered advanced, and I think almost anything you care about is in advanced values. So we need to make sure that advanced options are available for you to set. You need to set show advanced options to 1.

To make this go live, you have to use reconfigure– so I’m going to say make sure to show advanced options as 1 and then I’m going to view items pending reconfiguration. I want to see everybody whose value is not the value in use. I already had advanced option in use. And I want to stay at our cost threshold for five, so I don’t need to run reconfigure.

Nobody is showing in this list as having a different configured value than a different running value. Running value is value in use, so I am fine. But if you did want to change this on the instance, you’d have to make sure that advanced options were enabled and then run cost threshold for parallelism with that cost that you want. And I’m going to go ahead and view this again just to show you what the maximum and minimum values area. We can see that in there, our maximum value we can set for this is 32767.

The minimum value is zero. If we did set it to zero, it wouldn’t mean that everyone would go parallel. It would mean we’re setting the bar incredibly low but we may still have queries that don’t qualify to go parallel. Let’s look at estimated cost in a query. I am going to use our sample database of baby names and look at a table here. I’m going to first just run an estimated plan. I did a ctrl+l there I also could have used this button up here. And if I just hover over the select operator, I get the tool tip that says my Estimated Subtree Cost for this query is 732.901

Well above that threshold of 5, am I right? And we are seeing parallelism operators parallel zones in this plan. I have a gather streams operator here, I’ve got these little double arrows, indicating that this query has not only qualified for parallelism, but a parallel plan was selected by the optimizer for this query. I’m going to turn on my actual execution plans, run the query, and then after it runs, we’re going to just look at the estimated cost and operator. And we’ll also see how long it takes.

That took about seven seconds to run this parallel plan. If I hover over the cost here, 732 is my cost, I can see other things like my memory grant operator.

Back to: Cost Threshold for Parallelism in SQL Server (1 hour 5 minutes) > Learn

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.