Execution Plans
An execution plan is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted.
Query Optimizer
The query optimizer is essentially a piece of software that “models” the way in which the database relational engine works. Using the query processor tree and the statistics it has about the data, and applying the model, it works out what it thinks will be the optimal way to execute the query – that is, it generates an execution plan.
The optimizer will generate and evaluate many plans (unless there is already a cached plan) and, generally speaking, will choose the lowest-cost plan i.e. the plan it thinks will execute the query as fast as possible and use the least amount of resources, CPU and I/O. The calculation of the execution speed is the most important calculation and the optimizer will use a process that is more CPU-intensive if it will return results that much faster.
The optimizer will perform a cost-based calculation to select a plan. In order to do this, it relies on statistics that are maintained by SQL Server.
Statistics are collected on columns and indexes within the database, and describe the data distribution and the uniqueness, or selectivity of the data. The information that makes up statistics is represented by a histogram, a tabulation of counts of the occurrence of a particular value, taken from 200 data points evenly distributed across the data. It’s this “data about the data” that provides the information necessary for the optimizer to make its calculations.
Estimated and Actual Execution Plans
There are two distinct types of execution plan. First, there is the plan that represents the output from the optimizer. This is known as an Estimated execution plan. The operators, or steps, within the plan will be labeled as logical, because they’re representative of the optimizer’s view of the plan.
Next is the plan that represents the output from the actual query execution. This type of plan is known, funnily enough, as the Actual execution plan. It shows what actually happened when the query executed.
Actual execution plans, unlike Estimated execution plans, do not represent the calculations of the optimizer. Instead this execution plan shows what happened when the query was executed. The two will often be identical but will sometimes differ, due to changes to the execution plan made by the storage engine.
Sources:
Comments