How can I get the Graphical plan from XML

Question

Post reply

How can I get the Graphical plan from XML

sql-lover

SSCoach

Points: 18530
More actions
March 14, 2016 at 12:33 pm

#324946

I wrote an Extended Events that is helping me to capture the execution plan of an store procedure that needs some tuning. The Extended Events is working, but when I click, it opens a huge XML file. This is how I am querying that:
SELECT
CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('D:\Extended_Events\SP_Exec_0_131024528407210000*.xel', 'D:\Extended_Events\SP_Exec_0_131024528407210000*xem', NULL, NULL)
Does anybody know what functions I need to use so when I click, it will open the graphical plan instead?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you save the xml to a file, with no other changes, save it to the extension *.xdl, ie Deadlocks.xdl.

when you double click that, that extension will open up the graphical view of the execution plan.

i'm betting you can do the same to the row of data, too but not sure how yet.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

sql-lover (3/14/2016)
I wrote an Extended Events that is helping me to capture the execution plan of an store procedure that needs some tuning. The Extended Events is working, but when I click, it opens a huge XML file. This is how I am querying that:
SELECT
CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('D:\Extended_Events\SP_Exec_0_131024528407210000*.xel', 'D:\Extended_Events\SP_Exec_0_131024528407210000*xem', NULL, NULL)
Does anybody know what functions I need to use so when I click, it will open the graphical plan instead?

The trick is that the ShowPlanXML node is not the root of the XML; there's a lot of other information in the XML returned, so it won't get shown graphically. If you're fine discarding the rest of the information, you could just use the XML query method to pull the portion of the extended events XML that contains the ShowPlanXML. That would look something like this:

SELECT your_xml_column.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //ShowPlanXML')

Alternatively, if you are indeed on 2012, you could just read the event file using the GUI. The events with the plan data will actually have a separate tab with the graphical plan. I like avoiding GUIs as much as the next guy, but for this that probably makes the most sense.

Cheers!