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.