x

Query to result XML execution plan in SQL Server 2008 out of SSMS

Hi friends is there a query something like select * from [stored proc] which returns execution plan of SQL statement in XML format...I don't want to use SSMS .
more ▼

asked Aug 16, 2011 at 10:53 PM in Default

pvsrinivasrao gravatar image

pvsrinivasrao
174 26 27 27

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can use the SET SHOWPLAN_XML ON. It needs to be executed as the only command in a batch. Then if you execute subsequent queries, you will receive XML plan for them instead of executing those queries. To disable the XML plan you execute SET SHOWPLAN_XML OFF.

SET SHOWPLAN_XML ON
GO
Your query
GO
SET SHOWPLAN_XML OFF
GO

Details related to the SHOWPLAN_XML are hre on MSDN: [SET SHOWPLAN_XML (Transact-SQL)][1]

[1]: http://msdn.microsoft.com/en-us/library/ms187757.aspx
more ▼

answered Aug 16, 2011 at 11:57 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

This will give you all execution plans in the cache and a lot of statistics about the execution, like total/min/max IO/CPU/execution time etc.:

SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
more ▼

answered Aug 16, 2011 at 11:58 PM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x40

asked: Aug 16, 2011 at 10:53 PM

Seen: 854 times

Last Updated: Aug 17, 2011 at 01:07 AM