I have, in the past, made way too much of the need for Actual Plans when doing performance troubleshooting. The primary reason for this is to get the Actual Plan in order to see the differences between the Actual and Estimated Row Counts as a means of understanding how the optimizer saw the data. But, is that the only thing that’s different between Actual & Estimated Plans? Well, pretty much, yeah.
I took two fairly average execution plans from SQL Server 2014 and ran them through Altova’s XML Spy, which does XML comparisons similar to how Redgate SQL Compare will compare two data structures for you. Here is every single difference I found. Everything was additional information in the Actual Plan.
In the information for the first operator, in my case, a SELECT operator, in the QueryPlan element, two properties:
DegreeOfParallelism
MemoryGrant
In the MemoryGrantInfo element, six properties:
RequiredMemory
DesiredMemory
RequestedMemory
GrantWaitTime
GrantedMemory
MemoryUsed
Then, in all the operators, in each RelOp element in the XML of the execution plan, one additional XML element, RuntimeInformation with these properties:
RuntimeCountersPerThread
ActualRows
ActualEndOfScans
ActualExecutions
This data is not exhaustive. I used a relatively benign query (it hit about 10 tables, had some suggested missing indexes, and about 40 operators all told). I’m sure if I tried lots of different types of queries, I might find a few additional differences. But for your average, “Hey, Let’s Tune This”, query, we’re looking at one additional element in the operators and twelve (12) additional properties, only four of which are in the majority of the operators.
Don’t get me wrong. If I have the choice, I want to see the Actual Plan, because that Actual/Estimated row count and execution count comparison are important. However, they are not the be all, end all, of reading execution plans and query tuning. This means, if you can get the Estimated Plan, it’s probably good enough most of the time. Heck, you can look at the estimates and then look at the data and statistics directly to get a sense if they’re accurate or not. The only time you’re likely to see any differences (other than those noted above) between an Actual and Estimated Plan is in the event of a recompile. It’s also worth noting, the plan you’re going to retrieve from the cache is going to be an Estimated Plan.
Do not be afraid of the Estimated Plan.
If you want to spend a lot of quality time with me, talking execution plans and query tuning, I’ve got a couple of different all day pre-conference seminars coming up. The first is in Las Vegas at the Connections conference. Click here to register. The second will be down in San Diego, the day before their SQL Saturday event.
[…] Differences Between Actual & Estimated Plans – Grant Fritchey (Blog|Twitter) […]
I think the name of ‘Estimated’ plans is misleading to the newcomer. It implies that it’s a guess. In reality, this *is* how SQL Server will execute the query, assuming that the conditions under which the estimate was generated still hold (i.e. no new or updated statistics, no changes to SET options). It does appear that using Display Estimated Execution Plan does add the plan to the server’s cache (checked with sys.dm_exec_cached_plans on SQL Server 2008 R2), and that plan will be used if you then run the batch (no new plan was added to the cache when executed).
It’s a somewhat accurate name, in that the plan is generated using estimates of cardinality. However, the ‘Actual’ plan is merely the same plan decorated with how many rows were really hit and how many times operators were really run.
You can do most of your optimisation work using the ‘estimated’ plan, if you have a good idea of how many rows you would expect to see for each part of the query. You can do the comparison between that, and the cardinality estimates from SQL Server, and spot places where the estimates are bad, or obvious candidates for improved indexes when an expensive operator has been used. It’s just a bit easier to eyeball bad estimates with the ‘actual’ plan output – though you still need to understand whether the distribution of values in a column is skewed and the estimate was good for the parameter values that generated the plan, but bad for the actual execution.
Great point about the name being a potential scare point. I hadn’t really thought about it like that before. Thanks.
By the way, getting an estimated plan doesn’t load the plan into cache. Writing a blog post on that now.
[…] blogged about this before, but it’s worth mentioning again. There are a only a few minor differences between an […]
[…] This is a question and a myth I have to fight against all the time. It’s so hard 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 capture run-time metrics with that plan. So we end up with what is known as an actual plan, but it’s still just an estimated plan plus those run-time metrics. […]
[…] This is the classic estimated plan. These are very useful. It’s a quick and safe way to get an execution plan. Except when dealing with recompile events, this plan will always be the same overall shape as an actual plan or any of the “other” execution plans we’re going to talk about here. There may be differences in some of the property values between this plan and other plans. This is because you’re getting row counts and other estimated values at the moment you get this plan (unless, the plan is in cache, in which case, you’re getting the cached plan). However, the literal count of differences between these plans and what we call actual plans is quite small. […]