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).

Optimizer Timeouts with XQuery

xraygogsI was looking at performance of a database and I noticed a few of the plans were very large and timing out in the optimizer. This made me wonder, just how many of them were timing out?

This sounds like a job for XQuery!

There’s really nothing to it. Once you start plucking stuff out of the execution plans using XQuery, it’s kind of hard to stop. So here’s my little bit of code.

WITH XMLNAMESPACES(DEFAULT N'<a href="http://schemas.microsoft.com/sqlserver/2004/07/showplan')">http://schemas.microsoft.com/sqlserver/2004/07/showplan')</a>,  QueryPlans
AS  ( 
SELECT  RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)') AS TerminationReason,
        RelOp.pln.value(N'@StatementOptmLevel', N'varchar(50)') AS OptimizationLevel,
        --dest.text,
        SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
                  (deqs.statement_end_offset - deqs.statement_start_offset)
                  / 2 + 1) AS StatementText,
        deqp.query_plan,
        deqp.dbid,
        deqs.execution_count,
        deqs.total_elapsed_time,
        deqs.total_logical_reads,
        deqs.total_logical_writes
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
        CROSS APPLY deqp.query_plan.nodes(N'//StmtSimple') RelOp (pln)
WHERE   deqs.statement_end_offset > -1        
)   
SELECT  DB_NAME(qp.dbid),
        *
FROM    QueryPlans AS qp
WHERE   (qp.dbid = 13 OR qp.dbid IS NULL)
        AND qp.optimizationlevel = 'Full'
ORDER BY qp.execution_count DESC ;

The mandatory warning now, XQuery like this can be somewhat processor intensive. I wouldn’t suggest running this on a production system unless you were to put more filters in place to trim the data down a bit.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.