ENHANCEMENT (P3): Add ROWLOCK hint to reduce lock escalation in parallel UPDATE STATISTICS

[nanoDBA]

Problem

Under heavy parallelism, UPDATE STATISTICS can acquire row locks that escalate to table locks under memory pressure. This is non-deterministic and rare, but possible in high-concurrency environments.

Context

• Parallel statistics updates divide table into worker threads
• Each worker acquires row locks for sampling
• Memory pressure can trigger lock escalation to table level
• This is rare in practice but theoretically possible

Solution (Post-Release)

Add ROWLOCK hint to internal scan operations (if UPDATE STATISTICS API supports it). Goal: pin granularity at row level, prevent escalation.

-- Pseudocode: UPDATE STATISTICS with ROWLOCK hint
UPDATE STATISTICS table_name WITH FULLSCAN
    -- (if hint is supported)

Why Post-Release

1. Production testing shows zero lock escalation in parallel runs
2. Timing overhead ~2-5% (negligible but real)
3. This is defensive optimization, not a bug fix
4. Post-release backlog OK

Test Vector

• Run sp_StatUpdate on large table (>1M rows) with @ParallelDegree=4
• Monitor sys.dm_tran_locks during execution
• Verify no lock escalation events (LCK_M_* wait types)
• Measure timing difference with/without ROWLOCK (if implemented)

Confidence

Medium-high. Paul White + Brent Ozar both agree P3 is correct given empirical evidence (no observed escalations, <5% overhead).

Priority

Post-release backlog. Not critical for v2.4 ship.

[nanoDBA]

Invalid. SQL Server's UPDATE STATISTICS command does not support lock hints like ROWLOCK. Adding one would produce a syntax error. Lock behavior during UPDATE STATISTICS is controlled by the engine (Sch-S lock). The ROWLOCK hints in the proc correctly target only the parallel queue table (QueueStatistic), not the stat update commands themselves.