ENHANCEMENT (P2): Add ROWLOCK hint to parallel queue MIN subquery

[nanoDBA]

---

Summary

The MIN subquery used for parallel queue work-claiming (~line 5812) currently uses WITH (READPAST) but does not include ROWLOCK. Under high-concurrency parallel runs (multiple sp_StatUpdate sessions running simultaneously against the same queue), the absence of ROWLOCK allows lock escalation to page or table level, increasing contention and reducing parallel throughput.

---

Current Code (~line 5812)

SELECT MIN(stat_id)
FROM #stat_queue WITH (READPAST)
WHERE worker_id IS NULL

---

Proposed Fix

SELECT MIN(stat_id)
FROM #stat_queue WITH (READPAST, ROWLOCK)
WHERE worker_id IS NULL

Adding ROWLOCK prevents the lock manager from escalating beyond the row level for this read. In conjunction with READPAST (which skips locked rows rather than blocking), this makes the work-claiming pattern more efficient under parallel load.

---

Additional Context

This is a one-token change. The fix is unambiguous and safe — ROWLOCK is a hint, not an enforcement mechanism; the engine will comply where possible but will not error if escalation is unavoidable. The risk of the change is effectively zero.

The debate on severity (whether this matters in practice on most installs) is less relevant than the correctness argument: the hint reflects the intended access pattern (row-level work-claiming, not page-level) and should be explicit. Fix unconditionally.

---

Testing Requirements

• Confirm the hint appears in the MIN subquery after the fix
• Functional test: parallel run with @MaxParallelism > 1 completes without deadlock regression
• No version gate required — ROWLOCK is supported on all SQL Server versions

---

Related

• v2.3 added ROWLOCK to a different work-claiming query; this subquery was missed in the same pass