Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

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.

Comments

Posted by Jerrry Brenner on 25 October 2011

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.

Posted by Jason Brimhall on 26 October 2011

Thanks Grant.  I keep planning to get more in depth into this stuff but fail to find the time.  I guess I'll just have to keep loading these articles into my briefcase ;)

Leave a Comment

Please register or log in to leave a comment.