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.
Use Monitor Server or the monitoring tables to see how changes to the lock promotion threshold affect the system at the object level.
