Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Indicate "morally equivalent" forced plan where use_plan = true but is_forced = 0

Currently, if you force a plan in query store (or if it is forced by automatic plan correction), the exact plan which is forced has is_forced = 1 in sys.query_store_plan. In the plan properties, there is no use_plan indication.

After this plan is forced, often a "morally equivalent" plan will be compiled, which has a different query_plan_id and different costs, but the same plan shape.

This "morally equivalent plan" has is_forced = 0 in sys.query_store_plan. In the execution plan properties, use_plan = true.

These "morally equivalent plans" are currently difficult to identify in the Query Store reports and in the DMVs, because you really have to dig to find the use_plan = true property.

Ideas to improve the user experience:

1) Put some visual indication on a graphic plan on the top left most operator whenever use_plan = true (for whatever reason), to make the fact that something has been forced more obvious

2) Put a check mark (or some indication) on the bubbles in Query Store reports when use_plan = true

3) Make some way to identify these in sys.query_store_plan. (Is_forced = 2?)

Or just anything you can do to make these easier to understand for folks.

More details and a repro script are here: https://sqlworkbooks.com/2018/03/forced-plan-confusion-is_forced-vs-use-plan-true/

Thank you for making SQL Server more awesome!
Kendra

24 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Kendra Little shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base