Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 20 June 2016

What is waittype and lastwaittype in sys.sysprocesses? How to detect Deadlock in T-SQL?

The master.dbo.sysprocesses table in Microsoft SQL Server 2000 and SQL Server 2005 is a system table that contains information about the active server process IDs (SPIDs) that are running on SQL Server.

If you are using SQL Server 2005, you can also access this table by using the sys.sysprocesses compatibility view.

The waittype column, the lastwaittype column, the waittime column, and the waitresource column in the master.dbo.sysprocesses system table provide information about the resources that the processes are waiting on.

  • waittype: The waittype field is a reserved internal binary column. The value in the waittype field indicates the type of resource that the connection is waiting on.
  • lastwaittype: The lastwaittype field is a string representation of the waittype field. The lastwaittype field indicates the last waittype or the current waittype of a SPID. If the value of the waittype column for an SPID is 0x0000, the SPID is not currently waiting on any resource. In this case, the lastwaittype column indicates the last waittype that the SPID has experienced. However, if the value of the waittype column for an SPID is non-zero, the values in the lastwaittype column and the waittype column for the SPID are equal. These values indicate the current wait state for the SPID.

    Note The UMSTHREAD waittype is an exception to this rule. See the description of UMSTHREAD in the "Other waittypes" section for additional details.
  • waittime: The waittime column provides the number of milliseconds that the SPID has been waiting with the current waittype.
  • waitresource: The waitresource column provides more detailed information about the specific resource that the SPID is waiting on.

T-SQL to Identify/Deadlock in SQL Server:


SELECT *  FROM  sys.sysprocesses
WHERE blocked<>0

Using DMV's:

select * from sys.dm_exec_requests
where blocking_session_id<>0

select * from sys.dm_os_waiting_tasks
where blocking_session_id<>0