There are lots of SQL Server management tools for monitoring your environment—but using ones that keep DBAs like you ahead of the game and proactively find problems before your user base does have greater value. For example, logs and traces give you information about problems that have already occurred, but this is a reactive process. Proactive SQL monitoring that cuts down the time required to find and fix issues before anyone else knows they’re issues is the focus of this tip, centered around SQL Server event notifications.
Event notifications is an event-triggering platform that was introduced with SQL Server 2005. It utilizes events based on data-definition data (DDL) statements and SQL Trace events to feed event data into Service Broker tables, allowing you to process the data however you need based on the event.
Event notifications differ from DDL triggers in that they execute asynchronously. Due to this difference, event notifications can be triggered without affecting the transaction that generated the event. This benefit is realized in two ways: First, this removes the risk that DDL-centric event notifications will cause the triggering transactions to roll back. Second, any performance or resource costs associated with the DDL trigger will be offloaded from the triggering transaction. With SQL Trace and profiler, these events could be collected, but there wasn’t a method to process the data. The bulk of the effort was spent building a process to make the SQL Trace data available to monitor. With event notifications, the data is available as part of the feature. The DBA can concentrate on how to handle the information, instead of how to make it available.
When Deadlocks Strike
While DBAs are not necessarily responsible for the occurrence of every deadlock, we are responsible for detecting and attempting to prevent them from reoccurring. If a developer or business user brings a failed transaction to your attention, having trace flags can give you information on the deadlock via an error log (the reading of which can be painful), but doesn’t arm you very well in terms of proactive deadlock searches.
Even actively monitoring performance counters can leave you wanting. You may be monitoring the perfmon counter deadlocks/sec. so that an alert will be received whenever a deadlock occurs. This will tell you how many deadlocks are occurring and the scope of the problem, but it does nothing about what transactions have deadlocked. Again, you would need to have the trace flags in place to push deadlocks into the error log; which brings you back to the issues with the first SQL Server management scenario.
Resolving deadlocks with event notifications
Fortunately, event notifications provide rich and timely information on deadlocks. The deadlock event can occur, and a process can be built to automatically research and provide information on the deadlock shortly after it occurs.
There are a few steps involved in configuring deadlock monitoring through event notifications:
1. Enable service broker on the database.
2. Create a service broker queue to receive the event notification messages.
3. Create a service broker service to deliver event notification messages.
4. Create a service broker route to route the event notification message to the service broker queue.
5. Create event notification on deadlock event to create messages and send them to the service broker service.
Enabling the capture of deadlocks with event notifications takes a bit more to set up than enabling a trace flag. But in the end, doing this will allow you to be alerted to deadlock events as they happen, providing the opportunity to troubleshoot issues immediately, rather than when end users start to notice them. For an illustrated step-by-step guide to implementing this process—and other valuable tips on using both SQL Server events and extended notification to address a variety of performance puzzles, help yourself to our complimentary practical white paper on the topic.