Problem
@LockTimeout is set but runs show consistent lock timeouts (Error 1222) on >25% of attempts for the same statistic across multiple runs. The timeout value is either too short or there's a persistent blocking workload (index rebuilds, ETL, etc.).
Suggested Fix
Cross-reference ErrorNumber=1222 in #stat_updates with @LockTimeout from #runs. Flag when the same stat times out in 3+ consecutive runs. Differentiate recommendation: increase timeout vs reschedule vs enable parallel mode (workers retry faster).
Example Output
"dbo.Orders.IX_OrderDate hit lock timeout in 4 of 5 runs (@LockTimeout=30s). Persistent blocking detected. Consider: increase @LockTimeout, reschedule to off-peak, or enable @StatsInParallel."
File
sp_StatUpdate_Diag.sql, new check W9 (LOCK_TIMEOUT_INEFFECTIVE)
Problem
@LockTimeoutis set but runs show consistent lock timeouts (Error 1222) on >25% of attempts for the same statistic across multiple runs. The timeout value is either too short or there's a persistent blocking workload (index rebuilds, ETL, etc.).Suggested Fix
Cross-reference
ErrorNumber=1222in#stat_updateswith@LockTimeoutfrom#runs. Flag when the same stat times out in 3+ consecutive runs. Differentiate recommendation: increase timeout vs reschedule vs enable parallel mode (workers retry faster).Example Output
"dbo.Orders.IX_OrderDate hit lock timeout in 4 of 5 runs (@LockTimeout=30s). Persistent blocking detected. Consider: increase @LockTimeout, reschedule to off-peak, or enable @StatsInParallel."
File
sp_StatUpdate_Diag.sql, new check W9 (LOCK_TIMEOUT_INEFFECTIVE)