Quiz: How can you tell if an execution plan has been forced, guided, or corrected?

Quiz: 🚯 How can you tell if an execution plan has been forced, guided, or corrected? 🚱

Before spending a lot of time troubleshooting, it's good to know if an execution plan has been coerced. How can you tell?
  • You can sometimes see the hint appear in the TSQL embedded in the plan in 'StatementText'. That doesn't always work, so I didn't make it an answer and I used 'reliably' in the question.
  • Assume that the forced plan was compiled with a different cost than the manually forced plan.
  • Assume that the forced plan was compiled with a different cost than the "fast plan" identified as ideal by automatic plan correction.
  • This is the type of Plan Guide that can be used to add a hint to a query.
  • If you use a Template Plan Guide to force parameterization, you may notice that parameters are named @0, @1, @2, etc in the plan. But anyone can use those parameter names, so I haven't included this as an option.

By the way

I think this quiz is a toughie, because this stuff is so idiosyncratic. I’ll be doing a SQLChallenge soon where I provide plans which have been nudged, forced, or bullied in one way or another, and challenge folks to identify the coercion from the plan — I think it’ll be a fun hands-on way to see this in action.

5 thoughts on “Quiz: How can you tell if an execution plan has been forced, guided, or corrected?

  1. Hi Kindra,
    love these little quiz’s, especially when i score well 🙂

    There is a little interesting twist to question number 2.

    If you are using the query store catalog views instead of live query plan cache or the QueryStore reports found in SSMS, to see the queryplan of a stored query, there is no ‘Use Plan’ = True property in the xml of the query plan.

    example:
    This query extracts info on ‘myStoredProcdure’ form QueryStore:

    SELECT object_name(qsq.object_id) DB_ObjectName
    , qsp.query_id
    , qsp.plan_id
    , CAST(qsp.query_plan AS XML) query_plan
    , qsp.is_forced_plan
    FROM sys.query_store_query AS qsq
    JOIN sys.query_store_plan AS qsp ON qsp.query_id = qsq.query_id
    JOIN sys.query_store_runtime_stats AS qsrs ON qsrs.plan_id = qsp.plan_id
    WHERE object_name(qsq.object_id) =’myStoredProcedure’

    When using the query store catalog views, you need to include the column ‘is_forced_plan’

    regards

    Peter

    1. This is so complicated that it’s hard to word the question properly! I think you are right that I need to change the wording.

      You are correct that ‘Use plan = true’ is missing, but it’s only in *some* cases, and it’s the same in the GUI reports as it is in the TSQL. I’ll write up a blog post real fast with demo scripts and screenshots that shows what I mean, but the gist of it is that there’s more than one pattern:

      Pattern 1: Plan is forced, and that *exact* plan is what’s being used (same costs, same estimates). In this case, the plan shows with is_forced_plan in sys.query_store_plan.

      Pattern 2 (this should be very common): A plan is forced, but the plan that’s being used is slightly different. The plan shape is forced, but it’s compiled with different enough parameter values that it gets a different cost. This gets a unique entry in sys.query_store_plan. Is_forced_plan = 0 for this plan and it doesn’t have a check mark in the Query Store Reports (the *original* plan that’s being forced has that). Instead, for this “forced but modified” plan, you have to look for ‘Use plan’ = true, both in the DMVs and in the Query Store Reports.

      I have repro code for this set up and will reshuffle blog posts and publish this for tomorrow.

      As to how to re-word the question to specify that I’m talking about Pattern #2 here, that will take more coffee!

      1. yes pattern 2 is, at least at our place, very common, and in the beginning i was very confused that allthough i had forced plan xxx onto a query, i still got other plans created, that looked identical to the forced plan

    2. Lil update: post showing this is scheduled for Monday, March 5, comes with demo code 🙂

      I updated the wording on the descriptions a bit, and will put a link at the bottom of this page to the post when it goes live.

      Thanks much for your comment!

      1. looking forward to it

Leave a Reply

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