G’day,
Are people familiar with the saying “If there’s an elphant in the room, introduce it“? – if not, then to sum up here goes – “An important and obvious topic, which everyone present is aware of, but which isn’t discussed, as such discussion is considered to be uncomfortable”
So, what’s this got to do with SQL SERVER you may ask? Great question.
Well, as a DBA I like to build my own monitoring scripts and programmes to take care of some of my daily task – such as space monitoring, eventlog errors, jobs that fail to complete. You know, the obvious stuff that most DBA’s have probably addressed. I’ve got a development background and so I find the development of my own admin tools to be fun, educational and above all productive.
I’ve got one set of scripts that
- Monitor for deadlocks.
- Monitor for processes that have been blocked for x number of seconds.
- If either of the above 2 things are true then fire off an email to a particular individual or group.
No real surprises there – I’m sure a lot of DBA’s and developers have similar stuff. If’s great to be confident that we’ll be notified about deadlocks or blocked processes before an end user complains (or is even aware that they have taken place). Some applications (I’m thinking 3rd party applications) have re-try logic built into them so that if they encounter a deadlock then they keep on trying the statement that was chosen as the victim multiple times – and it’s amazing to see when these 3rd party databases have encountered deadlocks – I think you might be surprised if you do monitor some of them.
I was asked recently by a dev team to show them how to set this monitoring up as this is a great mechanism for instant feedback during development and testing. So I set about giving a presentation.. but.. (as soon as I started that damn elephant appeared.)
You see the mechanism I use involves “Event Notifications“. Well – as you might be aware – these are built using SQL Trace functionality.
Are you starting to see where the “Elephant in the room” may be yet?
You see SQL trace was put on the “deprecation” track with the release of SQL SERVER 2012 – and if you’ve opened any of the links you’ll see that the version drop down box has no entry for SQL SERVER 2012 – they stop at 2008 R2 (although the same functionality is in 2012 – and should be around for a few versions yet)
So, I’m guessing at this point you are perhaps starting to think why I’ve not mentioned their replacement Extended Events yet – which on the whole are more full-featured and give us greater insight into the workings of SQL SERVER – however they lack one piece of functionality that is available in “Event Notifications”.
Quickly back to my deadlock and blocked process monitoring and the reason that I can be confident that I will get a notification email when either of these events take place is becuase Event Notifications can write to SERVICE BROKER. And a service broker queue can have an activation stored procedure attached to it, in essence meaning that the stored procedure can take action immediately – such as logging the details to a table and sending a notification email.
I could use Extended Events to capture the deadlock graph or the blocked process report and filter them off to a partticular target. The first question is, can I be certain that no events will ever be lost from that target before a notification is sent? The second question becomes “How do I send a notification email”? Well I could have an SQL Agent job to contantly poll the target and respond to an entry by sending an email. This seems kind of untidy to me. I’m introducing more moving parts and the robustness starts to decrease.
Extended Events have been in the product since 2008 and have grown with each iteration of the product – they are great, I love them. Except, Extended Events lack the ability to write to service broker.
The interesting thing here is that there appears to be no plans to add this functionality to the product – there’s a connect article asking for the ability of SQL Audit to be able to write to Service Broker - that’s closed as “Won’t Fix” – and Audit piggy backs off Extended Events.
There’s even a connect item that specifically asked for Extended Events to be given the ability to write to a Service Broker target - at least that is still open. If you are keen on this idea then please vote that item up - I think this would make a lot of sense for the product. I’d be keen to hear your thoughts.
So getting back to my “Elephant in the room” – when I say that my eventing model is based on Event Notifications, which is based on “SQL Trace” – then it’s only fair to point out that this is in fact on the deprecation path. I think most DBA’s / developers can handle that as I’m only advocating that this is used for internal troubleshooting and notification.
In case you’re wondering about the Event Notifications and the eventing model that I use – it’s based on this great article from Jason Strate [blog | twitter]
As an addition, here’s an interesting article from Jonathan Kehayias [blog | twitter] contrasting Event Notifications and Extended Events.
Thanks for reading, have a great day – but please vote that connect item up first.
Cheers
Martin.
