I’ve been aware of the Query Store since it was first introduced in SQL Server 2016 and I’ve had it enabled since, but honestly, I didn’t appreciate the power of this thing until a few years ago.
I’ve since learned, the Query Store can be a lot more than a passive black box, it should be part of any performance troubleshooting.
It’s been sitting there like Richard Marx waiting for me and I just turned it on and mostly forgot about it while relying on other tools like Solar Winds’ DPA and my own custom SQL procedure cache collector, to look at performance history.
You can find everything you’d possibly ever want to read about the Query Store in other places. I’m here to tell you, it’s a great tool that should be a part of any performance evaluation. So if you’re not using it yet, jump in.
And don’t be afraid to rely on it to force plan use. It’s a temporary band-aid and while I wouldn’t rely on forcing a plan to be a permanent solution, you can at least see for yourself how the procedure performs with the forced plan and then determine next steps.
So even if you don’t rely on the “force plan” feature, you can at least review the good plan and compare it to what it is doing now.
What about AUTOMATIC_TUNING and FORCE_LAST_GOOD_PLAN ON options? Even with them enabled, there’s still been plenty of times where the optimizer insists on switching and sticking to an incredibly slower plan (aka “plan regression”). Hopefully, it’ll revert back to the good one, but not always.
UPDATE: See example farther down of how AUTOMATIC_TUNING and FORCE_LAST_GOOD_PLAN prevented SQL from interrupting my Saturday afternoon of smoking cigars with my friends.
Some key points about the Query Store:
- With the Query Store you can alleviate performance issues from plan regression without having to change any code in the procedure
- If the database is part of an Always On Availability Group, the forcing will not persist on failover (until SQL Server 2022). While the Query Store metadata is on the secondary replicas, the manual forcing does not persist until SQL Server 2022.
- Unlike the procedure cache, the Query Store data persists on restart.
- If the stored procedure is altered or recreated, it will be assigned a new query_id and thus lose any manual plan forcing.
I’ve got a basic retrieval query you can use to hunt down the problem statement in the Query Store as well as a wrapper procedure around the DMV’s to assist. They are available here.
A typical scenario for how you could use it, is you notice a statement in a stored procedure is performing horribly all of a sudden. Once you determine which statement it is, look for it in the Query Store searching by stored procedure and filter by isolating it down to the statement you’re looking for (this part may be a chore if it’s a long query with a lot of similar sql).
Sort on the execution time and see if it recently regressed to a slower plan and if the reads and duration have increased substantially. If so, review the plans (I prefer using SQL Plan Explorer, it’s free). If you determine the plan it WAS using is better, then you can use sp_query_store_force_plan to instruct it to rely on that past plan.
When and why would you do this? You want to immediately resolve the performance issue without changing the stored procedure or updating statistics on large tables (or modifying indexes). The procedure hasn’t changed, but the optimizer’s behavior has. Next steps would be to evaluate the statistics and indexes to see if they changed or can be improved. Maybe there’s an additional column in the INCLUDE of the index it could have used if added. There could be a lot of things to consider but at least for now you’ve forced it to behave as it has wanted to in the past. So it’s at least as good as it was before. And this is for situations where you ALREADY have a performance problem, and the duration and page reads are ALREADY significantly higher and you are needing to improve the performance with as low risk and and quickly as possible.
Other options you could try in this scenario are to just remove the bad plan and hope the optimizer returns to using the good one (not likely). Or even remove the query from Query Store and hope that when the optimizer compiles it on the next execution that it chooses the good plan (not likely). You could also recompile it but that probably won’t help. Ultimately, you’ll likely need to force the plan until you figure out if the issue is the statistics sampling on one of the indexes or if you need to sweeten one of the indexes with an additional column in the INCLUDE (or something else).
Here’s an example of the scenario described above. One of the statements in uspWeHaveAProblem changed plans and took an average 325 seconds to complete instead of the usual 50ms or less. The logical reads were also significantly higher. In this example, the optimizer eventually resorted to the prior better plan on its own using auto tuning. In this particular case, the optimizer had decided to scan the clustered Primary Key instead of relying on one of the nonclustered indexes and a key lookup.
Here’s another example of the forced plan optimization auto correcting a regressed plan (averaging 2min) and changed to the last known good plan eventually without any DBA intervention. It dropped from almost 2min on average per execution to 0.32ms. The logical page reads also dropped from around 55 million to 2. The optimizer was relying on an index that wasn’t as selective as the one it normally used and was having to scan through millions of rows until the optimizer self corrected it to the last known good plan. I rarely see such a fine example of this in the wild.
SQL Server 2022 introduced “Optimized plan forcing” and I need to dig into that more. Microsoft has spent a lot of focus since SQL 2014 on improving the optimizer and this is yet another feature in the family of intelligent query processing updates. Matthew McGiffen has a good explanation of it on his blog.
Here’s some great resources to look into the Query Store further:
Best practices for monitoring workloads with Query Store
SQLShack: SQL Server Query Store – Overview
Ola Hallengren’s Query Store Overview from SQL Pass 2019
System Stored Procedures to Get Familiar With
sp_query_store_force_plan
sp_query_store_unforce_plan
sp_query_store_remove_query
sp_query_store_remove_plan
See below for what the Query Store looks like when you refuse to use it.
