SQL Server XQuery Against Execution Plans

One of the greatest things about all the DMOs is how you can combine the information they present to you with execution plans. It allows you to see what a query is doing, even as it’s executing, because the plan is created first. I couldn’t possibly emphasize enough how important that’s going to be in your day-to-day troubleshooting.

Even better is the fact that you’re going to be able to make use of XQuery to pull useful information out of the execution plans that are in cache.

Or are you?

Here’s a query to pull some information out of the procedure cache:

SELECT  deps.type_desc,
deps.last_execution_time,
deps.execution_count,
deps.total_logical_reads,
dest.encrypted AS EncryptedText,
dest.text,
deqp.query_plan,
deqp.encrypted AS EncryptedPlan
FROM    sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
WHERE   dest.text LIKE 'CREATE PROCEDURE dbo.GetSalesDetails%' ;

When I run this, it has just two (2) reads and completes in about 38ms on my system. It works great. But what if I were interested in seeing the operators in this plan and the costs associated? Very straight forward. This would do it.:

SELECT  Xplan.p.value('declare default element namespace "<a href="http://schemas.microsoft.com/sqlserver/2004/07/showplan">http://schemas.microsoft.com/sqlserver/2004/07/showplan";</a>
@NodeId', 'int') AS NodeID,
Xplan.p.value('declare default element namespace "<a href="http://schemas.microsoft.com/sqlserver/2004/07/showplan">http://schemas.microsoft.com/sqlserver/2004/07/showplan";</a>
@PhysicalOp','varchar(50)') AS PhysicalOp,
Xplan.p.value('declare default element namespace "<a href="http://schemas.microsoft.com/sqlserver/2004/07/showplan">http://schemas.microsoft.com/sqlserver/2004/07/showplan";</a>
@EstimatedIO','decimal(7,6)') AS EstimatedIO,
Xplan.p.value('declare default element namespace "<a href="http://schemas.microsoft.com/sqlserver/2004/07/showplan">http://schemas.microsoft.com/sqlserver/2004/07/showplan";</a>
@EstimatedCPU','decimal(7,6)') AS EstimatedCPU,
dest.text,
deqp.query_plan
FROM    sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
CROSS APPLY deqp.query_plan.nodes('declare default element namespace
"<a href="http://schemas.microsoft.com/sqlserver/2004/07/showplan">http://schemas.microsoft.com/sqlserver/2004/07/showplan";</a>
//RelOp') XPlan (p)
WHERE   dest.text LIKE 'CREATE PROCEDURE dbo.GetSalesDetails%' ;

Here’s where it gets fun though. First, this doesn’t show me the reads & stuff. But it does show me that it’s taking 160ms to return five rows (five operators, five rows). Which seems like an extraordinary long time until you look at one little part of the XQuery: ‘//RelOp’

The // is basically like putting % into a LIKE clause (see this post and the discussion). It’s a wild card search. Go through the whole structure and find the operators, RelOp. That’s a very time consuming and expensive mechanism for retrieving XML data. Unfortunately, because of how the XML is structured, sort of mirroring how the plans operate, the operators are nested inside of each other. Which means you can’t know the exact path to the operators unless you already knew the plan. This means you’re going to be doing wild card searches against your XML.

You might think to yourself, “Fine. No biggie, it just takes a little longer.” And that would be true, except, I’m looking at a plan with five operators. Examine your plans. How many of the problematic ones, the ones you’re going to want to query contain just five operators? And now you begin to see the issue. As the plans get bigger and/or, you’re unable to filter as many as possible from the query, the queries get slower and slower. They can, and probably will, affect your production server and it’s performance.

What to do? Ah, well, that’ll have to wait for the next blog post on this topic. I’m working on a good solution now.

6 thoughts on “SQL Server XQuery Against Execution Plans

  • Yeah, the memory cost and the cpu cost for XQuery are rather high, but it’s when you have to use wild cards to query that it just becomes too painful. I’m working on something. We’ll see how it goes. I’ll post it if it fails or succeeds.

  • 2005? Isn’t that out of support already? I was hoping to have it working for 2012. Ha!

    Seriously, I’m going against the procedure stats, but just change it to the query stats and you’ll be fine: sys.dm_exec_query_stats.

  • Jerry Brenner

    We capture the top 400 queries, by total elapsed time. We do this from our app server, as opposed to sql scripts, which gives us the ability to do a lot of post processing within the app server. Rather than use XQuery embedded in SQL, we pull back the showplan xml for each of the query plans as text. For each plan, we build a DOM and then run xpath expressions against each query plan to put out the RelOps, accessed objects, etc. There’s a ton of useful information in the plan beyond the physical (and logical) operators. This allows us to incrementally increase the information that we analyze, without adding load to the database.

    A problem that we don’t have a solution for is building a string representation for arbitrary seek and scan arguments.

    (I first posted this on SQLServerCentral.com, before realizing that I should post it here. Sorry for multiple posts.)

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.