Detecting SQL Server 2005 Blocking

  • 發行項

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.

Comments

  • Anonymous
    January 01, 2003
    Hi, SQLDiag should only be executed when you are attempting to troubleshoot an issue. There may be a performance impact depending on the amount of SQL Trace data you are collecting, so try to limit the amount of time you run it. Also, the BLocked Process Report requires that a SQL Trace is running to collect the data, and this may also have an impact on performance. This article has more information on the performance overhead of running a SQL Server trace and some best practices: sqlblog.com/.../trace-profiler-test.aspx However, data collected from the DMV's or Perfmon should not affect performance. Rob

  • Anonymous
    January 01, 2003
    Hi, what other information are you looking for? Thanks!

  • Anonymous
    January 01, 2003
    nice article. a good place to start, when looking for blocking issues for SQL server performances.

  • Anonymous
    October 12, 2010
    hi good one .. but want more informatio ...

  • Anonymous
    January 10, 2011
    will performance be impacted if turned on, or is it best to use when running into repeated issues?