Spotlight on SQL Server Enterprise: Just the Facts! Part 4: What is the Alarm Setting?

As we continue our navigation of Spotlight on SQL Server Enterprise and we can diagnose an alarm that triggers. Let us see what setting caused an alarm to trigger. The fact is when you start monitoring an Instance it is assigned the preset “Factory Settings. Those settings may need to be adjusted it may be a good practice to familiarize yourself with the setting values and determine if you should adjust them to your environment.

The fact is Alarm settings can be altered at an individual Instance or managed by the use of an alarm Template. I will demonstrate the ability to alter an alarm for and individual Instance. Remember if this alarm setting needs to be applied to more than one instance then the use of an Alarm Template would be recommended.

The question is what is the best way to determine the Alarm setting? I found that the display of “Spotlight Today” to be very versatile. When an alarm is triggered I can see what setting or level of severity triggered the event. Let me walk you through that process:

First let’s open the Spotlight Client on my desktop.

You will be presented with the HeatMap display. Above the HeatMap are four tabs, select the tab “Spotlight Today”. Your display will change to a list of Alarms sorted by connections and list by severity.

You can alter “Spotlight Today” display by selecting the “Group By” field on the navigational ribbon. Now the alarms will be listed by alarm type and sorted by severity. The drop down arrow next to each alarm will provide you a list of instance experiencing the alarm. The fact is we can now navigate to the alarm to determine the setting that is causing this alarm to trigger.

Above I selected this alarm and by the use of a right click of the mouse I can confidently display the alarm value. In this case I choose the “Memory – Page Life Expectancy”.

Selecting “Alarm Setting” you will be presented the above dialog. This is the alarm for just this Instance which will allow you to see and familiarize yourself with the alarm values. Just select the edit indicator next to the Alarm Name.

Now you have the ability to determine if this alarms value is appropriate for this instance or an adjust needs to be applied. Always be cognizant if you make any changes it will only be applied to instance you selected. If you need or determine that this value should be applied to multiple instances please use the “Alarm Template” mechanism provided in the solution.

Now that we have focused it on the facts, have been able to diagnose the issues and determine the alarm settings. We need to decide what the appropriate course of action and that may take time depending on what is involved. The next part I will discuss the ability to snooze and alarm.