In every production environment, we have to monitor the various aspects of the SQL instance proactively to avoid any outage. Many times there are requirements like
Whenever the size of database increases more than a specific size- alert me (email/text me)Whenever the procedure cache size increases more than a specific size –alert me
Whenever Page Splits/sec increases more than a specific no. – alert me
and so on.
Today, we are going to talk about how we can achieve this using SQL agent alerts. It comes very handy when we don’t want to write a T-SQL code or use any third party tool.
In SQL server agent we can create 3 types of alerts:
1. SQ: server event alert
2. SQL server performance condition alert
3. WMI event alert
In this blog I will talk only about the second one i.e. SQL server performance condition alert.
To start with SQL server performance condition alert, I am sure everyone would have heard of the DMV sys.dm_os_performance_counters. In this DMV’s output we get the performance monitor data of SQL server. Yes, that’s what SQL agent use/query in the background.
1) Here is how you select this alert: right click on the alert and select new alert.
Then select SQL server performance condition alert.
2) click on the object combo
and select the counter you are looking for – to monitor.
3) click on the counter
4) select the instance for which you want to monitor and by default it’ll be the one from which you are creating the alert.
5) select the condition from the “alter if counter” combo and then set the value.
Once this is configured then the other options which are 1. response 2. options can be configured as mentioned in the article: – http://msdn.microsoft.com/en-us/library/ms191508.aspx
One more important thing which you may be thinking is how this type of alert works. As per my testing on this , I came to know that when we configure the alert it makes an SQL query using the sys.dm_os_performance_counter DMV and runs it every 20s. Once the condition which you had set becomes true then the notifications are sent.
Even, you can execute the job in response to the alert along with the notification.
e.g. if space in the database goes below the threshold , I want to add another .ndf file to the database and also send a notification.