Detecting SQL Server 2005 Blocking

  • Article

Database queries should be able to execute concurrently without errors and within acceptable wait times. When they don't, and when the queries behave correctly when executed in isolation, you will need to investigate the causes of the blocking. Generally, blocking is caused when a SQL Server process is waiting for a resource that another process has yet to release. These waits are most often caused by requests for locks on user resources. A full list of SQL Server wait types can be found here.

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 new tools that adds to this toolkit. These tools include:

  • Enhanced System Monitor counters (Perfmon)
  • DMV's: sys.dm_os_wait_stats, sys.dm_os_waiting_tasks and sys.dm_tran_locks
  • Blocked Process Report in SQL Trace
  • SQLDiag Utility

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, but only provides a summary , so further drill-down is required. 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:

  • The DMV only shows those processes that are waiting
  • sys.dm_os_waiting_tasks returns information at the task level, which is more granular than the session level.
  • Information about the blocker is also shown
  • sys.dm_os_waiting_task returns the duration of the wait, enabling filtering to show only those waits that are long enough to cause concern

The sys.dm_os_waiting_task DMV returns all waiting tasks, some of which may be unrelated to blocking and be due to I/O or memory contention. To refine your focus to only lock-based blocking, join it to the sys.dm_tran_locks DMV.

The SQL Trace Blocked Process Report is another useful way to identify blocking. You can automatically trigger an event when a process has been blocked for more than a specified amount of time. You use the sp_configure command to set the advanced option blocked process threshold to a user defined value:

exec sp_configure 'show advanced options', 1;
reconfigure;
go
exec sp_configure 'blocked process threshold', 30;
reconfigure;

This sets the threshold to 30 seconds. You can then start a SQL Trace and select the Blocked process report event class in the Errors and Warnings group. This article explains the event class in more detail, however it is important to choose the TextData column in order to inspect the contents of the report. The event will fire when a blocked process is detected and the TextData column will return an XML-formatted set of data. Data for the blocked process is shown first, and then the blocking process.

The benefit of the Blocked Process Report is that you have the blocking events recorded on disk in a trace file, along with the time and duration of the blocking. the Threshold option can be adjusted to narrow down the information returned to narrow down the longest ones.

The SQLDiag utility has been enhanced and provides information about your current system. It can run as an executable from the command line or as a service. You can read the output directly, or download the free SQLNexus utility to get reports for waits and blocking. You can also use the Microsoft PSS PerfStats collection of scripts in combination with SQLDiag to get blocking information.