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:
Just when you thought the image datatype was deprecated, huh?
Thanks for reading!
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.