Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Extracting ShowPlan XML from SQL Server Extended Events


By:   |   Last Updated: 2016-03-10   |   Comments (1)   |   Related Tips: More > Extended Events

Problem

Recently on Twitter, someone asked how to extract ShowPlan XML from the file target of an Extended Events session. I was surprised to discover how cumbersome this can be, but then again, I've never been a big fan of querying XML in SQL Server. I searched around and found many examples where people were filtering on certain attributes of the ShowPlan document, but none that produced the full XML document as a column. Here's how I approached this problem.

Solution

Before we get into a solution, I want to make it absolutely clear that capturing actual plans can be a substantial burden on your production workload. The following session will capture actual post-execution plans, but I am only advocating using this on a test server. For production, you'll want to do this very sparingly, and in a much more filtered fashion.

CREATE EVENT SESSION [Capture Actual Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan
(
 ACTION
 (
   sqlserver.sql_text
 )
)
ADD TARGET package0.event_file
(
  SET filename = N'C:\temp\CaptureActualPlans.xel'
)
WITH (EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS);
GO
ALTER EVENT SESSION [Capture Actual Plans] ON SERVER STATE = START;

Now that we're collecting data, let's run a query that will generate a post-execution showplan so that it is captured by our Extended Events session. In Management Studio, turn on "Include Actual Execution Plan," then run:

SELECT object_id, name FROM sys.objects;

Next we'll pull the data we've captured. Open a new query editor window (or disable Include Actual Execution Plan), and turn off the Extended Events session using:

ALTER EVENT SESSION [Capture Actual Plans] ON SERVER STATE = STOP;

Querying the file target (or ring buffer) directly can be extremely slow, especially when you start parsing and manipulating the XML. So what I typically do is dump the current event data into a #temporary table first (I describe this in a prior tip, "Query Data from Extended Events in SQL Server"):

SELECT event_data = CONVERT(XML, event_data)
INTO #tmp
FROM sys.fn_xe_file_target_read_file
(
  N'C:\temp\CaptureActualPlans*.xel',
  N'C:\temp\CaptureActualPlans*.xem', NULL, NULL);

Once we have pulled the event data into a #temp table, we can run all sorts of queries against it. First, let's just take a look at what we are dealing with:

SELECT event_time  = t.event_data.value(N'(/event/@timestamp)[1]', N'datetime'),
       sql_text    = t.event_data.value
                     (N'(/event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)'),
       whole_xml   = t.event_data.query('.')
FROM #tmp AS t;

In the output, I can easily spot the row I'm interested in:

Extended Events output

But let's delete the rest of the data before doing anything else:

DELETE t FROM #tmp AS t
WHERE t.event_data.value
  (N'(/event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)') IS NULL;

If we run the above SELECT query again, we should just get a single row. The nice thing about how proper XML renders in Management Studio is that I can click on that blue, underlined XML column and inspect the document closer. We can see that the ShowPlanXML fragment we're after is buried under event -> data [@name="showplan_xml"] -> value:

XML output

What we want to do is just extract that portion of the XML from <ShowPlanXML ...> to </ShowPlanXML>, so that we can run queries against that (say, looking for red flags deeper in the XML), or open it directly for further analysis in SSMS or SQL Sentry Plan Explorer. After many failed attempts at pulling that fragment, here is where I ended up:

SELECT sql_text = t.event_data.value
   (N'(/event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)'),
   actual_plan = z.xml_fragment.query('.')
FROM #tmp AS t
CROSS APPLY t.event_data.nodes(N'/event/data[@name="showplan_xml"]/value/*')
AS z(xml_fragment);

This produces the following, much more useful output:

More targeted XML output

This allows me to click on the XML and it opens a graphical showplan in Management Studio:

Graphical plan

I can also open it in Plan Explorer:

Plan in SQL Sentry Plan Explorer

However, if you look closely, there are a couple of things missing in the plan: the statement and the top operator (SELECT). When Extended Events captures a post-execution plan, the XML is not quite the same as that generated when you include an actual plan in Management Studio. Most notably, the StmtSimple element is missing attribues like StatementText, StatementType, and StatementId, among others.

And to be clear, this problem isn't isolated to Extended Events; the same thing happens with SQL Trace and the Showplan XML event.

This missing data might not be important when you're troubleshooting locally, but if you save that XML or pass it around, you'll need to find a way to ship the statement along with it.

For this simple case, where we have a single row and that row contains a single statement, we can update the plan XML directly. But it is not as straightforward as you might expect, because the xmlns attribute, which dictates the namespace to use for the XML, is non-standard. Functions like modify() do not work against XML fragments with custom namespaces. Therefore, we need to pull the data into a variable, temporarily remove the custom namespace, then put it back after our modifications are finished. To simplify things a bit, I'm going to take our previous query, which pulled sql_text and the ShowPlanXML fragment from the raw XML, and put those into variables.

DECLARE @stmt NVARCHAR(MAX), @plan XML;

SELECT @stmt = t.event_data.value
               (N'(/event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)'),
       @plan = z.xml_fragment.query('.')
FROM #tmp AS t
CROSS APPLY t.event_data.nodes(N'/event/data[@name="showplan_xml"]/value/ *')
AS z(xml_fragment);

-- "hide" the custom namespace via plain text replacement

SET @plan = CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), @plan),
  N'<ShowPlanXML xmlns="', N'<ShowPlanXML fakens="'));

-- add the StatementId attribute and set it to 1

SET @plan.modify(N'insert (attribute StatementId {"1"})
  into (//ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple)[1]');

-- make the top-most operator a SELECT operation, only because we *know* it is

SET @plan.modify(N'insert (attribute StatementType {"SELECT"})
  into (//ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple)[1]');

-- add the sql_text to the StatementText attribute

SET @plan.modify(N'insert (attribute StatementText {sql:variable("@stmt")})
  into (//ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple)[1]');

-- restore the custom namespace via plain text replacement

SET @plan = CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), @plan),
  N'<ShowPlanXML fakens="', N'<ShowPlanXML xmlns="'));

SELECT @plan;

The reason we don't perform brute force plain text replacement everywhere is that @stmt may contain characters that are dangerous for XML, such as <, >, and &. The above method handles entitization properly.

Now we get XML results that, when clicked and opened as a graphical plan, include the statement and the top-most operator:

Graphical plan

And things look much better in Plan Explorer, too:

Plan in SQL Sentry Plan Explorer

In more complex cases, when there are multiple rows, and each row can have multiple plans, statements, and even statement types, it will take quite a bit more XML wrangling in order to make sure each plan has its corresponding statement included (along with other attributes, such as cardinality model and whether the plan was retrieved from cache). Perhaps I'll address this scenario in a future tip.

Conclusion

Querying XML data in SQL Server involves some pretty meticulous syntax. In this tip, I showed how you can extract usable ShowPlan XML fragments from Extended Events data, without inundating you with the dozens of unsuccessful bouts of trial and error I had to endure to get there. I hope this is useful in your troubleshooting efforts, but I want to stress again: No matter what method you use, be very careful about capturing showplan events in production.

Next Steps
Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, March 19, 2016 - 3:08:21 AM - Rekhila Back To Top

Informative blog..You have clearly explained about extracting showplan XML from SQL Server Extended Events..Got an clear idea about it..

 


Learn more about SQL Server tools