Transcript

Another way we can do this is to use a slightly different kind of plan guide

This one can look kind of complex, but I think it actually is one of the easier plan guide types to use if you don’t have the option to use Query Store, simply because we don’t have to worry about escaping out those quotation marks in the text or hinting out an entire execution plan and converting the types. With this type of plan guide, SQL Server’s going to take care of a lot of that data conversion for us.

Once again, I am going to use the unsupported trace flag 8649 for my session so that I can get a parallel plan into cache.

Looking at DBCC TRACESTATUS for my plan, I can see that it is enabled. If I look at my estimated plan here, right now, I am seeing a parallel plan for this. It is going to look at the one in cache that is available.

I might in some cases have to say, “I want to use sp_recompile so that on the next run, I get a fresh execution plan.” Let’s go ahead and un-comment that and run that. When we do run sp_recompile against a store procedure or a table, it can take out a high level of lock against that table while it’s running. Usually, this isn’t a big deal if you’re running against a store procedure like we are now.

If you do run sp_recompile against a table to say, “Any query that uses this table, I want to get a fresh plan,” be very careful, and make sure that you aren’t causing blocking if that is in a production environment.

So now if I look at my estimated plan here, I am getting an estimated plan with a cost of 79.4. I am getting parallel plan because I have the undocumented trace flag on for my session, that it says, effectively, “Please, for my session, ignore cost threshold for parallelism setting on the instance. And if you can give me a parallel plan, please do so.”

I’m going to go ahead and run this and get the plan into cache. Once the plan is in cache, it should take about five seconds to do so. It took about four that time. Once the plan is in cache, I no longer have to have the trace flag on.

As long as that plan stays in cache, I will be able to freeze it, so I’ve turned the trace flag off for my session. This time, I’m not going to be adding a hint to the query. This time, I’m using a different type of plan guide.

We have a type of plan guide for plan freezing

You create these by saying sp_create_plan_guide_from_handle, and we give a plan handle for, in the dynamic management use, here is the handle for this execution plan, the unique handle for it. And then we also have the option to give a statement_start_offset. For my particular procedure, I’ve just got one query in there. I don’t have to worry about the offset. In some situations, if you’ve got multiple statements, you may want to find the offset for that particular plan handle to say, “I just want to freeze this individual query in here.”

What I’m doing, what my query does here is I declare handle and offset, and then I’m looking at sys.dm_exec_procedure_stats and friends to say, “For the procedure TotalSalesByRegionForYear, I want to get the unique plan handle for this.” I then pop that into sp_create_plan_guide_from_handle, and I’m naming my plan guide FreezeParallelPlan. Once I go ahead and execute this, if I can highlight it all, SQL Server has gone out and looked in my execution plan cache and it said, “For this plan handle, I’m going to freeze this plan.”

Notice that I haven’t mentioned the query text in here at all. In the cache, it has the associated query text that it’s going to recognize for that handle. When it sees that query text, it will freeze that plan.

If I go ahead and look at my estimated plan now that I’ve created a plan guide, it invalidated, it knows it’s for this procedure, it has invalidated anything that was in cache other than this. This plan was already in cache. I have frozen it, but creating the plan guide does, for other types of plan guides, it invalidates the cache. For this type of plan guide, that makes no sense because I have actually frozen the plan in cache with a ray gun. I have frozen it. And now I see a plan that has an estimated subtree cost of 139.8.

And yes, just like with Query Store, I am seeing … Now that I’ve frozen a plan, we saw this very similar behavior with Query Store and with the USE PLAN hint. When following this optimization path, it actually is able to adjust the costs a little bit, and in this case say, “Hey, you could improve your indexing as well.”

If I execute the query with the plan guide freezing the plan in place, my actual execution plan, I can see that my QueryTimeStats … Let’s go ahead and click around to see our QueryTimeStats here. Our QueryTimeStats are around five seconds of execution time, or around 16 seconds of CPU time. And for this type of plan that we have frozen from the query handle, we can once again see, in the properties of the plan, we can see the plan guide name, plan guide database, and Use plan equals True in those properties.

You might wonder, if I’ve frozen plans with plan guides, do those show up in Query Store?

I have not refreshed this Query Store window. I’ve had this open since when I had a plan frozen in Query Store. Right now, if I refresh the window, the reporting Query Store that says Queries with forced plans, that is only referring to, at this point at least, plans who have been frozen using Query Store.

My plan guide, freezing somebody doesn’t currently qualify to show up in this report. Of course, Query Store is still working, and it is still seeing everything that I’ve been doing. So if I just go into another report such as Top Resource Consuming Queries, this query, if it happens to be a top resource consuming query, will show up in there.

I’m going to go ahead and click this button to change this top-left quadrant to the table view, which I prefer. I like this because it makes it real easy to identify, “Oh, here’s the store procedure I’m looking for.” It is one of the top 25 resource consumers, so it shows up in this report. I can see now that I have had five different plans for this seen by Query Store.

We’re currently on Plan ID 4 is the one who is selected. Let’s look over here at the right. I want to click this one whose Plan ID 14 is who we’re looking at now. Who’s Plan ID 14? Will we be able to tell? It’s a parallel plan who has an index hint on it. I’m going to right-click on the Select operator here and view the Properties pane. Plan ID 14 happens to be, we can see Use plan is True, and this is the plan who had Add a USE PLAN hint. I had captured the execution plan in the cache, put it into a variable, and added it on with a USE PLAN hint. That one had a cost of 139.8.

This purple one here, who’s Plan ID 12, Plan ID 12, if we click around on Plan ID 12, Plan ID 12 had Add USE HINT ENABLE_PARALLEL_PLAN_PREFERENCE. This had a different cost. It didn’t have the query hint there. Here is the yellow one here. Who’s this one? This one, if I click around here, it has Use plan equals True, but it has no plan guide name on it. This is the one that I did force with Query Store. It says not forced here. I got a morally equivalent plan for it.

Since I know what I did, I can make sense of all these things. This had been forced with Query Store there. Here is my single-threaded plan, and here, if I click way back here on Plan ID 3, here is my original parallel plan that I originally had forced with Query Store for a brief time. It had the cost of 79.38. I’ve done all of these different things and they have all been captured by Query Store. I can look back on them all and compare the plans, which is cool.

We’ll notice that for things like Plan ID 14, it doesn’t gather streams until the very end of the plan, whereas back on my original Plan ID 3, it gathered streams before it did the final sort stream aggregate sort over there.

Minor differences in these plans. Very, very interesting to look at. There’s lots of different ways that I can do this.

Right now, I still have the plan guide freezing it from the handle, saying, “I see this plan in cache. I’m going to freeze that one for the associated query text.” I still have that in there, so I’m going to go ahead and clean that guy up and drop that plan guide so that our database is back to normal.