SQL Query Execution Plan

Introduction

Today we will understand the concept of SQL execution plan, which is a vital part of SQL query optimization. Every developer must be aware about these plans. We will be disccussing the following points in this article,

Types of execution plans

Today, in this article we will discuss the following points,

What is an execution plan?
Types of execution plan.
Display options of execution plan.

Let’s start,

What is an execution plan?

It is a graphical representation of the operation performed by the SQL server database engine. Execution plan is generated by query optimizer. It tells us the flow of the query.

Execution plan lets us know how a query will execute on the database engine to return some results.

Types of execution plan

Most people think that there are only two types of execution plans, estimated execution plan and actual execution plan, but that’s not true, there are three types of execution plans as mentioned below,

Estimated execution plan

This execution plan shows us SQL optimizer view. Basically it provides us an estimation of query execution process to get the query results. It is a compiled plan, which means query does not get executed to get execution plan.

To get a query’s estimated execution plan we have to do the following steps,

Switch to SSMS (Sql Server Management Studio)
highlight the query
click on Query
click Display Estimated Execution Plan

Or,

The shortcut for this is Ctrl+ L.

Write a simple select query and press Ctrl+L. You will see the execution plan without a result window (i.e. query doesn't need to execute to get execution plan) as below.

Actual execution plan

The Actual Execution Plan is also compiled but along with its execution context. Execution plan will be displayed only once the query execution has been done. This plan includes actual results along with estimated results.

To get a query’s actual execution plan,

Switch to SSMS (Sql Server Management Studio)
highlight the query
click on Query
Click Include Actual Execution Plan

The shortcut for this is Ctrl+ M.

Write a simple select query, press Ctrl+M and press F5 to execute the query. You will see the execution plan with result window (i.e. query has been executed to get execution plan) as below.

You can simply select the highlighted option from toolbar and press F5 to get the actual execution plan.

For the simple queries, most of the time estimated and actual execution plan results are same, but for the complex queries, it may differ.

Cached execution plan

For the first time when a query gets executed, it is compiled and a query plan is generated for the same. This query plan has been stored in the SQL server query plan cache. Thus, when the same query gets executed again, sql does not need to create the query plan again and take the query plan from sql cache. This results in query performance. Sql server takes less time to execute the query as compared to the first time.

SQL Server Management Studio provides us some views and functions that could be used to know what is in the Cached Execution Plan. Through following views and functions we can view the cache query plan.

sys.dm_exec_cached_plans (View)
sys.dm_exec_sql_text (Function)
sys.dm_exec_query_plan (Function)

Let’s use these views and functions to get some cached information. Write a simple select query and execute. See the screenshot as below:

In the above picture, the first entry is for the query we have executed and the second entry is for the second query, which we executed to get the cached data itself.

Usercounts
I have executed the first query twice, that’s why the value of the usercounts column is two.
Objtype
This column tells us the type of the object.
Text
This column displays the query we have executed.
Query_plan
This column displays the query execution plan. When you click on the this, it will redirect you to the execution plan window.

Clean the cache

You can clean the cache by running the following command.

DBCC FREEPROCCACHE

By clearing the cache you can again get the actual time of query execution.

Display Options of Execution Plans

STATISTICS TIME

Displays the number of milliseconds required to parse, compile, and execute each statement. Time statistics are displayed when SET STATISTICS TIME is ON. In the following picture you can see that just on the time statistics just before select query and you can see the results. Message tells us that query gets executed with 121317 rows.

We can also see how much time CPU taken for this particular query (0 ms in this case). Elapsed time is the actual time taken by the query execution (171 ms in this case).

STATISTICS IO

Displays the IO characteristics. It shows us the information about the disk usage by a statement. Once this option is ON the statistical information displays until the option is set to OFF. Let's check the same in the below figure. You can see, once I is ON the IO statistics, message windows displays the IO characteristics.

Scan counts show number of seeks or scans.
Logical reads shows number of pages read from the data cache.
Physical reads show Number of pages read from disk.
Read ahead reads show the Number of pages placed into the cache for the query and so on.

IO statistics are really beneficial when we have a large amount of data or a large query. We may know which table is sending a large amount of physical reads. If the physical reads is high that mean a lot of physical IO’s are happening to get the data from the hard disk or the memory. In our case here is only 3 physical reads as we have have a simple select query.

STATISTICS XML

We can also display execution plan in xml format by using the following figured query. When we click on the xml it will redirect us to graphical execution plan. This statement executes the query and shows an XML based actual execution plan. This is just like the "Include Actual Execution Plan" option in SSMS.

STATISTICS PROFILE

We can also display a text based execution plan. In this option query also gets executed and displays a text based actual execution plan.

SHOWPLAN_TEXT

We used this alternate option to display a basic text based estimated execution plan, without executing the query.

SETSHOWPLAN_TEXTON

SHOWPLAN_ALL

We used this alternate to display a text based estimated execution plan with cost estimations, without executing the query

SETSHOWPLAN_ALLON

SHOWPLAN_XML

We used this alternate option to display an XML based estimated execution plan with cost estimations. This is similar to the "Display Estimated Execution Plan." option in SQL Server Management Studio.

SETSHOWPLAN_XMLON

Summary

In this article we have learned what is an execution, how many types of execution plans sql has and the display options of the plans. Hope everyone is very well aware now with the execution plan concept. Stay tuned to understand the operations of the execution plan in the upcoming article. Keep Learning SQL Query Execution Plan