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