By Kendra Little on September 2, 2024
tldr; I’ve published a script to loop through all databases on an instance, identify if there are any query plans in a problematic “failed” forced state (which can hurt query performance), and un-force them if found. Get the dbo.dba_QueryStoreUnforceFailed stored procedure on GitHub.
This script is designed to work on SQL Server on-prem, in a VM, or in Azure SQL Managed Instance or SQL Server RDS. Since the script is instance-level and loops through all databases, this isn’t really designed for Azure SQL Database – and you don’t get a SQL Agent there anyway, so you probably want to change this around for that use case. The script is shared under the MIT license, feel free to contribute code and/or adapt away for your own uses.
What’s so bad about failed forced plans?
Query plans that have been forced in Query Store may end up with the forcing in a “failed” state for a variety of reasons.
Sometimes this might be that a structure in the forced plan is no longer valid– maybe an index that had been selected in the plan has been dropped or its definition has changed. This plan will have a last_force_failure_reason of 8712 and a last_force_failure_reason_description of NO_INDEX in sys.query_store_plan.
I’ve found that if a forced plan is cancelled by the calling application while it is still compiling, it will have a last_force_failure_reason_description of GENERAL FAILURE, and that this scenario can cause serious performance problems by dramatically increasing compilation time while the query has the failed forced plan. See my post General Failure Failed Forced Plans in Query Store Cause Even Slower Compile Times for a script to reproduce this problem.
I do think there are likely more scenarios where failed forced plans cause slow compilation, other than the GENERAL FAILURE state. And failed forced plans do you no good at all– there is no benefit to leaving them in a failed forced state.
So if you force plans at all, whether manually or by using the Automatic Plan Correction feature, it is worthwhile to set up a process to check for failed forced plans and un-force them.
Get the script
Get the dbo.dba_QueryStoreUnforceFailed stored procedure on GitHub.
Shouldn’t SQL Server un-force these itself?
I think so. Unfortunately, it does not. Add this to the “job security for database specialists” file, I suppose.
I’ll be talking about this and more at PASS Community Data Summit in November
I’m giving a regular session, Perf Tuning in Prod: Plan Forcing, Plan Guides, and Plan Correction in Seattle at the PASS Community Data Summit in November.
You can save $150 off the 3 day conference registration with the code LITTLEK24. Register today.
REGISTER