Monitoring Lock Activity in SQL Server

To monitor the performance of the system, it is necessary to keep track of locking activity in SQL Server. Following are the more commonly used methods to do so:

  • Using the sp_lock stored procedure

  • Querying the syslockinfo table directly

  • Viewing locking activity with SQL Enterprise Manager

  • Viewing locking activity with SQL Profiler

  • Viewing the current quantity of locks with Performance Monitor

As you read through the rest of this chapter, you might want to examine or monitor the locking activity for the examples presented. To assist you in that effort, the remainder of this section describes the methods of examining lock activity in SQL Server 2000.

Using the sp_lock Stored Procedure

The stored procedure sp_lock ...

Get Microsoft® SQL Server™ 2000 Unleashed, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.