query_stats and procedure_stats render plan XML BELOW the TOP: 2,434 TVF executions for 200 kept rows - validated 6x fix from field investigation #1959

Field investigation from the prod-sql-use2 fleet (52 RDS replicas), source-analyzed at 0af464e with live A/B measurements and captured actual plans. Fleet query_stats sweeps: avg 5.1s / p95 9.3s / max 34.4s over 24h, including 30-SECOND COMMAND-TIMEOUT MISSES on the heaviest boxes - lost collection cycles, not just slowness.

Mechanism (from the captured plan, 21,316 cache entries)

The dm_exec_text_query_plan OUTER APPLY is spliced into the FROM, and the optimizer does NOT defer it past the TOP even though query_plan_xml is only referenced in the SELECT list: the TVF sat at node 24, deep below the Top, executed 2,434 times to keep 200 rows - 92% of the plan-XML renders discarded, 5,293 ms of a 6,499 ms plan (81%). The optimizer estimated 3 rows reaching the Sort (46x under) and never considered deferral. The usual suspects (NOT LIKE self-filter 361 ms, sql_text 17 ms, DMV scan 80 ms) were noise by comparison.

The fix, validated on the field fleet

Rank on the cheap DMV columns first in a derived table (inner TOP with headroom, ORDER BY total_elapsed_time), then run the text/plan applies and the NOT LIKE self-filter against survivors only - deferral made STRUCTURAL instead of hoped-for. Measured interleaved on the alpha box: median 7,736 ms -> 1,286 ms (6.0x), TVF executions 2,434 -> 200, row-identity parity 99.2% with the residual fully explained by cache churn between paired runs. The fleet's 30s timeout misses concentrate on exactly the big-cache boxes where discarded renders scale worst - expected to disappear.

Scope and caveats (the reporter's, all sound)

• The big win is Darling-specific (CapturePlanXml=true); Lite's shape pays only the text-apply + NOT LIKE (~380 ms here) - same rewrite applies, smaller win, parity is the rule so both ship together.
• Inner TOP needs headroom over the outer 200 because the self-filter moves post-ranking (240 clean on their alpha; 300 costs nothing - use 300).
• Evicted-text rows excluded identically in both shapes - no parity change.
• The /PLAN_SELECT/ / /PLAN_APPLY/ splice points move to an outer query; the Azure variant gets the same treatment minus the dbid apply.
• procedure_stats has the same defect three times (the apply below TOP(150) in each UNION ALL branch, at module grain 0,-1 where plans are biggest). Honest field numbers: neutral under daytime load (candidates under the TOP), and the 25-26s overnight worst-cases are exactly when candidates blow past it. Apply as structurally-identical tail insurance: no headroom needed (no text filter, all-cheap ranking, exact parity), costs nothing when under the TOP, bounds the tail when not. Implementation note from the field: the outer apply needs the raw varbinary plan_handle carried through the derived table (current SQL only projects the varchar(130) conversion).

Field scripts and four .sqlplan captures preserved at the site (SQLServerSetup/PerfTestingScripts/20260731_darling-querystats-collector-tuning/).