To monitor lock waits, use the Lock Information branch of the Performance Monitor window in VDBA. For details, see Viewing Performance Information in online help.

If you find lock waits, identify the queries that are holding locks on the resources you are waiting to access. You must modify your locking strategy to avoid future problems.

Pay particular attention to:

If the lock being waited on was created as the result of lock escalation, your system is configured with too few system-wide locks. This is a configuration issue; see the System Administrator Guide.

If lock escalation occurs because too many locks are taken on a given table’s pages, a SET LOCKMODE statement can be issued to increase this threshold. The default is 10 before escalation occurs. For more information, see the chapter “Understanding the Locking System.”