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][1] 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][2]. 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!

  [1]: http://sqlblog.com/blogs/paul_white/archive/2013/08/31/sql-server-internals-nested-loops-prefetching.aspx
  [2]: https://pastebin.com/4e3BhHTC
Top Answer
meme (imported from SE)
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.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.