SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Monitoring Blocked Processes with Event Notifications

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:

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.

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.


Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...