Click here to Skip to main content
Click here to Skip to main content

Understanding SQL Execution Plan (Part I)

, 12 Aug 2008 GPL3
Rate this:
Please Sign up or sign in to vote.
Brief Intro of how out t-SQL is parsed and submitted to SQL Engine to retrive data

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.

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

Share

About the Author

JustChiragPatel
Software Developer
India India
Chirag Patel, a Programmer Analyst in a well known IT company working on .NET Technologies since last 2 years. He is interested in Pure business logic and fuzzy logic. his area of interest is in C#.NET, VB.NET and MSSQL 2005.
 
catch me on: http://groups.google.com/group/codewar

Comments and Discussions

 
GeneralSir need more more and more Pinmemberrajeshrisharma4-Dec-09 3:40 
QuestionInteretsing Pinmemberstixoffire12-Jun-09 6:58 
GeneralNice article PinmemberSrinath G Nath19-Feb-09 20:18 
Generalwaiting for your Part II on this post.. Pinmemberlvprasady4-Dec-08 2:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 13 Aug 2008
Article Copyright 2008 by JustChiragPatel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid