The Plan Cache Plan

Question

The Plan Cache Plan

Steve Jones - SSC Editor

SSC Guru

Points: 736524
More actions
March 9, 2022 at 12:00 am

#3971053

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

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

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

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 1

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?

NBSteve SSCarpal Tunnel Points: 4082 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 3

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)

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 4

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.