SQL Server: Display Query Execution Plans–Different ways

A SQL Server Query execution plan can return detailed information about how the statements are executed. Execution Plans can be viewed in different ways and formats. In this article, we will discuss a couple of options using which you can view the execution plan of a query.

Method 1: Using SQL Server Management Studio (SSMS)

Just highlight a piece of code in SSMS, right click and select ‘Display Estimated Execution Plan’

OUTPUT



Method 2: USE SET statements

You can use SET statements with options like showplan_text, showplan_all, showplan_xml

showplan_text



OUTPUT



showplan_all

Similarly the SHOWPLAN_ALL option displays the same query plan as the SHOWPLAN_TEXT option, but it also provides additional columns of output, for each row of textual output

SET SHOWPLAN_ALL ON;
GO
SELECT *
FROM Production.Product
WHERE ProductID = 526;
GO
SET SHOWPLAN_ALL OFF;
GO



showplan_xml

To view the results in an XML format, use SHOWPLAN_XML

SET SHOWPLAN_XML ON;
GO
SELECT *
FROM Production.Product
WHERE ProductID = 526;
GO
SET SHOWPLAN_XML OFF;
GO

The result can be huge, so just right click the result > copy and paste in a notepad or XML editor to view the results.

Method 3: Use Plan Cache

You can also use the plan cache to find the execution plan of a query. Use the dm_exec_cached_plans DMV which returns a row for each query plan that is cached by SQL Server for faster query execution.

USE master;
GO
SELECT *
FROM
sys.dm_exec_cached_plans AS cp
CROSS APPLY
sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);
GO

The query above retrieves the plan handles of all query plans in the cache by querying the sys.dm_exec_cached_plans dynamic management view.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook