Articles → SQL SERVER → Introduction To Execution Plans

Introduction To Execution Plans

In this article, I will briefly introduce the actual and estimated execution plans in SQL Server. To begin with, let us look at the purpose of the execution plan; the execution plan indicates how SQL Server may execute the query.

Components

Let us begin with the architecture of the SQL Server from the execution plan perspective. The SQL Server consists of two components: -

Relational engine → This component generates an execution plan based on the query submitted by the query source.
Storage engine → Executes queries based on the execution plan.

The image below illustrates the process from query submission to query execution.

Process

For this article, we will examine the internal processes of a relational database. There are three main processes of the relational engine that generate the execution plan: -

Query parsing
Algebrizer
Query optimizer

Picture showing the pictorical representation of the different processes in the relational database

Let us discuss each process.

Query Parsing

In this process query parser checks if the query is written correctly or not. If there is any error in the query, then the parser returns the error to the query source. A parse tree is created and passed to the next process if the query is written correctly and well-formed.

Algebrizer

Algebrizer uses a parse tree created during the query parsing process. Algebrizer performs the following activities: -

Resolves table and column names referenced in the query.
Determines the data type of each column involved.
Identifies the aggregate functions, like group by, max, etc.

The output of this process would be a query processor tree. Each query processor tree contains a hash-coded value that uniquely identifies the query.

Query Optimizer

In this process, the optimizer uses the query processor tree and statistics and generates a plan that the optimizer thinks is the most cost-effective plan. Statistics is Meta information maintained by SQL Server. The output of the query optimizer is an execution plan, which will be passed to the storage engine for execution of the query.

Estimated And Actual Execution Plan

The output that the query optimizer has created is an estimated execution plan. The actual execution plan is the output of actual query execution by the storage engine.

Posted By -	Karan Gupta

Posted On -	Wednesday, April 1, 2015

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**