We hear a lot about all the data that Query Store captures, but it is tempting to argue that the best part of Query Store is the ability to force a specific plan for a query. Arguably, if the data didn’t exist, we would have a harder time figuring out what plan to force, but plan forcing with Query Store is light-years easier than using a plan guide (the historical method for getting a query to use a specific plan). Further, tracking what plans are forced and how the queries are performing is easy too, using the Queries With Forced Plans report in SSMS.
Items to Consider
Before you go too crazy trying to stabilize performance with hundreds of forced plans, here are a couple things worth mentioning about plan forcing.
- I think of plan forcing as a temporary solution. I do not view it as a “set it and forget it” option.
- Use T-SQL for forcing and un-forcing (versus the UI) so you can track when it was done, and by whom, in change control. Nothing is entered in the ERRORLOG, or anywhere else, and there is currently no event that captures it.
- Evaluate both the execution statistics (average and total) and the different plans for the query to determine the “best” plan to force.
Why do I view plan forcing as temporary? Because if I have plan variability, I’d really like to address it through code or schema changes. Maybe there’s an index to add. Maybe a section of the query needs a re-write. To me, forcing the plan gives me a way to “stop the bleeding” – I can stabilize performance right now – until I can determine the right long-term solution and implement it.
For those of you with third-party applications, who cannot change code or indexes, you still want to monitor performance of those forced plans. While you can’t change the code or the schema, the vendor can (and most likely will at some point), and your data will change. Changes in data and its distribution could have a greater impact on plan choice than schema and code, and you may be preventing a better plan from being used by forcing the one that was good six months ago.
Deciding What Plan to Force
When you are looking at plan performance, deciding which plan to force can get tricky, depending on how many plans you have for a query. I start by looking at the plans – does one of them stand out as a “better” plan because of the operators it uses (or doesn’t use), the lack of warnings in the plan, etc. Then I look at the execution statistics, and this is where you want to make sure you know what metric is displayed in the UI. Examine graph A below, and decide what plan you would force before you continue reading.
Graph A shows plan performance for query_id 137512 across a 48-hour window. They y-axis is duration, and notice that it’s TOTAL duration.
Now take a look at graph B, below, which shows plan performance for the same query_id across the exact same window of time, but the y-axis is AVERAGE duration. Does your forcing decision change?
When we look at average duration across the different plans, it’s quite consistent. So much so that I probably wouldn’t force a plan for this query. I may investigate the plans in more detail to understand what the variation is, but for the most part, I’d probably leave it. Remember that total duration represents the total amount of time spent executing the query during a time interval. So the plans that had a lower total duration most likely were not executed as many times as those with a higher total duration. This becomes evident when you hover over one of the circles and look at the metrics for that specific data point, as shown below.
I think plan forcing is a fantastic feature in SQL Server, whether you’re manually forcing plans, or using Automatic Plan Correction. If you are forcing plans, take the time to decide the “best” plan to force by not just reviewing the plan and the execution statistics, but also by testing the plan with different input parameters. This can really to understand what plan consistently provides stable performance for a query. I recommend setting up an Extended Events session to monitor for forcing failures, and regularly re-visit what plans are forced to see if they are still appropriate. Finally, remember that if you turn off Query Store for any reason, plan forcing is not longer in effect. But if Query Store happens to flip to READ_ONLY, as long it’s enabled, your plans will still be forced.