Articles → SQL SERVER → Introduction To Execution Plans

Introduction To Execution Plans

In this article, I will provide a brief introduction of the actual and estimated execution plan in SQL server. First, let us look at the purpose of the execution plan – the execution plan tells you how the SQL server may execute the query.

Components

Let us start with the architecture of the SQL server from the execution plan standpoint. SQL server contains 2 components: -

Relational engine – This component generates an execution plan from the query submitted by the query source
Storage engine – Executes query based on the execution plan

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

Process

In this tutorial, we will look at the internal process of relational database. There are three main process of relational engine which generates 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 parser returns the error to the query source. If the query is written correctly and well-formed then a parse tree is created and passes it to the next process.

Algebrizer

Algebrizer uses parse tree which is created in query parsing process. Algebrizer does the following activities: -

It resolves the names of tables and columns mentioned in the query
It identifies data type associated with each column of the table
It also identifies the aggregate functions i.e. group by, max etc. mentioned in the query

The output of this process would be query processor tree. Each query processor tree contains a hash coded value which is used to uniquely identify the query.

Query Optimizer

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

Estimated And Actual Execution Plan

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

Posted By -	Karan Gupta

Posted On -	Wednesday, April 1, 2015

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**