Lock monitoring - SQL Server 2000

One of the important parts of performance monitoring is monitoring locks. Imagine a situation, where you have a query that just waits for long and
you want to find which query and connection is locking it. One option is to use sp_lock and check which connection holds exclusive lock and then check
use dbcc inputbuffer to find the actual query. Another option is to Activity monitor on the Enterprise manager. Both of these dont directly specify the query responsible for locking. The following script will list the queries along with the locks held by them.


CREATE TABLE #sp_lock_details
  (
     spid     INT,
     dbid     INT,
     objid    INT,
     indid    INT,
     TYPE     VARCHAR(100),
     resource VARCHAR(1000),
     mode     VARCHAR(10),
     status   VARCHAR(50)
  )

INSERT INTO #sp_lock_details
EXEC Sp_lock

DECLARE @id INT
DECLARE @max INT
DECLARE @spid INT
DECLARE @exec_qry VARCHAR(100)
DECLARE @monitored_dt DATETIME

SET @monitored_dt = Getdate()

CREATE TABLE [#sysproc]
  (
     id             INT IDENTITY(1, 1),
     [spid]         [SMALLINT] NOT NULL,
     [dbname]       [NVARCHAR](128) COLLATE sql_latin1_general_cp1_cs_as NULL,
     [cpu]          [INT] NOT NULL,
     [memusage]     [INT] NOT NULL,
     [status]       [NCHAR](30) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [loginame]     [NCHAR](128) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [hostname]     [NCHAR](128) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [lastwaittype] [NCHAR](32) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [last_batch]   [DATETIME] NOT NULL,
     [cmd]          [NCHAR](16) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [program_name] [NCHAR](128) COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     QUERY          VARCHAR(255),
     open_tran      INT
  )
ON [PRIMARY]

CREATE TABLE #dbccinfo
  (
     eventype  VARCHAR(255),
     param     INT,
     eventinfo VARCHAR(255)
  )

INSERT INTO #sysproc
            (spid,
             dbname,
             cpu,
             memusage,
             status,
             loginame,
             hostname,
             lastwaittype,
             last_batch,
             cmd,
             program_name,
             open_tran)
SELECT spid,
       Db_name(MASTER.dbo.sysprocesses.dbid) AS dbname,
       cpu,
       memusage,
       status,
       loginame,
       hostname,
       lastwaittype,
       last_batch,
       cmd,
       program_name,
       open_tran
FROM   MASTER.dbo.sysprocesses
WHERE  spid > 50

SET @max = @@IDENTITY
SET @id = 1

WHILE ( @id <= @max )
  BEGIN
      SELECT @spid = spid
      FROM   #sysproc
      WHERE  id = @id

      SET @exec_qry = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(3), @spid) + ')'

      INSERT INTO #dbccinfo
      EXEC (@exec_qry)

      UPDATE sys
      SET    QUERY = eventinfo
      FROM   #sysproc sys,
             #dbccinfo
      WHERE  @id = id

      TRUNCATE TABLE #dbccinfo

      SET @id = @id + 1
  END

SELECT #sysproc.spid,
       QUERY,
       #sp_lock_details.mode          AS LOCK,
       TYPE                           AS object_type,
       dbname,
       cpu,
       memusage,
       #sysproc.status                AS query_status,
       loginame,
       hostname,
       lastwaittype,
       last_batch,
       cmd,
       program_name,
       open_tran,
       Db_name(#sp_lock_details.dbid) AS locked_object_dbname,
       #sp_lock_details.indid         AS locked_obj_indid
FROM   #sysproc,
       #sp_lock_details
WHERE  #sp_lock_details.spid = #sysproc.spid
ORDER  BY #sp_lock_details.mode DESC

DROP TABLE #dbccinfo
DROP TABLE #sysproc
DROP TABLE #sp_lock_details 




Fairly simple script. Logic is to get the sp_lock details along with sysprocesses details and fire a inputbuffer or each spid.The Script provides the query involved along with the details from sysprocesses and hence it covers all the info provided by Activity monitor and adds a valuable addition by giving the query as well.The script provides the results in descending order of lock mode, so
that 'X' the exclusive lock, which most of us are interested in comes first.

The Script for SQL Server 2005 is even more simpler, which I will post in the next post.