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!