The Plan Cache Plan

  • Comments posted to this topic are about the item The Plan Cache Plan

  • Steve, per the reference "Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing."  Isn't this the actual plan since Explain plans are not cached?

  • I'm surprised Hugo hasn't already chimed in here, but everything about this question and answer choices is, at best, misleading.

    What's stored in the plan cache is THE execution plan.  It's the actual execution plan used to execute the query.  It's also the execution plan returned for the Explain/Estimated execution plan.  They are one and the same.  So the answer of "Actual" is true, and the answer of "Actual and Estimated" is also true, since they're the same.

    Now when you request an "actual execution plan" in SSMS, it also displays some of the run-time statistics from that specific execution.  Those statistics are not part of the plan cache because they change with every execution (thus contradicting the term "cache").  So perhaps the author chose "Estimated" as the answer because run-time statistics are not stored in the plan cache, but if that's what the question was supposed to ask, it is worded very poorly.  From a technical point of view, "Estimated" isn't the wrong answer, it's just one of the three possible correct answers.

  • I thought the question was just fine because what the system runs has no actuals in it and so that inherently makes it "estimated" even if SSMS didn't exist at all.


    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I agree this is a bit misleading. I'm not sure what triggered me to think about this, but since there aren't details, this should be the estimated plan, with the actual plan typically containing the actual stats and row counts. However, as I read it again ,that is misleading.

    Not quite sure how to rephrase this to be a bit clearer. Suggestions welcome.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply