SQL Server Query Store - why does it not always use the forced plan?



  • 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?

    enter image description here

    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/ .



  • 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,
    force_failure_count, last_force_failure_reason_desc
    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
    WITH (STARTUP_STATE=OFF)
    GO




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2