Transcript

Welcome to the SQLChallenge on forcing parallelism. I’m Kendra Little from SQLWorkbooks.com. Your challenge today is to force a query to go parallel, but you can’t do just anything to force parallelism.

In our scenario, there’s a lockdown on making certain changes and you can’t change the query text that’s executing, and you also can’t change the instance level setting for cost threshold for parallelism. You need to swoop in and intervene in a more creative way.

To test this on your own system…

You’re going to need a SQL server that has more than one CPU. You’ll see me doing this in a virtual environment with four virtual CPUs, and we’re using the ContosoRetailDW sample database. This is a free sample database from Microsoft. I am testing this from SQL Server 2017, but the ContosoRetailDW database can be restored to older instances of SQL Server. There’s a couple of different ways to solve this problem and with more recent versions of SQL Server, you have more options. So I recommend using the free developer edition of SQL Server 2017 so that you can play around with different ways to solve this, some of which will only work on SQL Server 2016 and higher.

So you can do lower versions, you just won’t have all the options or solutions available to you.

First up, I’m going to walk you through the problem script and set you up and show you what the problem is

After you watch the problem, I want you to reproduce it on your own and then try your hand at different solutions. The script for the problem has as the top of the URL where you can download ContosoRetailDW from Microsoft. The very first thing we’re going to do at the beginning of the challenge is restore the database to our instance. Depending on how you have your laptop or whatever your test instances laid out, you may need to change some of the file locations. I have an S drive setup where I put all my SQL Server files. Restoring this just took six seconds on my instance.

The next step I’m going to do is raise the compatibility level for the database to 140. At the beginning of our problem, our test instance has cost threshold for parallelism set to the value of 50. The default value for this on SQL Server is five which is really really low. So imagine that to start out we have raised this a bit. We have max degree of parallelism set to four so that if a query qualifies to go parallel and everything works out, it gets a parallel plan, it can use all four of the cores on my laptop.

Next I’m going to setup Query Store. Query Store is configured on the database itself. So notice that this is an alter database command. Now the values I’m using here to setup Query Store may not be the right values for every single Query Store. For instance, you might want to keep more than 30 days worth of queries and you might want to allocate more than two gigs to it. But this is just a sample setup for a small demo environment. It’s going to take us less than 30 days to do this. So I’ve setup Query Store so that as I run queries against the ContosoRetailDW database, they will be captured. I have actually said by the way that query capture mode is all. The default setting of auto which may ignore some plans is often best, right, so I just wanted to make sure for the purposes of demo, I said go ahead and query store capture everything.

Our problem query uses a view…

…and I’m going to create the view in ContosoRetailDW. The view is created and if you look at the name of this view and say oh you really shouldn’t use the naming convention V underscore for views, I understand, I’m not crazy about that either. I’m being consistent with the naming convention already in use in this database, not my favorite naming convention either. Now this view does return customer data. If we look at the definition of the view, we’re joining between quite a few tables, from FactOnlineSales all the way down to DimGeography.

And we’re returning some of the information we have about the customers of Contoso.

We have a stored procedure that references this view

Our stored procedure’s named total sales by region for a year and we pull data from the view for a given calendar year and then we group by the region and the year. We order by total sales descending. Here’s the way the procedure runs before the problem happens. Right now our cost threshold for parallelism setting is at 50. And I do have actual execution plans enabled with this button up here. If I run this, the first time it runs, it goes out, grabs that customer data and aggregates it. And this takes about five seconds to run.

You’ll notice that we have some interesting data for our customers, for Contoso by the way, the minimum yearly income figures are rather high here, so we have quite the customer base in some places. Our sample data is a little bit interesting in Contoso. But looking at our plan, we do get a parallel plan. With cost threshold set to 50, we have a parallel plan for this query. If we hover over the select here and we look at the estimated subtree cost, the estimated subtree cost for this particular parallel plan is 79.3876.

And one thing to notice, we’ll compare this and see something different later on, is this particular parallel plan there is not a green query hint anywhere to be seen. SQL Server isn’t for this plan making any suggestions about indexes that it thinks that we should change.

Alright, so for this parallel plan, we’re getting a run time of around five seconds. Well let’s say this is the normal run time for our customers. This is what our customers are used to, the query’s pretty consistent with this run time until a change comes along.

Let’s say this instance has other things going on and the lead DBA decides to make a change and raise the cost threshold for parallelism

The bar is being raised on the instance as to who may qualify to be considered for a parallel plan. This change goes into place. Reconfigure makes the change take effect. And when the cost threshold for parallelism is changed, SQL Server says ooh, oh, okay, my plans have been invalidated. The bar for who gets considered for parallelism has changed so I need to look and compile fresh plans when query’s run. If we look at our query right now, I’m just going to do estimated plan for this with control l.

Right away with an estimated plan, I see this no longer has all those double arrows on it. We don’t have repartition streams. We don’t have any parallelism operators. Our query is now going to be single threaded. We have a different subtree cost as well. The estimated subtree cost for the plan we got, the single threaded plan we got is 152. It is below that 200 so SQL Server says oh well, to run your query with one thread is below this threshold which is now at 200 so you don’t qualify for parallelism. Executing this query, this query no longer takes just five seconds to run.

With only one thread to run this on, this query takes 10 seconds plus to run. This time it took 10 seconds to run. If I look at the properties here for the plan, I right clicked and selected properties hidden behind the tool tip, looking at my properties here, I can see in my query timestamps that sure enough my elapsed time now is 10 seconds. This is taking twice as long to run with only one core.

And it is in this case saying for this plan, hey I would like some index help.

My estimated subtree cost once again for this single threaded plan is 152.

Your SQLChallenge is…

Can you get the parallel plan back for this stored procedure without changing that instance wide cost threshold for parallelism setting, and without changing the stored procedure definition?

There’s more than one way to do this. I’m going to show you in the next video, I’m going to show you four different potential solutions with their pros and cons, but you don’t have to come up with four.

Come up with one, work at the problem, try it out and then meet me in the next video for the solution.