Home > SQL Server > SQL SERVER – SET SHOWPLAN_XML – SET Statement

SQL SERVER – SET SHOWPLAN_XML – SET Statement


SET SHOWPLAN_XML : This set statement is used to get the detailed information about the transact SQL statement’s execution (execution plan) without executing the statements. If we set this set statement to ON then the SQL Server returns only the details of the T-SQL statements query plan for the current session until it is turn OFF.

SQL Server returns XML execution plan output of type nvarchar(max). The XML schema definition can be download from this link.

Note: SHOWPLAN_XML statement cannot be used inside the stored procedure.

Usage

SET SHOWPLAN_XML ON

or

SET SHOWPLAN_XML OFF

Example

when ShowPlan_XML is ON,

pic1If we click the XML link, graphical query execution plan will be shown in a separate window.

pic2Dynamic Management View

Query plans can be obtained using the DMV sys.dm_exec_query_plan

Example

select * from sys.dm_exec_query_plan
((select plan_handle from sys.dm_exec_requests where session_id = {User Session ID}))

pic3

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: