Summary

• Adds MAXDOP 1 to all 23 SELECT INTO queries that use OPTION(RECOMPILE)
• Eliminates parallel insert plans that cause massive LATCH_EX waits on tempdb allocation pages
• Zero functional change — same results, same RECOMPILE behavior, just serial inserts

Problem

When sp_HealthParser runs with @warnings_only = 1 (the common case for scheduled collection), most SELECT INTO queries produce zero rows. However, OPTION(RECOMPILE) causes SQL Server to generate parallel insert plans for these SELECT INTO statements.

Under any level of concurrent activity, the parallel threads hit tempdb allocation page latch contention (LATCH_EX), even though there's nothing to insert. Actual execution plans from a production collector showed:

253 seconds of cumulative LATCH_EX across 6 queries, with under 1 second of actual CPU work.

Fix

OPTION(RECOMPILE) → OPTION(RECOMPILE, MAXDOP 1) on all SELECT INTO queries. This eliminates the parallel insert operator while preserving RECOMPILE for cardinality-based plan optimization.

Test Results (SQL Server 2022, idle server)

Before (RECOMPILE only):
  Run 1: 54,016 ms
  Run 2: 51,391 ms
  Run 3: 50,891 ms
  Run 4: 50,847 ms
  Run 5: 54,515 ms
  Avg:   52,332 ms

After (RECOMPILE, MAXDOP 1):
  Run 1: 4,516 ms
  Run 2: 4,531 ms
  Run 3: 4,422 ms
  Run 4: 4,719 ms
  Run 5: 5,234 ms
  Avg:   4,684 ms

~10x improvement with zero trade-off — the data volumes from system_health XML (1-hour windows) are too small for parallelism to provide any benefit.

Test plan

• Tested on SQL Server 2022 at idle (results above)
• Test on SQL Server 2019 and 2025
• Test with @warnings_only = 0 to verify results are identical
• Test with @skip_locks = 0, @skip_waits = 0 to exercise all 23 queries

🤖 Generated with Claude Code