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.
T-SQL to Identify/Deadlock in SQL Server:
1)
SELECT * FROM sys.sysprocesses
WHERE blocked<>0
Using DMV's:
1)
select * from sys.dm_exec_requests
where blocking_session_id<>0
2)
select * from sys.dm_os_waiting_tasks
where blocking_session_id<>0
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:
1)
SELECT * FROM sys.sysprocesses
WHERE blocked<>0
Using DMV's:
1)
select * from sys.dm_exec_requests
where blocking_session_id<>0
2)
select * from sys.dm_os_waiting_tasks
where blocking_session_id<>0
No comments:
Post a Comment