SQL Server Real-time Monitoring using WMI classes: Part -1

We all know that that SQL Server had a great face lift from SQL Server 2000 to 2005 .Everything else was a history after that .It was a big leap and we can count the new features of SQL Server 2005 on fingers. However there is one feature that I feel should have been marketed but was not done.

Starting from SQL Server 2005, we can use SQL Server agent to capture WMI alerts .While this is catching up fast in the SQL Server community, many still do not know what magic this functionality can do and how useful this is.

Let’s say you are facing blocking on one of your very busy database. But how will you know this? When will you know this? What information will you collect? .I have noticed it many time that by the time the DBA team is involved the blocking fades away (I am not saying always).If not we generally find our less skilled DBAs struggling in finding the root cause, until things really go out of control and the senior DBA is called upon at mid night .He joins the call and asks for the so called history of this issue, which frustrates the stake holders … this is a common story my friends. There are lots other.

In this post I will show you how you can leverage SQL Server Agent and WMI to detect the Blocking and send you a mail with blocking graph as an attachment (using database mail) to give you a complete understanding of the reason behind blocking. Even though I wanted to add some more details related to WMI in this post but let me first begin with the demonstration and we will talk about this ni later posts (This reminds me of the dialogue in the movie The Good , The Bad and the Ugly “When you want to shoot , just shoot , don’t talk”) .

Before we begin,

  • Make sure your database mail is working and has an account associated with the Profile.
  • Make sure you are on SQL Server 2005 SP3 or above .I have seen some issues with SQL Server 2005 SP2 and below .You might find some bugs also if you BING .So far I have not found any issues on Build 3054 (which is SP2 + some CU) but it’s better to be on SP3 .
  • You should have admin rights before you deploy the scripts.
  • Ensure that Token Replacement option is checked in the Agent properties :

SQL Server Agent >> Alert System >> Token Replacement >> Check the Replace tokens for all job responses to alerts opton.


  • Set the blocked process threshold value in sys configurations to 5 using sp_configure.This means that we want to capture any blocking that exceeds 5 seconds.

We are all set now to deploy the following script:

Thats It ….

Now you have a mail in your inbox that has :

  • Blocking graph that give you the complete blocking history.Its just like a deadlock graph .You dont need to run Trace all the time and there is no trace flag for capturing blocking .
  • Head blocker list .(You can comment out this code if you don’t need it )
  • information of sp_who and sp_lock of the spids involved in blocking .(You can comment out this code if you don’t need it )
  • Information of the index fragmentation and last update stats date for the objects involved in blocking . (You can comment out this code if you don’t need it )

So next time you dont have to ask too much becasue you have the blocking history straight to your inbox .

Hope you have enjoyed this post .In the next post I will share some more facts and tips on Realtime monitoring through SQL server Agent and WMI ..

As always , your feedback is most welcome .



Abhay Chaudhary

Like us on FaceBook Follow us on Twitter

Follow me on FaceBook | Join the fastest growing SQL Server group on FaceBook