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

avatar image

pvsrinivasrao
174 26 27 30

(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)

more ▼

answered Aug 16, 2011 at 11:57 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(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

avatar image

Håkan Winther
16.4k 36 45 57

(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.

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:

x2070
x57

asked: Aug 16, 2011 at 10:53 PM

Seen: 1030 times

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

Copyright 2016 Redgate Software. Privacy Policy