How to parse query plans to get a count of nested loop operators with unordered prefetch? - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

How to parse query plans to get a count of nested loop operators with unordered prefetch?

sql-server add tag

reluctant and angry dba (imported from SE)

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.

1 Answer