Every Execution Plan Is An Estimated Plan

SQL Server
I consider myself to be the most responsible for making such a huge deal about the differences between what is labeled as an Estimated Plan and an Actual Plan. I walked it back in the second edition of the Execution Plans book. Hugo and I completely debunked the issue in the third edition of the Execution Plans book. That is the one you should all be referencing now. As I like to joke, the guy who wrote the first two editions of the book was an idiot (and lest anyone take offense, let's be clear, I'm the idiot). Now, I'm trying my best to make this whole issue more clear. Let's talk about the "different" plans you can capture in SQL Server. Estimated Plan This is where you have a…
Read More

Get the Last Actual Plan With sys.dm_exec_query_plan_stats

SQL Server, T-SQL
I've always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for the print version) 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 get a completely different execution plan when we look at an actual plan, it's just going to have those very valuable runtime metrics. The problem with getting those metrics is, you have to execute the query. However, this is no longer true in SQL Server…
Read More

Actual Execution Plan Costs

T-SQL
Why don't "actual execution plans" have "actual execution plan costs"? 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. Execution Plan Costs When you look at a given operator within an estimated plan, it's going to show you four numbers related to cost: Estimated CPU Cost Estimated I/O Cost…
Read More

Every Single Execution Plan is an Estimated Plan

SQL Server, SQL Server 2016, SQL Server 2017
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…
Read More

Generating Estimated Plan and the Plan Cache

SQL Server, T-SQL
Does generating an Estimated Plan cause that plan to be loaded into the plan cache? No.   What? Still here? You want more? Proof? Fine. Let's first run this bit of code (but please, not on your production server): DBCC FREEPROCCACHE(); That will remove all plans from cache. Now, let's take this query and generate an Estimated Plan (CTL-L from your keyboard or by clicking on the "Display Estimated Execution Plan" button on the toolbar): SELECT * FROM Production.ProductModel AS pm; This will generate a trivial plan showing a scan against the Production.ProductModel table. Now, let's run another query: SELECT deqs.plan_handle FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text = 'SELECT * FROM Production.ProductModel AS pm;'; That's just an easy way to see if a plan_handle exists.…
Read More

Differences Between Actual & Estimated Plans

SQL Server, T-SQL
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,…
Read More