Using T-SQL to Display Execution Plans in SQL Server

The Query Analyzer window in Sql Server Management Studio (SSMS) makes it easy to view exection plans. You have to either click on the 'Display Estimated Execution Plan' or 'Include Actual Execution Plan' as shown below:



However if you want to see the execution plans using T-SQL, here's how to do so:

SET SHOWPLAN_ALL ON/OFF returns an estimated execution plan with detailed information about how the statements will be executed, without
executing the query

USE Northwind

GO

 

SET SHOWPLAN_ALL ON

GO

-- First query.

SELECT CustomerID, CompanyName, [Address] from Customers

GO

-- Second query.

SELECT CustomerID, CompanyName, [Address] from Customers

WHERE City LIKE '%S%'

 

GO

SET SHOWPLAN_ALL OFF

GO

Similarly you can also use SET SHOWPLAN_TEXT ON which returns a textual estimated execution plan without running the query or SET SHOWPLAN_XML ON which returns an XML-based estimated execution plan without running the query.



SET STATISTICS PROFILE ON/OFF returns a detailed actual execution plan for a each query, after running the query.

SET STATISTICS PROFILE ON

GO

-- First query.

SELECT CustomerID, CompanyName, [Address] from Customers

GO

-- Second query.

SELECT CustomerID, CompanyName, [Address] from Customers

WHERE City LIKE '%S%'

 

GO

SET STATISTICS PROFILE OFF

GO

Similarly you can also use SET STATISTICS IO ON to get information about IO/Disk Activity while executing the statements and SET STATISTICS TIME ON to display the milliseconds required to parse and compile each statement while executing it

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