What is an Execution Plan ?

July 17, 2017 Execution Plans 1901 Views

/_include/blog-images/article-images/what-is-execution-plan-cartoon.jpeg

An Execution Plan is the road-map to execute a query. It determines the objects involved in the execution of the query and the sequece that the objects are accessed, and a lot more things.


Let's see an example:
Consider the query below:
SELECT *
FROM Customers as c
WHERE c.CustomerID = 'GREAL'

Based on the server and database conditions, SQL Server generates a propper execution plan. Below is one possible execution plan that SQL Server might generate for the query above.

Sample Execution Plan

What is inside Execution Plan?
Execution Plan is SQL Server's road-map to execute a query. The information found in the execution plan includes:
  • objects and tables that are accessed
  • the way objects and tables are accessed
  • the sequence in which objects and tables are accessed
  • IO cost of each operation
  • number of rows each operation effects
  • and a lot more

Having a good understanding of Execution Plans can dramatically help determine the bottlenecks of a query and optimize for speed.

Execution Plan types

There are two types of Execution Plans:

1. Estimated Execution Plan
2. Actual Execution Plan

The biggest difference between the two types of execution plans above is about the time SQL Server generates the plan. Estimated Execution Plan is a plan that SQL Server generates without executing the query, hence the name estimated.

On the other hand, Actual Execution Plan is the plan that is generated when SQL Server executes a query and specifies the route that SQL Server actually went through to execute the query. It is possible that the two types of Execution Plans differ.

The Estimated and Actual execution plans above have a lot in common in terms of the structure and appearance. In summary, you can get an estimated road-map of a query execution without executing that query. This Estimated Execution Plan comes in handy when you cannot run queries.

You might be wondering why you cannot run a query on database. As an example, you might need to get the execution plan of a query that contains an UPDATE statemant. Therefore it is not always possible to run the query on a production database to get the execution plan. In such cases, Estimated Execution Plan can be generated without running the query.

Execution Plan formats

Execution Plans can be output from SQL in three formats:

1. Graphical format
2. Text format
3. XML format

All the formats above represent the same data, just in different formats.

Graphical Execution Plan

In order to view the graphical Estimated Execution Plan in SQL Server Management Studio, use the Ctrl+L or click on the relevant button as shown below:
Show Estimated Execution PlanInclude Estimated Execution Plan

By clicking on the button or pressing Ctrl+L, SQL Server tries to generate the Estimated Execution Plan for the query in the Query Window.

Let's see an example:
Consider the query below. We are to see the Estimated Execution Plan for this query.
SELECT *
FROM Customers as c
WHERE c.Country = 'France'
ORDER BY c.ContactName

By pressing Ctrl+L, the following Estimated Execution Plan appears in the Management Studio.
Estimated Execution Plan

In order to view the graphical Actual Execution Plan in SQL Server Management Studio, use the Ctrl+M or click on the relevant button as shown below:
Show Actual Execution Plan Include Actual Execution Plan
By clicking on the button or pressing Ctrl+M, SQL Server will also output the Actual Execution Plan for any query it will execute afterwards. Consider the same query above, if you press Ctrl+M and execute the query, Management Studio will output the Actual Execution Plan. In this particular case, the Actual Execution Plan is exactly the same as the Estimated Execution Plan.

How do you rate this topic?


Further reading:
How to read Execution Plans
Actual vs Estimated Execution Plans
What does arrow thickness mean in Execution Plans?
Blocking vs Non Blocking operators
Actual Execution Plan hides some queries!!!