May 11, 2009

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


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

0 Responses to "Using T-SQL to Display Execution Plans in SQL Server"
 

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