Extracting ShowPlan XML from SQL Server Extended Events

By: Aaron Bertrand | Comments (2) | Related: > 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:

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:

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:

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

I can also open it in 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:

And things look much better in Plan Explorer, too:

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

See these related tips and other resources:
About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Extracting ShowPlan XML from SQL Server Extended Events

Problem

Solution

Conclusion

Next Steps

About the author

Comments For This Article