May 30 2011

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.

[sourcecode language="sql"]WITH XMLNAMESPACES(DEFAULT N’http://schemas.microsoft.com/sqlserver/2004/07/showplan’),  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 ;
[/sourcecode]

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.

7 Comments

  • By John Johnson, June 10, 2011 @ 3:33 pm

    Your presentation at the Richmond SQL Server Users Group meeting was excellent. You demonstrated this sql there which I want to try on our systems. However, I get errors when I parse it. Please help.

  • By Grant Fritchey, June 10, 2011 @ 5:15 pm

    What errors are you getting? Oh, and I didn’t mention this but it is looking at one database in particular, which you can see as qp.dbid=13. That’s one issue that you might need to address. Other than that, without the error message, I don’t know what to tell you.

  • By Grant Fritchey, June 11, 2011 @ 6:32 am

    Looks like there’s a formatting issue on the first line. Not sure why it’s doing that, it’s not that way in code. It should read this way:

    WITH XMLNAMESPACES(DEFAULT N’http://schemas.microsoft.com/sqlserver/2004/07/showplan’), QueryPlans
    AS (

  • By John Johnson, June 11, 2011 @ 8:47 am

    It works with the replacement first line. Thanks!

    This is wonderful stuff you’re sharing with us.

Other Links to this Post

  1. Quora — August 19, 2011 @ 8:53 am

  2. Execution Plans, What Do I Look At? | Home Of The Scary DBA — January 30, 2012 @ 9:15 am

  3. Querying Information from the Plan Cache, Simplified | Home Of The Scary DBA — July 2, 2012 @ 8:43 am

RSS feed for comments on this post. TrackBack URI

Leave a comment