Extracting ShowPlan XML from SQL Server Extended Events

By:   |   Comments (2)   |   Related: > Extended Events


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.


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.

ADD EVENT sqlserver.query_post_execution_showplan
ADD TARGET package0.event_file
  SET filename = N'C:\temp\CaptureActualPlans.xel'

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:


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*.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:

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.


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

  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

  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.


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

Comments For This Article

Wednesday, July 21, 2021 - 11:06:30 AM - Franz Renesnicek Back To Top (89034)
Aaron, you failed many attempts to pull the query plan from the event xml because that fragment belongs to a namespace.
When specifying that namespace in the query, you can work around the clumsy cross apply like this:

with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as SP)
select sql_text = t.event_data.value(N'(/event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)')
, showplan_xml = t.event_data.query(N'(/event/data[@name="showplan_xml"]/value/SP:ShowPlanXML)')
FROM #tmp AS t;

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

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


get free sql tips
agree to terms