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.