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! : http://sqlblog.com/blogs/paul_white/archive/2013/08/31/sql-server-internals-nested-loops-prefetching.aspx : https://pastebin.com/4e3BhHTC
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.