September 12, 2011

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’

display estimated execution plan

OUTPUT

display execution plan

Method 2: USE SET statements

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

showplan_text

showplan_text

OUTPUT

showplan_text

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_all

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


showplan_xml

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.

sql plan cache


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

1 Response to "SQL Server: Display Query Execution Plans–Different ways"
  1. Pinal Dave said...
    September 12, 2011 at 2:32 AM

    Excellent Article - so simple and easy to understand.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions