Reading an Execution Plan - Part 1

Execution plan is something similar to the Bible, everyone know it is there but very few reads and understand what is in there. We here lot of people saying "Why this query is slow" but if you ask them "Have you look at the execution plan?" you rarely get the answer "Yes".

So first of all what is Execution plan,  in simple terms, it is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. In other words it says how the query was or will be executed in side SQL Server.So when you know how to read it you can find the places which are making the query slow.

Execution Plan Types

When you look at a execution plan of a particular query SQL Server presents two types of plans;

Estimated Execution Plan

When estimated execution plans are generated, the Transact-SQL queries or batches do not execute. Instead, the execution plan that is generated displays the query execution plan that SQL Server Database Engine would most probably use if the queries were actually executed.

Actual Execution Plan

When actual execution plans are generated, the Transact-SQL queries or batches execute. The execution plan that is generated displays the actual query execution plan that the SQL Server Database Engine uses to execute the queries.

Execution Plan Formats

In SQL Server execution plan can be viewed in three different formats;

• Graphical Plan
• Text Plan
• XML Plan

Graphical Plan

These are quick and easy to read but the detailed data for the plan is masked. Both Estimated and Actual execution plans can be viewed in graphical format.

Text Plans

These are a bit harder to read, but more information is immediately available. There are three text plan formats:

• SHOWPLAN_ALL : a reasonably complete set of data showing the Estimated execution plan for the query
• SHOWPLAN_TEXT : provides a very limited set of data for use with tools like osql.exe. It too only shows the Estimated execution plan
• STATISTICS PROFILE: similar to SHOWPLAN_ALLexcept it represents the data for the Actual execution plan

XML Plans

XML plans present the most complete set of data available on a plan, all on display in the structured XML format. There are two varieties of XML plan:

• SHOWPLAN_XML : The plan generated by the optimizer prior to execution.
• STATISTICS_XML : The XML format of the Actual execution plan.

Now let's look at a query and how  it's execution plan looks like. I am using the AdventureWorks sample database for the examples.

Here I am writing a full select on person table. And you can see the Actual Execution Plan below in which it does a full Clustered Index Scan. This is because SQL Server has to return all the values in the table using the clustered index.

Let's write a query which will do a restrict (WHERE filter) to the primary key, now you can see it is using a Seek to the clustered index to get the value.

Scan operations are very costly and Seek operations are performance friends since they use Indexes to get information. Simply a Clustered index scan means a full table scan which is the most costly operation to retrieve data.

Now let's look at how a non-clustered index being used is shown in an execution plan. Below query is asking for the people who has PersonType attribute is set to 'EM'. In the table PersonType column does not have an index so the Query Optimizer has no any other option but to go for a full clustered index scan to get the results.

Now let's ask SQL Server to return people who has first name Alan. Person table has a composite index for FirstName, MiddleName and LastName. As you can see it uses a index scan to on that composite index first and then do a Key_Lookup on the clustered index to present the data.

Let's ask SQL Server to get the people who has name Dylan A Millar. In this scenario it uses an Index Seek on the composite index. Which is fully utilizing the index for optimal performance. In simple terms Index Seek is the most optimal way to access the data but in the previous scenario it had to scan through the whole index since the first name didn't have a  proper index.

In conclusion you must look at the execution plan when optimizing a bad performing query. If a Index is missing you can simply identify it by looking at the execution plan as explained above. We will be discussing more about reading different type of execution plans in the next chapter of this article series.