SQL Server is able to service requests from a large number of concurrent users. When SQL Server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. In SQL Server, a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it. Blocking typically occurs more frequently with increasing transaction volumes.

Fortunately, SQL Server provides variety of different tools, which helps database administrators (DBA) and developers to identifying blocked and blocking processes on SQL Server instance that are listed as follow:

• Activity Monitor
• SQLServer:Locks Performance Object
• DMVs
  • sys.dm_exec_requests
  • sys.dm_tran_locks
  • sys.dm_os_waiting_tasks
• SQL Server Profiler Locks Event Category
• Extended Events
• Event Notifications

Out of all these options Extended Events and Event Notifications are the most efficient and modern way to capture the blocking information.

Event Notifications were introduced in SQL Server 2005 and offer the ability to collect a very specific subset of SQL Trace and DDL Events through a Service Broker service and queue. SQL Server Extended Events (Extended Events) is a general event-handling system for server systems. The key difference between the two features is that Event Notifications allow automated processing of the events asynchronously through service Broker. There is no similar mechanism even in SQL Server 2012 for Extended Events, which is why Event Notifications SQL Trace events still exists for event generation.

As being a fan of Event Notification and service broker, I usually use these two features to proactively monitoring blocked processes information. Check out my article here in which I demonstrated the steps, which you can follow to set-up Event Notification to proactively capture blocked process information.

This article is published on SSWUG.org.