5

My boss wants me to parse a set of query plans stored in a table and to determine how many nested loop operators with unordered prefetching are present in each plan. I only have around 100 query plans so performance isn't very important. I tried doing it myself but quickly got confused and couldn't make progress.

The structure of the table:

DROP TABLE IF EXISTS dbo.query_plans;

CREATE TABLE dbo.query_plans (
plan_name VARCHAR(100),
query_xml XML
);

I uploaded T-SQL to add three example query plans to the table on pastebin. This is the output that I'm looking for:

╔═════════════╦════════════════╗
║  plan_name  ║ OPERATOR_COUNT ║
╠═════════════╬════════════════╣
║ NO_PREFETCH ║              0 ║
║ 1_PREFETCH  ║              1 ║
║ 2_PREFETCH  ║              2 ║
╚═════════════╩════════════════╝

I can't answer any questions as to why I need to do this. Thanks!

9

This will Work Perfectly®

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
SELECT qp.plan_name, 
       qp.query_xml.value('count(//p:RelOp/p:NestedLoops/@WithUnorderedPrefetch)', 'int') AS operator_count
FROM dbo.query_plans AS qp;

If you prefer, you could use

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

...and omit the namespace prefix p: from the XQuery expression.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.