Detecting Parallel Query Plan Performance Issues In Query Store

Posted on by Erik Darling

Let’s Have Fun

I got asked recently if there’s a way to find parallel queries that are potentially skewed.

One thing that you’ll see quite often is that duration and cpu time aren’t much different. For parallel queries, it should be the opposite — you should be using more CPU in order to reduce duration. Granted, this query could also find queries that were blocked, but it’s a start.

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS x ), 
skew AS 
(
    SELECT qsp.query_id, 
           qsp.plan_id,
           qsrs.avg_cpu_time / 1000. AS avg_cpu_time_ms, 
           qsrs.avg_duration / 1000. AS avg_duration_ms,
           (qsrs.avg_duration - qsrs.avg_cpu_time) / 1000. AS the_difference_ms,
           qsrs.avg_dop, 
           CONVERT(XML, qsp.query_plan) AS query_plan
    FROM sys.query_store_runtime_stats AS qsrs
    JOIN sys.query_store_plan AS qsp
        ON qsrs.plan_id = qsp.plan_id
    WHERE qsp.is_parallel_plan = 1
    AND qsrs.avg_duration >= qsrs.avg_cpu_time
)
SELECT sk.*
FROM skew AS sk
    OUTER APPLY sk.query_plan.nodes('//x:StmtSimple') AS s(c)
WHERE s.c.exist('@StatementType[.= "SELECT"]') = 1
ORDER BY sk.avg_duration_ms DESC;

If you’re wondering why I’m looking at the XML outside a CTE, it’s because Query Store doesn’t store plans in XML, so you get an error. This was the first workaround I found.

Msg 9506, Level 16, State 1, Line 125
The XMLDT method 'nodes' can only be invoked on columns of type xml.

And if you’re wondering how Query Store stores query plans, JOIN THE CLUB:

i quit.

Just when you thought the image datatype was deprecated, huh?

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts