0845 643 64 63

Query Store Forced Plan Failures

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:

Erratic Query

Here we can see an example of a query that has been forced to use a nicely optimised plan:

Forced Query

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.

Failed Query

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

371,505 Spambots Blocked by Simple Comments

HTML tags are not allowed.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon