SQL Deadlock Monitor with Meaningful Alert Details
SQL Management Pack offers extensive monitoring capabilities for monitoring SQL workloads. You need to use the Operations Manager console to utilize the full range of capabilities.
When I visit some customers I realize that DBA’s not always have access to the OpsMgr console and they receive alert notifications mostly by email. If you check some alerts you will find alert description lacking sme important information. In order to get that details you need to open alert context from the OpsMgr console.
Example SQL Blocked Sessions Monitor
This monitor is disabled by default and customers running in house build applications with SQL backend tend to monitor blocked sessions closely. So OpsMgr Admin enables this monitor for the SQL instances. an when a blocking session occurs we have an alert in OpsMgr.
As seen in screenshot we can only see that session 57 is blocked on the default instance on a server. When SQL DBA receives that email they need to go back access the server to see what was that session running and which session is blocking it etc . In fact query running on SQL has all the details and its published under Alert Context tab
Again this information is available only trough OpsMgr console , Alert view.
Here SQL MP runs a SQL query compiles and publishes all information about blocking sessions in property bags and hands then to the monitor. By altering the script slightly we can publish more data and change the alert description to show this information.
Here is the same alert with the modified monitor ;
As seen in the alert details above now blocked session details like program, login id, TSQL and blocking session id are all in the alert description. So now just from the alert description DBA’s can see who is running the blocked query as well as the query and blocking session id.
Attached MP has a new monitor called Blocked SPIDs targeted to SQL DB Engine.