Ways of seeing Query Execution Plans

When it comes to troubleshooting, most of the time, first thing we do is, checking the query execution plan related to the query for understanding what has happened with the query, whether added indexes have been used, JOINs used as we expected, any differences between estimated rows and actual number of rows accessed and, much more. Since Query Execution Plan explains many things, it is always better to understand what it is, how it can be seen and how the guidelines given with it have to be used for troubleshooting. First of all we need to know all possible ways of seeing it, hence this posts speaks about it.

SQL Server allows us to see the plan mainly in three different ways;

• Graphical execution plan
• Text execution plan
• XML execution plan

Most of us are familiar with Graphical Execution Plan as that is what we generally use. However there are some more ways of seeing two types of execution plans;

• Estimated plan - Complied plan for the given request. Result of the request of it is a SELECT or modification related to the request if it is an action query such as INSERT, UPDATE, DELETE, will not be produced or done.
• Actual plan - Complied plan along with runtime statistics. This can be seen with the execution of the request and shown with the result.

Here are the ways of seeing these plans;

• Estimated plan (compile)
• SET SHOWNPLAN_TEXT (deprecated) 
• SET SHOWPLAN_ALL (deprecated) 
• SET SHOWPLAN_XML
• Graphical showplan (SSMS)
• sys.dm_exec_query_plan, sys.dm_exec_text_query_plan
• Actual plan (runtime)
• SET STATISTICS PROFILE (deprecated) 
• SET STATISTICS XML
• Graphical showplan (SSMS)

Let's see each an every way for seeing the execution plan.

SET SHOWPLAN_TEXT

This helps us to see how engine will execute the given query without executing it. The output of this show a hierarchical tree that explains steps taken and actions performed for executing the query. This is not much usable when compared to other ways but good for getting an understanding on each steps and their execution. However, since this has been marked as deprecated, it is not recommended to use.

USE AdventureWorks2014;
GO

SET SHOWPLAN_TEXT ON
GO

SELECT *
FROM Sales.SalesOrderHeader h
 INNER JOIN Sales.SalesOrderDetail d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.CreditCardApprovalCode = '108674Vi12864';
GO

SET SHOWPLAN_TEXT OFF
GO

See more info: https://msdn.microsoft.com/en-us/library/ms176058.aspx

SET SHOWPLAN_ALL

This is a deprecated set statement too. Just like the previous one, this does not show actual statistics but the plan is shown in tabular manner, not as a text like previous one, hence more readable. As you see, it is easy to see logical and physical operations that will be used, estimated rows, estimated CPU, etc.

USE AdventureWorks2014;
GO

SET SHOWPLAN_ALL ON
GO

SELECT *
FROM Sales.SalesOrderHeader h
 INNER JOIN Sales.SalesOrderDetail d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.CreditCardApprovalCode = '108674Vi12864';
GO

SET SHOWPLAN_ALL OFF
GO

See more info: https://msdn.microsoft.com/en-us/library/ms187735.aspx

SET SHOWPLAN_XML

This deprecated set statement allows us to see the estimated execution plan as an XML. It can be open as an XML or graphical plan can be seen too.

USE AdventureWorks2014;
GO

SET SHOWPLAN_XML ON
GO

SELECT *
FROM Sales.SalesOrderHeader h
 INNER JOIN Sales.SalesOrderDetail d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.CreditCardApprovalCode = '108674Vi12864';
GO

SET SHOWPLAN_XML OFF
GO

For more info: https://msdn.microsoft.com/en-us/library/ms187757.aspx

Graphical Showplan (SSMS) - Estimated
This is the simplest way of seeing the estimated execution plan without executing the query. All we have to do is, click the icon called Display Estimated Execution Plan (Ctrl + L) in the SSMS toolbar selecting the query. The plan shows is well readable and provides lot of information on the execution. Each step is shown with a related icon. More information related to them can be seen using the tooltip and details can be seen with property window.

sys.dm_exec_query_plan
This dynamic management function allows us to see estimated plans from the cache. It requires the Plan Handle as a parameter. Required plan handled can be taken from sys.dm_exec_cached_plans dynamic management view. sys.dm_exec_query_plan returns the showplan in XML and just like SET SHOWPLAN_XML, it can be open as a graphical plan or an XML.

-- Cleaning the cache for testing (should not do this in PRD)
DBCC FREEPROCCACHE;
GO

-- Execute a sample query
SELECT *
FROM Sales.SalesOrderHeader h
 INNER JOIN Sales.SalesOrderDetail d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.CreditCardApprovalCode = '108674Vi12864';

-- Checking cached plan and get the handle for above query
SELECT
 cp.plan_handle, cp.cacheobjtype, s.text
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) s
WHERE s.text LIKE '%108674Vi12864%';

-- Get the query plan using the handle
SELECT dbid, query_plan
FROM sys.dm_exec_query_plan(0x0600070027CDCA1D506292630400000001000000000000000000000000000000000000000000000000000000);

Once you have the link like above, it can be open as a graphical showplan or an XML (just like SET SHOWPLAN_XML)

sys.dm_exec_text_query_plan
Just like the previous one, this can be used for seeing the estimated plan for queries. The difference between this and above is, this does not provide a clickable link for opening the graphical showplan. However it can be save as a plan and open it separately as a graphical showplan.

-- Get the query plan using the handle
SELECT dbid, query_plan
FROM sys.dm_exec_text_query_plan(0x0600070027CDCA1D506292630400000001000000000000000000000000000000000000000000000000000000, 0, -1);

SET STATISTICS PROFILE
This can be used for seeing the plan with both estimated values and actual values. Not only that, it execute the query and shows the result too. The output related to the plan is similar to the output generated with SET SHOWPLAN_TEXT.

USE AdventureWorks2014;
GO

SET STATISTICS PROFILE ON
GO

SELECT *
FROM Sales.SalesOrderHeader h
 INNER JOIN Sales.SalesOrderDetail d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.CreditCardApprovalCode = '108674Vi12864';
GO

SET STATISTICS PROFILE OFF
GO

SET STATISTICS XML
This is similar to previous one, only difference is, it shows plan as a clickable XML and it can be used for seeing the graphical showplan as well as XML.

USE AdventureWorks2014;
GO

SET STATISTICS XML ON
GO

SELECT *
FROM Sales.SalesOrderHeader h
 INNER JOIN Sales.SalesOrderDetail d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.CreditCardApprovalCode = '108674Vi12864';
GO

SET STATISTICS XML OFF
GO

Graphical Showplan (SSMS) - Actual
This is most used way of seeing the plan. Since this can be enabled using a toggled icon given with the SSMS, and it shows the plan with actual statistics while executing and producing the result, we can simply use it for understating the query and troubleshooting issues.

In addition to these, we can use SQL Server Profiler and Extended Events for seeing plans too.