Transcript

Let’s talk through different solutions for this SQLChallenge. There are a few!

Once again, I’m in our ContosoRetailDW database, here, and I think that the easiest solution, if you’re on SQL Server 2016 or higher, is using that Query Store to freeze the parallel plan.

Query Store, as long as it has that parallel plan, still recorded in the Query Store, as long as it hasn’t been aged out or cleaned up by some mechanism, we can tell SQL Server, “Hey, I like that plan.”

The first way we’re going to look at this is looking at the dynamic management views

This happens to be a stored procedure, so I can just join from sys.query_store_query to sys.objects, and on sys.objects, I can say, “I want to see the information “for ‘TotalSalesByRegionForYear’.” I’m also joining to the Query Store plan view, so I can see the query plans as well as information on the query text in there. Query Store says I’ve got a couple of plans for this. I can verify the TSQL by clicking on the TSQL here, I can see, this is the query inside my stored procedure. It has a bit at the beginning, to tell me, okay, the @CalendarYear parameter is an integer data type, but this is my query that sits inside that stored procedure. Query id 3. Both of these are query id 3. Both rows are for this same exact query text, but they are for different query plans. I have one query plan with plan id 3, and I have one query plan with plan id 4.

I can see that these are from the same database compatibility level. When these plans were created and captured by Query Store, my database was using database compatibility level 140 the whole time, and I think that’s a really cool feature, that we can see things like that. Like the engine version, and the compat. level, so that if we are doing things, like we raise our compat. level, and we notice that regression in performance, we can tell which plan is form which compat. level. Right now, neither of these are forced.

If I look at my top line here, for plan id 3, I can see that plan id 3 is the parallel plan. It is the plan that does not have an index request, and it has an estimated subtree cost of 79.3876. That’s plan id 3. Plan id 4, the second line here, well, that’s got to be my single-threaded plan, right?

Sure enough, this is the single-threaded plan, who does have an index request

Well, I want to say, “I want plan id 3. “I want that parallel plan, even though cost threshold “has been raised.” And I can do that, by saying, “Okay, Query Store, “I want to force a plan. “For query id 3, hey, that’s our query, “I want you to use plan id 3. “Plug those in together.” And Query Store now says, “Okay, when I see the query, “in this query text, “I’m going to go ahead and force this plan.” Now, if I just look at my estimated plan, now, I can do control + L, or I can hit this button here. If I just look at estimated plan right after I forced it, it looks like it didn’t work, because I see here the single-threaded plan, hey, no parallelism operators here, and I, you know, have the missing index request.

When I look at estimated plan, in this case, for something forced with Query Store, it just is looking in memory

Right after I force the plan in Query Store, nobody has run the query yet, my procedure cache still has that single-threaded plan in there. Once I actually execute this query, I have actual execution plans on, and I’m going to go ahead and execute it, as part of the process for this execution, SQL Server figures out, “Oh! You have forced a plan “in Query Store,” and it then goes down a different optimization path. This query took just five seconds to run. Looking at my properties pane, here, and clicking around, so it refreshes, if I open my QueryTimeStats, here, yes, we are not at 10 seconds execution, which is what we get for the single-threaded run. We are at five seconds of execution time, and then 15, almost 16 seconds of CPU time, since we’re using multiple threads.

Our estimated subtree cost, there are things that are different here, and this is interesting. Our estimated subtree cost, we can see this in the properties pan or in the tool tip, same thing, is 139.78, and you may have noticed, hey, there is an index request up here. SQL Server before, on the parallel plan, it wasn’t making any suggestions about indexes, it was only doing that on the single-threaded plan. Now, it’s actually for the parallel plan, it’s actually said, “Hey, potentially, I can reduce the estimated cost of this query by, like almost 76%, if you change around some of your indexes. We have gone down a slightly different optimization path, after forcing this query, and we can see this in Query Store, too.

I’m running this same query again, looking at the Query Store dynamic management views, looking at Query Store query, Query Store plan, for our stored procedure.

Before, we only had two query plans for this, but now, we have three

So things get a little interesting when we look at the details. We have our first line, so for query id 3, our first line, we froze plan id 3 for that, and it says, “Yep, this is the forced plan,” right? That’s just as we expect. We still have plan id 4; that’s the single-threaded plan, but now we have a new line, here. We have a line for plan id 6. Plan id 6 is a special plan. I’m going to go ahead and highlight that line, and we’re going to go over to the right. We’re going to open it up. This is the Query Store version of the actual execution plan that we just looked at. The one who has the missing index request. And this is the one, this is not the exact same as the forced plan, but it was forced by the forced plan. If I look into the properties of this plan, and we’re going to look at something slightly different now, notice that use plan is true for this. This was also in that actual execution plan. This plan is called a Morally Equivalent Plan. This is the term that’s being kicked around for this.

We have a plan that is forced, but when we have that plan that’s forced, and we go to execute it, SQL Server has the option, on that optimization path, to say, ah, do I want to use this identical plan, or do I want to change some very minor details around a little bit? Maybe I want to just do something slightly different, but use the same general plan shape and general plan characteristics. This does have a different cost, and it even has an index suggestion for it. We can also see these things, not just by querying the DMVs, if we want to use the Query Store reports, we can see them there as well. So I’m going to open up ContosaRetailDW, and I’m going to go down, and I’m going to look in the Query Store section of this. There is a report on the queries with forced plans. Let’s open up Queries With Forced Plans, here. I only have one query whose plan has been forced in Query Store. I can see over here that it does see all three queries for this. I have some overlap in my bubbles down here.

Right now, plan id 3 is highlighted and that’s the check-marked plan. That is the one who says is force equals one. The plan who is technically the forced plan, that parallel plan, is the plan with the estimated subtree cost of 79.3876. Plan id 6, just like we saw, when we queried the DMVs– this is pulling from those same DMVs that we queried– it has the same plan shape, but it does have a different cost, it is asking for that index, and it has the higher cost of 139.783, and now that I’ve highlighted over here, it put that bubble on top. We are sorting by average duration, here, and the average duration of plan id 3 and plan id 6 is just about the same, so they are showing up in the same way there. And notice that they are, like if we switch back and forth, they’re very, very similar.

There are some differences between the plans

On plan id 3, notice there is a Bitmap Create here, just to the right of this Hash Match, we can see that Bitmap Create operator. On plan id 6, we don’t see that Bitmap Create operator being separated out there in the same way. Instead we see a Repartition Streams operator there, so if you dig in, you’ll see, the plan is largely the same, but there are minor details where, in the optimization process, it decided to do things differently. And this is particularly interesting because, these procedures were compiled for the same parameter values. If we look at, and confirm, for this query plan id 6, if we look at the parameter list it was compiled for, it was compiled for 2007. If we go back to plan id 3, and we look at its parameter list, yes, it was also compiled for 2007.

Even if we are compiling this for the same parameter value, after we force the plan, we are going down a different optimization path, and we may see a slightly different plan.

This, I think, is a good thing. I think this is a benefit, but it’s just good to know about, so that you don’t get surprised when you force a plan, and say, “hey, what the heck “is that new plan there, and why isn’t it “exactly the same?” It isn’t exactly the same, because SQL Server has this ability. All right, so we can force that plan with Query Store, and, by the way, now that I have forced the plan with Query Store, and run it– I’ve run it and I’ve gotten it in a cache– now, I did control + L to look at estimated plan, now when I look at estimated plan, it says, “oh, yeah, the plan I have in cache “is this parallel plan,” which this is, if I look at the properties of this guy, pull up our properties plane here. This is the use plan equals true plan, and that’s how I can tell, “hey, you didn’t just “come up with this plan on your own.” Use plan is true on this plan. Query Store is a great way to do this. It’s very convenient, but there are other ways to do this, which is good, because maybe I’m not running SQL Server 2016 on everything yet.

So, to explore those other methods, I’m going to un-force the plan from Query Store, and now we’re going to, once again, look at the plans in Query Store and confirm, okay, nobody is forced for this, we have cleaned up before we go on and explore other ways to do this.