Wednesday, April 25, 2007

TSQL : Blocking & Locking

Tasks waiting right now -
SELECT * FROM Sys.dm_os_waiting_tasks

Tasks being blocked right now -
SELECT * FROM Sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL

Bit difficult to test when your system is running fine, but a query for clearly seeing the details of blocking issues -
SELECT PROCESS_BLOCKING.SPID                 'Holding ID',
RTRIM(PROCESS_BLOCKING.STATUS)        'Status',
'Lock Type' = CASE SYSLOCKINFO.RSC_TYPE
WHEN 1 THEN NULL
WHEN 2 THEN 'DATABASE'
WHEN 3 THEN 'FILE'
WHEN 4 THEN 'INDEX'
WHEN 5 THEN 'TABLE'
WHEN 6 THEN 'PAGE'
WHEN 7 THEN 'KEY'
WHEN 8 THEN 'EXTENT'
WHEN 9 THEN 'RID'
WHEN 10 THEN 'APPLICATION'
ELSE NULL
END,
SUSER_SNAME(PROCESS_BLOCKING.SID)     'Holding User',
SUSER_SNAME(PROCESS_WAITING.SID)     'Waiting User',
PROCESS_WAITING.SPID                 'Waiting ID',
'Database' = CASE
WHEN SYSLOCKINFO.RSC_DBID = 0 THEN '[NULL]'
ELSE DB_NAME(SYSLOCKINFO.RSC_DBID)
END,
SYSLOCKINFO.RSC_OBJID            'Object ID',
RTRIM(PROCESS_BLOCKING.HOSTNAME)      'Holding Host',
RTRIM(PROCESS_WAITING.HOSTNAME)      'Waiting Host',
RTRIM(PROCESS_BLOCKING.PROGRAM_NAME)  'Holding Program',
RTRIM(PROCESS_WAITING.PROGRAM_NAME)  'Waiting Program',
PROCESS_BLOCKING.CMD                  'Holding Command',
PROCESS_WAITING.CMD                  'Waiting Command',
PROCESS_BLOCKING.CPU                  'CPU Time',
PROCESS_BLOCKING.PHYSICAL_IO          'I/O',
PROCESS_BLOCKING.MEMUSAGE             'Mem Usage'
FROM   MASTER.DBO.SYSLOCKINFO
JOIN MASTER.DBO.SYSPROCESSES PROCESS_BLOCKING
ON SYSLOCKINFO.REQ_SPID = PROCESS_BLOCKING.SPID
JOIN MASTER.DBO.SYSPROCESSES PROCESS_WAITING
ON SYSLOCKINFO.REQ_SPID = PROCESS_WAITING.BLOCKED
AND PROCESS_BLOCKING.SPID = PROCESS_WAITING.BLOCKED
WHERE  PROCESS_BLOCKING.SPID <> @@SPID

1 comment:

RCS said...

need to fix it for case sensitive databases:

Here you go:

-- Tasks waiting.

SELECT * FROM sys.dm_os_waiting_tasks
GO


--Tasks being blocked.
SELECT * FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL
GO

--Details of blocking issues.
SELECT PROCESS_BLOCKING.spid 'Holding ID',
RTRIM(PROCESS_BLOCKING.status) 'Status',
'Lock Type' = CASE syslockinfo.rsc_type
WHEN 1 THEN NULL
WHEN 2 THEN 'DATABASE'
WHEN 3 THEN 'FILE'
WHEN 4 THEN 'INDEX'
WHEN 5 THEN 'TABLE'
WHEN 6 THEN 'PAGE'
WHEN 7 THEN 'KEY'
WHEN 8 THEN 'EXTENT'
WHEN 9 THEN 'RID'
WHEN 10 THEN 'APPLICATION'
ELSE NULL
END,
SUSER_SNAME(PROCESS_BLOCKING.sid) 'Holding User',
SUSER_SNAME(PROCESS_WAITING.sid) 'Waiting User',
PROCESS_WAITING.spid 'Waiting ID',
'Database' = CASE
WHEN syslockinfo.rsc_dbid = 0 THEN '[NULL]'
ELSE DB_NAME(syslockinfo.rsc_dbid)
END,
syslockinfo.rsc_objid 'Object ID',
RTRIM(PROCESS_BLOCKING.hostname) 'Holding Host',
RTRIM(PROCESS_WAITING.hostname) 'Waiting Host',
RTRIM(PROCESS_BLOCKING.program_name) 'Holding Program',
RTRIM(PROCESS_WAITING.program_name) 'Waiting Program',
PROCESS_BLOCKING.cmd 'Holding Command',
PROCESS_WAITING.cmd 'Waiting Command',
PROCESS_BLOCKING.cpu 'CPU Time',
PROCESS_BLOCKING.physical_io 'I/O',
PROCESS_BLOCKING.memusage 'Mem Usage'
FROM master.dbo.syslockinfo
JOIN master.dbo.sysprocesses PROCESS_BLOCKING
ON syslockinfo.req_spid = PROCESS_BLOCKING.spid
JOIN master.dbo.sysprocesses PROCESS_WAITING
ON syslockinfo.req_spid = PROCESS_WAITING.blocked
AND PROCESS_BLOCKING.spid = PROCESS_WAITING.blocked
WHERE PROCESS_BLOCKING.spid <> @@SPID
GO