Query Execution Plan - Basics

There are two types of Execution plan in SQL Server i.e.


  • Estimated execution plan
  • Actual execution plan


Estimated execution plan is generated based on the statistics available for the SQL table columns and indexes. The estimated execution plan may differ from the actual execution plan as the latter is generated when the query is executed and depicts the execution mechanism followed by the server in order to generate the query in the best possible way (least time).

Execution plan is the steps taken by the SQL server optimiser to reach the SQL query results in best possible way. We always have questions such as why our query is running slow, how we can reduce the time to execute the query. The query which is to be executed is submitted to the SQL Server, the query optimiser is responsible for parsing the query and generating the execution plan. When the query is submitted to the system, there are many underlying operation that are happening in the backend before the result is computed.

The Storage engine is the place where index maintenance and table storage occur.
The Relational Engine is where the query execution plan is created.

Before we look into the query execution plan, there are few topics that we need to cover:
  • Brief backgrounder on the query optimiser.
  • Actual and Estimated execution plans – what they are and how they differ
  • Capturing and interpreting the different visual execution plan formats – we’ll investigate graphical plan for a very basic SELECT query
When we submit a query to the SQL Server Database, number of processes on the server go to work on that query. The purpose is that it will provide the data back to us, store it and return the results in a timely manner. These processes are run for each and every query stored in to the system. In the relational engine, the query is parsed and processed by the query optimiser which generates the execution plan. The plan is sent to the storage engine which it then uses to retrieve or update the underlying data. The storage engine is where the activities such as index maintenance, locking and transactions occur.


Query Parsing:

When we pass the SQL query to the SQL Server, the first place it goes is the SQL Relational engine. As the T-sql arrives, it is checked that the SQL query is written correctly. It checks for the possible syntax and for the possible data types that is varchar(50) or nvarchar(25).
When it is a DML statement, it is checked for possible execution plan. Because there is just one possible way to execute a Create table statement. In the DML Statement, it checks for the possible query syntax, columns aliasing since the database server would be unaware of the column names in the SQL query.



Comments

Post a Comment

Popular posts from this blog

Nested Loop Join There are basically three join mechanisms in SQL Server: i. Nested Loop Join ii. Hash Join iii. Merge Join The Nested Loop Join is chosen by the SQL Server when the tables being used are of the comparable sizes and is been the default join mechanism being used. It is non-blocking,  doesn't require equi-join, data to be sorted and doesn't use tempdb. It is mostly suited for inner joins between tables having relatively smaller inputs and having an index on the joining key. Select a.id, b.id from Table1 a inner Join Table2 b on a.id=b.id If we look at its execution plan, it will be using Nested Loop join. Also, In cases where we are having cross join between two tables mostly Nested Loop join will be used by the SQL Execution plan as it is a product join and produces N*M rows. In cases, if we add certain where clause to the Cross Join query to limit our results, Nested Loop Join might not be used. Basic Example: There is a table with fou...
Read more