How to read Execution Plan

When I saw some junior developer writing SQL query, they always busy to get the correct output form there query. Not even thinking about the performance of the query. When they grow up, then they can understand the real fact of performance. It is the key factor of any query execution. How much times it takes to execute. It is going harder to header to learn, how to improve the execution time of a query. I saw many senior people to think about it. The execution time improvement is really a nightmare to us. So, to improve the performance of the query, we must know where we made the mistake and rectify those things that are all.

So I decide to write an article related to execution plan. I try to make it simple that anyone can understand it. Before writing the article, I summarized the facts by reading different articles.

How the execution is happening

To understand the execution plan, we must know how, how the execution is happening. Basically SQL query will be parsed "Top to bottom and Left to right".

Whenever we execute a SQL statement the SQL server will give output by processing the query into two phases.

1.    Relational Engine

2.    Storage Engine

First the SQL statements will be check against syntax called "Parsing" and the output of this process is in the form of tree called "Sequence Tree".  Then the process checks all the table names, columns names, their data types in DML statements and verify against database called "Algebrizer".  Then the query process tree goes to the "Query Optimizers", where software will calculate the calculated execution path based on statistics available in the database. Then the optimiser gives some number to each step called "Estimated Cost" for preparation of execution plan. SQL server check the availability of the execution plan is called "Plan Cache". Then the plan will be given to the storage to actually execute the query. It is only the "Estimated execution plan" not the "Actual execution plan". Actual execution plan which will be taken out during the process and stored engine. 

The Actual execution plan is cache by stored engine because the plan is used by query engine.

So the "Actual Execution Plan" is more corrects then the "Estimated Execution Plan".

Reading the Execution plan

The graphical plans are read from right to left and from top to bottom. Each icon represents an operation. Some icons are same as estimated and actual execution plan and some are different.   Each operator is connected by an arrow that represents the data feed. The thickness of data feed are different, dependent on amount of the data it represents. Thin rows represent the fewer rows and thick one represents more rows.

The SQL Statements

SELECT  soh.[SalesOrderID], soh.[OrderDate], soh.[ShipDate], sod.[ProductID],

        sod.[OrderQty], sod.[UnitPrice], soh.[CustomerID]

FROM    [Sales].[SalesOrderHeader] AS soh

        INNER JOIN [Sales].[SalesOrderDetail] AS sod

ON soh.[SalesOrderID] = sod.[SalesOrderID] WHERE soh.[CustomerID] = 29559;

The mentioned SQL statements represent the following execution plan.

Starting at the right and the top you see an Index Seek (NonClustered) against the index named [SalesOrderHeader].[IX_SalesOrderHeader_CustomerId]. This feeds data out to a Nested Loop (Inner Join). Working down we can see a Key Lookup (Clustered) operation against the PK_SalesOrderHeader_SalesOrderID. This is a classic key lookup, or what used to be called, a bookmark lookup. We can see that the data feeds back up to the Nested Loop and then that feeds on down to another Nested Loop operator. Below that is a Clustered Index Seek (Clustered) against the [PK_SalesOrderDetail_SalesOrderId] primary key. Finally the data flow goes out to the SELECT operator. That's the basic information available within the execution plan. Lots more detail is also available.

Hover with the mouse over one of the operators and you will get a tool tip, different for each operation type, showing some of the detail behind the operator. Displayed below is the tool tip for the Key Lookup operator:

At the very top of the tool tip is a description of the operator. In this case, "Uses a supplied clustering key to lookup on a table that has a clustered index." Most operators will include this description, telling you what the operator does within the execution plan. After that, most operations will have a varying number, and type, of fields within the tool tip, supplying different kind of information. An example of one of the common fields is Estimated Operator Cost. You'll see this in most tool tips for most operators. A piece of information that is specific to this operator (although not unique to this operator) is the Seek Predicate information at the bottom of the tool tip.

But the most interesting piece of information for the Key Lookup operator is that it exists within this execution plan. It exists because, while the index on CustomerID is sufficient to get a specific set of rows returned to the application, all the columns needed are not contained on the index. Because the data is stored on the clustered index, and additional set of seeks are required to retrieve the data, which is joined with the information retrieved from the index on CustomerID through the Nested Loop join operation.

To see even more information about the operators in the execution plan, right click an operator and select "Properties" from the drop down menu. This will open a complete properties sheet. Much of the data on the properties sheet is the same as that available in the Tool Tip, but even more is on display in the property sheet.

Show Execution plan displays different type of icons for different takes. 

For that please refer to the MSDN. http://msdn.microsoft.com/en-us/library/ms175913.aspx

Hope that the article is quite informative and thanking you to provide your valuable time on it.

Posted by: MR. JOYDEEP DAS