Understanding SQL Execution Plan (Part I)

Oh!!! Why my SQL query is taking so much time to retrieve data...?

This is the common question when our SQL query is taking much time to execute. So i will briefly describe hat is happening when we are executing any query.

Introduction

To understand what is happening when executing SQL query, we must know how the execution (Parsing of SQL statements) is happening. Basically SQL query will be parsed "Top to Bottom and Left to Right".

Understanding SQL Execution

When ever we Execute any SQL query(Statements) SQL Server will give output by processing this Query in two phase.

1. Relational engine

2. Storage Engine

Our SQL Query will be first given to the Relational Engine of the SQL server to analyze all statements. Means here our query will be parsed and will be optimized in the form so that SQL server can do transaction on that query. At this time our query will be in optimized form means it is the shortest execution plan which can be taken out on the database to retrieve or update the data.

Then after, this optimized query in the form of binary data will be submitted to the Storage Engine, where the actual execution will be done on database. All retrieval or modification of the data will be carried out here.

So in these two steps our query will be executed and we are getting result. So we will focus on the "Relational Engine" part of the execution plan because from here we will get our Answer to the Question "Why my query is getting to much time to execute?".

Relational Engine

• First our Query will be checked against all syntax of T-SQL, this process is called "Parsing" and the output of his process will in form of tree which is called "Sequence Tree".
• This "Sequence Tree" will be given to the process called "Algebrizer", which is the process which will check all the table names, column names and their data types in "DML" statements and will verify against the Database. And this process will give binary output which is called "Query Processor Tree".
• This "Query Processor Tree" will be given to one bunch of software called "Query Optimizers". Where these software will calculate the execution path based on the already created Execution plan and the Statistics available from the Database server.
• This Optimizer will give some number to each and every steps given in plan which is called "Estimated Cost" and this cost for all step will be called Estimated cost for Prepared Execution Plan. And this Plan will be given for executing query.
• SQL server will check the availability of the Execution plan in the cache called "Plan Cache" and if already available then will take that plan, and if not then will add into Plan Cache.
• Then this plan will be given to Storage Engine to actually execute the query.
• Note that this is the "Estimated Execution Plan" not the "Actual Execution Plan" which will be taken out during the process in Storage Engine.
• Yes but when this Estimated Plan exists in Plan cache of Actual Execution Plan then Storage engine will use that Actual Execution Plan from cache, because this plan is already used by the Query Engine (Storage Engine).

Why we see difference between Estimated and Actual Plan

When we generate the Estimated plan and when we run the query actually and get the actual execution plan then there may be some difference between them. This difference may be due to following scenarios.

• Due to not having proper Statistics which is used in Query Optimizer. there may be some reasons to not having proper Statistics because due to changes in data in tables and having changes in Indexes, statistics will be changed and they may affect the generation of estimated execution plan.
• Due to Invalid Execution Plan. This may be the case like we are using CREATE TABLE statement in our query. Because Algebrizer works on already available metadata, so when we ask for estimated execution plan for query which contains CREATE statements then it will give error, but when we run this query with actual execution plan then it will give actual execution plan.

NEXT

In My Next Part of this article, i will show you how to use Execution Plan to improve our query. Part Two will be posted soon.

References

eBook Dissecting SQL Server Execution Plan By Grant Fritchey.