The Myth About Estimated Execution Plans

How you name things matters. As Buck Woody said about the original SQL Server documentation, Books Online, “They weren’t books and they weren’t online.” There are even more horrific examples. As was pointed out to me by Mike Dimmick on my blog, the name for Estimated Execution Plans puts people off. When you go to generate execution plans, you have a choice, Estimated or Actual. Well, who would want an estimated measure when you can get an actual measure? The problem is, the name in this case, Estimated execution plan, is completely wrong.

An Estimated execution plan is a full execution plan. It’s not an estimate. You can test this yourself. Here’s a very basic query:

SELECT  *FROM    Production.Product AS pJOIN    Production.ProductSubcategory AS ps    ON ps.ProductSubcategoryID = p.ProductSubcategoryIDJOIN    Production.ProductCategory AS pc    ON pc.ProductCategoryID = ps.ProductCategoryIDWHERE   pc.Name = 'Clothing';

If you put this query into SQL Server Management Studio and type Ctrl-L on your keyboard, you will get an execution plan that looks like this:

That is an Estimated Execution. It’s also the final execution plan. If you type Ctrl-M on your keyboard and then execute the query, you’ll get a result set and the following, Actual, execution plan:

As a graphical representation of an execution plan, these two plans are practically identical. The exact same operators are in use in exactly the same order. That’s because these plans are the same. The query optimization process that arrives at an execution plan is the same if you’re going for an Estimated plan or an Actual plan. The only time you’re going to see a substantial difference, in terms of the operators within the plan, is when you have a recompile during execution. Differences in the statistics between when the plan was initially compiled as an Estimated plan lead to different choices by the optimizer.

If you look very closely you can see one difference in the graphical plans. The pipe representing data flow between the Nested Loops operator and the Clustered Index Seek operation against the ProductCategory table is slightly thicker in the Actual plan:

This is caused by one of the few differences between an Estimated and an Actual plan. An actual plan will include a few run-time metrics. An average operator within an execution plan will only have four extra properties for an Actual plan:

RuntimeCountersPerThread
ActualRows
ActualEndOfScans
ActualExecutions

That’s it. These are captured only at run-time. In our graphical plan above, the estimated number of rows is 1 and the actual number is 8. That explains that very minor difference in the size of the pipe. You can also verify this by looking at the properties for the operator:

Not only are the Estimated and Actual plans effectively identical, if you query the plan cache to retrieve an execution plan, the plan that is stored inside of SQL Server and is being reused over and over to execute your queries is an Estimated plan. It won’t have any runtime information stored with it.

Don’t get me wrong, Actual plans are extremely useful because of the run-time information. Knowing that the optimizer thought it was going to retrieve 1 row, but, in fact retrieved 100, 1000 or 1 million, is a vital part of query tuning. However, all the rest of the information you get from execution plans is the same. That’s why you shouldn’t shy away from using Estimated execution plans.