Using sp_sysmon while tuning lock promotion thresholds

Use sp_sysmon to see how many times lock promotions take place and the types of promotions they are.

If there is a problem, look for signs of lock contention in the “Granted” and “Waited” data in the “Lock Detail” section of sp_sysmon output.

See “Lock promotions” and “Lock detail” in Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon for more information.

If lock contention is high and lock promotion is frequent, consider changing the lock promotion thresholds for the tables involved.