SQL Server Query Store - why does it not always use the forced plan?
Analeea last edited by
I forced a plan to be used for a specific query ID. However, SQL Server seems to only use the forced plan occasionally, interspersed with other plans. See image below. I forced plan 154315 for query ID 146019 and usage of this plan is indicated by the black check mark. Why is it not always used?
UPDATE: Well, nevermind. Just learned about https://www.littlekendra.com/2018/03/12/what-is-a-morally-equivalent-execution-plan-and-why-is-it-good/ .
carriann last edited by
It seems the success of forcing the plan is not guaranteed. The https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql?view=sql-server-ver15 says:
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.
I recommend you see https://dbanuggets.com/2021/12/27/query-store-fundamentals-why-my-plan-forcing-has-failed/ . Here's some of the article content:
Query for latest failure reason:
Code Source: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15 USE [QueryStoreDB]; GO
SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;
That article also mentions the following XE Session to capture the history of failure reasons:
CREATE EVENT SESSION [Querystoreforcedplanfailures] ON SERVER
ADD EVENT qds.query_store_plan_forcing_failed
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\qserror.xel'),
ADD TARGET package0.ring_buffer