Let’s say you want to have an addition built on your house. You contact a contractor, who comes to your house, looks at your lot, looks at what exists, and asks you questions about exactly what you want. He then gives you an estimate of the work – approximately how long he thinks it will take his crew, what supplies will be needed, and the cost of those supplies.
SQL Server gives you a similar option. When you write a query, before committing to it, you can request an estimated execution plan.
The estimated execution plan is designed to show what SQL Server would most likely do if it were to execute the query. Using statistics, it estimates how many rows may be returned from each table. It chooses the operators it will use to retrieve the data – scans or seeks. It decides how to best join the tables together – nested loops, merge joins, hash joins. It is a reasonably accurate guide to what SQL Server will do.
You can view an estimated execution plan for an individual statement, a stored procedure, or a function. The plan can be text, XML, or graphical. To view text, use SET SHOWPLAN_TEXT ON. For an XML version, use the command SET SHOWPLAN_XML ON. To view a graphical plan in SSMS, click the Display Estimated Execution Plan button in SSMS.
There are some cases in which SQL Server can’t create an estimated execution plan. If your query has parameters in it and values aren’t passed in, SQL Server can’t interpret those – it needs literal values. If the query references a temp table that is not declared, the plan also can’t be generated.
Now, let’s go back to our construction project. We’ve signed the contract and the contractor begins work. While the addition is being framed, you decide you want to add an extra room, or add windows, or make the ceilings higher. The contractor has to adjust for this in terms of time and cost. This will change how long the project takes, and how expensive it is.
Executing a query in SQL Server is no different. The actual execution plan is shown after a query is executed. The difference here is that SQL Server can tell you exactly how many reads were performed, how many rows were read, and what joins were performed.
If it’s a long-running query, it will take a while to get the execution plan. Parameters, if required, must be passed in.
The text plan is generated using SET STATISTICS PROFILE ON. The XML version of the actual plan can be viewed by using SET STATISTICS XML ON. A graphical version can be generated in SSMS by using the Include Actual Execution Plan button.
There are some cases in which things that show up in the estimated plan will not show in the actual plan. For example, when you call a scalar-value function, the estimated plan will show it – the actual plan will not. (This is why the impact of functions can be very misunderstood.)
“IF I VIEW AN ESTIMATED PLAN, THEN IMMEDIATELY RUN THE QUERY AND VIEW THE ACTUAL EXECUTION PLAN, WHY DO I SEE DIFFERENCES?”
The query optimizer is going to use statistics on the tables and indexes to decide how to perform the actual query execution. If statistics change for any reason between the time you estimate a plan and when the query is actually run, you can see differences.
Changes to table schema, indexes, or even the data can affect the statistics. If a new index is added, or rows are updated in the table, when the query optimizer executes the query, it could choose a different set of operators than it did during estimation. Sometimes the differences between the estimated and actual plans can be large!
How can you prevent this from being a problem? Make sure statistics are updated on your tables and indexes. Auto update stats will automatically refresh statistics if a specific number of rows in a table change – after a table reaches 500 rows, roughly 20% of the rows need to change. (Exact details about that are here http://support.microsoft.com/kb/195565/en-us). The more rows that your table contains, the more changes that need to be made for them to automatically refresh – on large tables, you may need to set up more frequent stats updates.
You also want to be aware that using table variables on large result sets can be wildly inaccurate – they always estimate a low number of rows.
TRIVIA: WHICH TYPE OF PLAN IS STORED IN THE PLAN CACHE?
The estimated plan is stored in the plan cache. If you review the XML (doesn’t that sound like fun?!), you will see “ParameterCompiledValue” listed near the end. This is what value the query was run with when the plan was stored. Ensuing executions may use different values, which can lead to less-than-optimal performance if bad parameter sniffing happens.
Estimated execution plans can be very useful as you are writing and tuning queries, giving you an idea of how SQL Server will most likely perform query execution. However, if you need to know exactly what steps SQL Server will take, executing the query and reviewing the actual execution plan is the only way to be certain.
Learn More in Our Execution Plan Training
Our How to Read Execution Plans Training explains how to get an execution plan, how to read the operators, and learn solutions to common query problems.
17 Comments. Leave new
Probably worth noting on the topic of statistics and auto-updating them is trace flag 2371, which overrides the 20% threshold for tables over 25K rows. It can be handy given the right circumstances. 2389 and 2390 are worth reading up on too for those that may not be familiar with them, but I believe they remain “undocumented” (which means unsupported as well).
http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
How does the estimated pan get stored as opposed to the actual plan? Just curious.
The estimated plan isn’t stored in the plan cache.
Brent,
Did you mean to say: The actual plan isn’t stored in the plan cache?
J – no, eric81 asked how the estimated plan was stored. It’s not stored in the plan cache.
Under the header WHICH TYPE OF PLAN IS STORED IN … in this article is stated the estimated plan was stored in cache hence my confusion. Thanks for confirming Brent it is not the actual plan is what’s stored.
When you view a plan stored in cache, you will see estimated values – estimated costs, estimated rows returned. Thus, I say the plan stored are “estimated” plans. Any individual execution of that query could return different actual numbers of rows, but that in itself wouldn’t invalidate a plan and cause it to be recompiled.
Hey, wait, something’s gone a bit off the rails here! I’m not sure if this is a wording issue or what, but we’ve got a little bit of a misunderstanding going on.
The execution plans stored in memory in SQL Server which you can see with sp_BlitzCache are usually referred to as “estimated” plans in conversation in my experience. The execution plan does not contain the “actual” / post execution statistics for rows returned / MB returned, etc. You can see aggregate query stats for that stuff in the DMVs, but it’s an “estimated” plan.
And the plans are stored in memory. SQL Server doesn’t have to go to disk to fetch an execution plan (that’d be slower than just recompiling it).
If you generate an estimated execution plan for a query in SSMS, that plan shouldn’t get stored in the cache. That only happens when the query is run. But the common definition of “estimated plan” is “doesn’t have all those ‘actual’ runtime stats.”
So I believe the post to be accurate. If it’s not, I would LOVE to learn how I’ve been wrong about it all this time. Show me!
Editorial note: I took so long to write this that Jes beat me by several minutes. I am clearly too slow.
Yeah, I should have been more clear. If you put a query in SSMS and hit control L to display the estimated plan, that doesn’t go into cache. That’s what I meant by estimated plan. (Seems obvious to me, but clearly I’m the only one, hahaha, so I’m probably using the wrong terms.)
Oh, yeah, totally agree. I think that the blog post is using the term “Estimated Plan” not only to mean plans you generate by using that button, but the broader meaning of “logical plan only”.
I was really curious, “Where does this terminology come from?” I did a little research. I don’t think the terms “estimated plan” and “actual plan” really come from Microsoft at all: I don’t see those in any of the major whitepapers on this topic. I think they’re largely community based terms that came out of need.
One recommendation for you guys who were asking about this and are interested in the topic: go read Grant Fritchey’s free ebook, “SQL Server Execution Plans”! You’ll love it if you’re interested in this. He covers this issue on page 25, but the whole thing is worth a read.
http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf
I think my confusion (and probably others’ as well) came from the tooltips in SSMS itself… “Display Estimated Execution Plan” vs. “Include Actual Execution Plan.” Thanks for the clarification.
Hello,
I have created a view. It has 50 joins and number of correlated sub-queries. But when I am looking for execution plan, it is not showing any missing index. Could you please help me that why it is not recommended any missing index details.
Any help would be really appreciated 🙂
Thanks,
Arjun
Arjun – for questions unrelated to blog posts, your best bet is to post them (along with the execution plan) over at http://dba.stackexchange.com.
Could you clarify please, Is there any way that ‘Display the Estimated Execution Plan’ can actualy change data in your database?
For instance you have 30 unfamiliar procedures that are called from main procedure and all that is written by someone else, in that case is it always safe to ‘Display the Estimated Execution Plan’?
Because when you press it you can see with sp_who2 that your session is doing inserts, deletes and then it all breaks with “System.OutOfMemoryException” exception.
Is it always safe to ‘Display the Estimated Execution Plan’?
Yb80 – uh, no, displaying an estimated execution plan doesn’t change data.
Well, that’s good then.
These inserts, deletes and 10 opened transactions during plan estimation scared a bit.
Thank you for clarification and excelent articles.
I have a view which gives different estimated time when I was using where and Between clause for the same view with different range of date value. The first query takes just a 1 second to complete and second query takes 4 minutes to completes, Could you please suggest what could be reason for this delay in second query when , I still using same veiw.
Query1:
select * from vwMsOrdersLifecycleEquipment where DateCreated between ‘2021-01-25’ and ‘2021-02-26’ and PartNumber like ‘1361693’ order by DateCreated desc.
Query2:
select * from vwMsOrdersLifecycleEquipment where DateCreated between ‘2021-04-25’ and ‘2021-05-26’ and PartNumber like ‘1361693’ order by DateCreated desc.