Use sp_sysmon to see how many times lock promotions take place and the types of promotions they are.
See “Lock promotions” on page 276 in the Performance and Tuning: Monitoring and Analyzing for more information.
If there is a problem, look for signs of lock contention in the “Granted” and “Waited” data in the “Lock Detail” section of the sp_sysmon output.
See “Lock detail” on page 272 in the Performance and Tuning: Monitoring and Analyzing for more information.
If lock contention is high and lock promotion is frequent, consider changing the lock promotion thresholds for the tables involved.
Use Adaptive Server Monitor to see how changes to the lock promotion threshold affect the system at the object level.
