TopAnswers
Databases
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:
xxxxxxxxxxDROP 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:
xxxxxxxxxx╔═════════════╦════════════════╗║ 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!
Top Answer
This will Work Perfectly®
xxxxxxxxxxWITH 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_countFROM dbo.query_plans AS qp;If you prefer, you could use
xxxxxxxxxxWITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')…and omit the namespace prefix p: from the XQuery expression.
