All the execution plans are estimated plans. All of them. There fundamentally isn’t any such thing as an “Actual” plan.
Where Do You Get Execution Plans?
There are a lot of sources for execution plans. You can capture them using extended events (or, if you must, trace). You can capture them through the Management Studio gui. You can also capture them from the SQL Operations Studio gui. You can query the cache through the DMVs and pull them in that way. You can look at plans in query store.
All these resources, yet, for any given query, all the plans will be identical (assuming no recompile at work). Why? Because they’re all the same plan. Each and every one of them is an estimated plan. Only an estimated plan. This is why the estimated costs stay the same between an estimated and actual plan, this despite any disparity between estimated and actual row counts.
I’ve blogged about this before, but it’s worth mentioning again. There are a only a few minor differences between an estimated plan and an actual plan. It’s all about the data set. What’s going on is that an actual plan can capture query metrics, which are then appended to the estimated plan. At no point is any different plan generated during this process. It’s just a plan, an estimated plan, or, it’s a plan plus query metrics.
Conclusion
Never mistake me on this, I want to see the query metrics as often as I can when dealing with execution plans. That means capturing what’s called an actual plan. However, let’s be clear, there’s nothing different in that plan except those metrics. It’s not a different plan. It’s the same plan, plus some metrics. Hopefully this helps people clarify what’s up with execution plans and will help them put a little more faith in using the estimated plans in some cases.
Want to talk execution plans and query tuning some more. I have three, full day seminars coming up.
First, in Richmond, on March 23, 2018, I’m doing a precon for SQLSaturday Richmond on the topic of tools for query tuning. Please register here.
I’m also going to be doing this same pre-con for SQLSaturday Philadelphia on April 20, 2018. You can sign up here for that event.
Additionally, I am doing this precon for SQLSaturday NYC on May 18, 2018. Go here to register for that event.
[…] Read the entire article at the source link… Grant Fritchey […]
My understanding has always been that the actual execution plan is just that, the ACTUAL execution plan, and while it closely mirrors the estimated plan in most cases, it can differ sharply when parallel execution, object creation and manipulation, and other runtime complications are involved.
Can you direct me to authoritative links to the contrary? Thanks!
Any differences you see between an actual and estimated plan are caused by recompiles. The examples you listed, object changes, etc., are perfect examples of where you can get a recompile. A recompile can result in a different plan being displayed between the time you capture an estimated plan and the plan returned after you execute the query. However, the difference in the plans is caused by the recompile, not that there is a thing, an actual plan, that is radically different than the estimated plan. It will have all the runtime metrics added, but that’s the difference, not the plans itself.
I’d love to give you a Microsoft link on this, but I’m not aware of them having specific documentation on this behavior. In fact, the best resource I know of for this behavior are my books (especially the new version which will be out in about six weeks).
However, I don’t want to argue from authority. Test it. See if you can find a plan that displays differently as an “actual plan” than the plan in cache through the DMVs or in the Query Store. It won’t be. Ever. It’s always an estimated plan plus metrics.
Probably not perfect verification, but, since I’m sitting in Redmond and I have a couple of guys from the engine team in front of me, I asked. They confirmed. All execution plans are estimated plans and the actual plans are estimated plans plus runtime metrics. The specific individual I was talking with is Joseph Sack. You can look him up.
Sorry I don’t have a link available for you.
[…] Grant Fritchey drops a bomb on us: […]
Discussion with Rob Farley several years ago (who could never pass up an opportunity to pull someone’s leg):
Me: “So what’s the difference between the actual and the estimate?”
RF: “Oh they’re different. They ARE different. Veeeery different.”
Me: “But it looks like nothing changes. Why is it called the ‘actual’ when it just seems like the estmate all over again.”
RF: “Because it IS the actual. It’s the actual estimate. The estimate is just the estimated estimate.”
Me: “Huh?”
Sounds like Rob.
[…] Read More (Community […]
[…] to convince people that all execution plans are estimated plans in the first place (by the way, all execution plans are estimated plans). If we execute a query at the same time we capture a plan, we have enabled SQL Server to also […]
[…] that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime metrics. It’s not that we’re going to […]
[…] that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime […]
[…] People have written string splitting functions in a variety of ways (here’s a few; here’s a few more) over the years. Some use XML functions, other use CLR, others are plain T-SQL. The trouble is…none of them are great. They all have their weaknesses, most notably they can be slow. And UDF performance impacts are hidden in actual execution plans – you need to get the estimated plan from Management Studio if you want to see the approximate costs resulting from calling your function (yes, even though Actual plans are Estimated plans). […]