Query Store is a fantastic feature of both SQL Server and Azure SQL DB. It allows you to monitor how queries execute against the database which is invaluable for troubleshooting performance issues. More than that though, it gives you the option to force an erratic query to use a particular execution plan, this helps avoid queries from running with inefficient plans and provides predictability and stability on the server.
Here we can see an example of a query that hasn’t been forced and the engine is free to execute it however it sees fit, as you can see, the variation of execution time is erratic and unpredictable:
Here we can see an example of a query that has been forced to use a nicely optimised plan:
Here we can see an example of a query that has been optimised but the forcing failed in one instance, resulting in an average execution time of over 2 minutes, whereas before it was only 114ms, a huge performance issue when you factor in that this query runs multiple times per minute.
Plan forcing isn’t guaranteed and can fail for various reasons, the documentation from Microsoft reads:
“When a plan is forced for a particular query, every time SQL Server encounters the query, it tries to force the plan in the Query Optimizer. If plan forcing fails, an Extended Event is fired and the Query Optimizer is instructed to optimize in the normal way.”
If a plan does fail, the query will still execute, the optimiser will just default back to its normal behaviour. The good news is we can monitor the failures through extended events, and the Query Store GUI (although this only shows the last one):
Below are the reasons and explanations for potential failures:
These potential failures reinforce the point that using Query Store isn’t a matter of forcing a plan and forgetting about it, even if you’re using Automatic Plan Correction (APC), you should always actively monitor and manage it.
