This is the second post on execution plans. I’m going to briefly discuss estimated execution plans and actual execution plans, the differences between them and when you would want to use which.
First however, a bit on query execution, just so that I know everyone’s on the same page.
When a query is submitted to SQL Server (and for simplicity I’m going to assume it’s a straight select statement not a procedure) the query is parsed, then bound to the underlying objects(tables, views, functions, etc). Once the binding is complete, the query passes to the query optimiser. The optimiser produces one or more suitable execution plans for the query (more on that in a later post). The query is then passed into the query execution engine, which does the memory grants, picks a parallelism option, if necessary and executes the various query operations.
Estimated execution plans
When an estimated execution plan is requested for a query, the query goes through the parsing, binding and optimisation phases, but does not get executed.
An estimated execution plan can be obtained by issuing any of the following SET statements before executing the query, or by selecting the ‘Show Estimated Execution Plan’ button from the toolbar in either Management Studio or Query Analyser
SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET SHOWPLAN_TEXT ON
Actual execution plan
An actual execution plan is returned after a query has completed executing. Hence the query goes through all four phases.
An actual execution plan can be obtained by issuing any of the following SET statements before executing the query or by selecting the ‘Include Actual execution Plan’ button from the toolbar
SET STATISTICS_PROFILE ON SET STATISTICS_XML ON
Differences
Since for an estimated execution plan the query is not executed, it will not include the actual number of rows affected by the various query operators. Also the estimated plan will not include details on the number of threads used or the number of rewinds or rebinds.
Since only the estimated number of rows is available in the estimated plan, and that row estimate is derived from table statistics, the estimated plans can look very far off for queries that contain any data source that does not have statistics (table variables, remote data sources, openXML). I have seen an estimated plan that involved several remote data sources where the estimated row count at the end of the query was somewhere around 56 billion. In reality, when the query was executed only 27 rows were returned.
Another point about estimated plans is that, since the queries are not run, any procedure that involves DDL operations may fail to produce an estimated plan (e.g. a procedure that creates a temp table, populates it then queries it will give an error when an estimated plan is requested, since the temp table does not exist).
Finally
So, that’s what they are. As for which to use, personally I prefer the actual execution plan, unless there is some constraint that prevents the running of the query on a test machine, like a very long running query, . Especially now with SQL 2005 where the XML plan can be saved to disk as a sqlplan file, which is a feature that was sorely lacking in SQL 2000.
For more on actual and estimated plans, see the article that Kalen Delaney wrote recently.
I’m not sure at the moment what the next post on execution plans will be about, it depends on what comes to mind. If there’s anything specific about exec plans that you’d like to know, post a comment and I’ll see what I can do.