Blocking in SQL Server 2005
Prior to SQL Server 2005, blocking could be detected using
the sp_blocker_pss80 stored
procedure, sp_who2, Perfmon and SQL Profiler. However, SQL Server 2005 has
added some important features to the System Monitor and new DMVs for diagnosing
the same.
- Enhanced
System Monitor counters (Perfmon)
- DMV's: sys.dm_os_wait_stats,
sys.dm_os_waiting_tasks and sys.dm_tran_locks
In System Monitor, the Processes
Blocked counter in the SQLServer:General Statistics object
shows the number of blocked processes. The Lock Waits counter from the SQLServer:Wait
Statistics object can be added to determine the the count and
duration of the waiting that is occurring. The Processes blocked counter gives
an idea of the scale of the problem.
DMV's such as sys.dm_os_waiting_tasks and sys.dm_tran_locks give
accurate and detailed blocking information. The sys.dm_os_waiting_tasks DMV
returns a list of all waiting tasks, along with the blocking task if known.
There are a number of advantages to using this DMV over the sp_who2 or the
sysprocesses view for detecting blocking problems:
In the above screenshot the blocking_session_id shows the blocking process SPID.