Transcript

Another way to do this is to use a plan guide adding a hint

Now the first plan guide that we’re going to look at has a hint that is not supported.

So I want to show you just that you can do this, it’s not hard, I don’t think this is a good solution because this hint, that says “Note my preference to use a parallel plan.” It says “Option, use hint, parallel plan preference” and this is available.

The option use hints syntax is available in SQL Server 2016 Service Pack One and higher. If you’re not using 2016, you could use another undocumented trace flag, you could say “Option query trace on, trace flag 8649.” Using query trace on requires sys admin permissions, and trace flag 8649 isn’t supported either. Neither enable parallel plan preference or 8649 are documented and supported by Microsoft, and by adding them as a hint, we are putting them into our production code, which I don’t think is a great idea.

That being said, here’s how it works

We can say, like any hint in SQL Server, “I want to add, here is my hints syntax.” Right, so I could say “Option recompile” as a hint, this works for other hints as well. But in this case I’m saying “Option, use hint, enable parallel plan preference.” I’m putting that into a variable named plan hint, then I am creating a plan guide. My plan guide says, “I want you to look for this statement,” this matches the query in my sort procedure exactly, “I want you to look for this statement, you’re going to find that statement in an object, an object named “Total sales by region for year.”

When you see this, I want to add plan hint on to it, as a hint.” So I’m going to go ahead and execute this code and create plan guide goes out and says, “Okay, I am creating a plan guide, so that when I see this exact query text, in total sales by region for year, I am going to tack on this hint that you have.” Plan guides work a little differently than query store freezing, they do. They’re totally different technology. And when I create a plan guide for this stored procedure, SQL Server automatically, at the creation of the plan guide invalidates any plans that happen to be in cache for this.

So, if I look at my estimated plan, right away, I just did control + l for estimated plan, right away I can see, hey, this is a parallel plan here, and this is a parallel plan that’s getting an Estimated Subtree Cost of 79.4048. Notice there’s no index hint here, this is a version of the parallel plan that is a little different from the one that I got when I forced planID=3 in query store, because this time, I’m not forcing that same planID, I’m just using a hint that says, “Please ignore cost threshold for parallelism,” and it’s going down a different optimization path. I can also see, even in the properties of my estimated plan, if I click around and look at my properties here on the select operator, I can see ooooo, we have a plan guide at work and we even get the name of the plan guide.

When we use this type of plan guide, that tacks on the hint, the estimated plan, the cached plan and the query store plan, everybody, as well as the actual plan shows, hey, there is a plan guide being applied to this. We’re going to go ahead and run this. The plan guide adds on the hint that says “Hey, this hint … this undocumented hint is basically saying for me, ignore that cost threshold for parallelism setting,” and we get the plan with the Estimated Subtree Cost of 79.4 that is a parallel plan, our Query Time Stats, shows that our execution is around five seconds with around 13 seconds of CPU time.

So that is one way that we can get parallelism for this plan, but I don’t like it because we have tacked on an unsupported, undocumented hint.

As soon as I drop my plan guide there, I ran sp_controlplanguide with drop, with my name in there. As soon as I do that, that also, by dropping the plan guide it says, “Oh, this is for this object, Total sales by region for year.” It invalidates the plan in the cache, so just looking at estimated plan for this, I’m back to getting my single threaded plan for that automatically.

Another way to approach this with a plan guide - USE PLAN

Well, that’s not the only way to do it, and let’s do a way that we may have to use something unsupported but, I just don’t want to leave an unsupported trace flag or an unsupported hint in the solution. So, how can I do that?

Well, one way I can do that, this does require some high permissions, but one way I can do that is to generate a parallel plan, and then capture that plan and tack it on and hint the plan itself. That means the plan itself doesn’t necessarily have to have that trace flag embedded in it, and here’s one way that I can do that.

This is an undocumented trace flag, but I am enabling it for my session. I’m not enabling it globally for the SQL Server instance, I’m enabling it for my session. I’m using DBCC TRACEON with 8649 and then zero. When I look at DBCC TRACESTATUS, it says, “Okay, yup that is on, just for your session.” Now what imma do, is imma run our query with actual execution plans on and I’m going to get the plan into cache on my SQL Serve instance. I’m running this with actual plans on, just so I can see it, just executing it puts it into the query plan cache, but I want to look at my query plan and confirm, this is the version of the plan that has an Estimated Subtree Cost of 79.4048 and note that it does not have that missing index request hanging out in there at all.

So, okay, this is also in cache now. I can even turn off the trace flag for my session and that doesn’t remove it from cache or anything, I’ve just said “Okay, I want to turn of the trace flag for my session, I don’t need that to be on anymore, now that I’ve gotten the parallel plan for this in cache.”

What imma do right now is create a different plan guide.

Our query here declares plan as a variable that’s XML, and we are going to set plan to the query plan, which is currently in cache for this stored procedure because I actually want to freeze the plan for the whole stored procedure, which actually is just one statement in this case.

I’m querying my dynamic management views, setting plan to the execution plan itself, the cached plan itself, and then I’m creating another variable called plan hint.

Plan hint is something we’re going to tack on at the end of our query and it doesn’t use an undocumented hint. It uses a documented hint, which is just a little fancy, called Option Use Plan. What imma do is imma plug in the sentence and say option use plan and then, plug in the execution plan itself but we do have to replace single quotation marks with double quotation marks, just because our execution plan contains some quotation mark that if we don’t escape them out we won’t get valid syntax. Isn’t it fun, getting to convert things to get them to work here?

We now have a similar plan guide to last time, the difference is that, instead of our plan hint tacking on an undocumented statement, our hint is saying, “Okay, I’m hinting an execution plan.” Yes, that execution plan was generated because I had an undocumented trace flag enabled on my session, but this is, one step closer to being supported. Right, I’m still doing something that’s a little fishy here, but it’s one step closer to being supported. I create my plan guide here, so I pop the execution plan into my variable, I replace some of the characters to make it a valid, unicode string and then, I pop that use plan hint into my plan guide. Now, when I look at my estimated plan for my execution plan, hey, this is interesting, right away, because the plan guide was created for the procedure, it invalided a plan that was in cache, because plan guides do that, query store does not, when I look at my properties for my stored procedure, and I click on the select operator, I can, right away, say “Oh yeah, you’ve got a plan guide that’s being created for this, that says oh, you need to add a use plan hint for this.”

But there’s something really interesting here, do you see that I do have a missing index request again?

Just like with Query Store, I also have a different Estimated Subtree Costs, it’s 139.8.

When this plan guide put a use plant hint … use plan, not use plant, use plan hint on there, just like query store, we are now going down an optimization path that does allow for some tailoring and SQL Server does have the ability to say, “Oh, actually, you know what, I think the cost for this is a little bit higher than the exact use plan hint you were using.” And so it has customized it for me a little bit, even though I am using use plan, instead of the query store force plan, they are both allowed to be a little bit flexible there.

Imma go ahead and execute this, not just looking at the estimated plan, but let’s execute this and get the actual plan, and we do get on our Query Time Stats, we can confirm, yeah, we are once again parallel plan, although we are getting some variation in these parallel plans, we have run times within a very similar range of one another. We’ve got five seconds of elapsed time, 15 seconds of CPU time, we do have that index suggestion and we do have that Estimated Subtree Cost of 139.8. We can also tell of course what plan guide was applied, and use plan equals true on this. This was told, “You need to use a plan.”

Lets go ahead and drop that plan guide. I think there is a simpler way to do this, rather than to use option (use plan…). but option (use plan… ) has been around with us for a very long time, so it is useful to know about.