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.

image

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

image

 

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 ;

image

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.

SQL.BlockedSessions.xml.zip