In this video, I will be discussing IDERA’s SQL Server performance monitoring alerting and diagnostic tool, namely SQL Diagnostic Manager also known as SQL DM. As a tool designed to be used by DBAs, one of the core functionalities of the product is the ability to generate alerts, which allows DBAs to become aware of possible performance issues. Given that this is an important feature sync with SQL DM provides a couple of options as to how alerts are generated. To better explain, let us take a look at the product itself.

As you see, I had the SQL DM desktop client open, and we are currently viewing the SQL DM today view window. On the active alerts tab, you will see the various alerts that have been raised in my environment. All these alerts have been generated based on thresholds that have been configured for each metric that is being monitored by SQL DM. To view the list of metrics and their defined threshold, we will need to navigate to the alert configuration window, which can be accessed a couple of different ways. The first way is to right-click on an alert of interest and select configure alerts. You can see here the alert configurations open. The other way is to right-click on the monitored SQL Server instance name and then again select configure alerts.

As you can see, there is a number of different metrics on which SQL DM can alert upon. Some of these metrics are enabled while others are disabled. If there is a particular metric that you are interested in receiving alerts on, you will want to make sure that it is enabled. By default, these metrics are grouped by category, then by the name of the metric.

Personally, I have difficulty remembering to name the category and prefer an ungrouped view of the list of metrics. By unchecking this group by checkbox, the list of metrics will automatically be updated to be listed alphabetically by the metric name itself. This allows me to quickly and easily find a particular metric that I am looking for rather than having to recall the category name. In this video, however, I really want to talk about configuring thresholds. Generally speaking, thresholds are key to how SQL DM generates alerts. SQL DM uses these threshold settings to determine whether a metric is in an okay informational warning or a critical state.

Let us take a look at the days since last backup metric as an example. Once the metric is selected the right-hand side of the window updates to where you can see the configuration of that particular metric. Clicking on the Edit window will open up a window where I can change the threshold settings. For example, I can change the warning threshold so that SQL DM can alert me when they detected that its database has not been backed up for two days. That is a good example of a static threshold. Next, I want to take a look at baseline thresholds, but first, we will need to talk about baselines.

As a modern tool, SQL Diagnostic Manager gathers and store data in the repository database for historical analysis, such as creating a baseline. Baselines are essentially used to determine what is to be considered normal in your environment. To configure a baseline, simply click on the configure baseline button. On the monitored SQL Server properties window, this is where you can define the baseline period. By default, SQL Diagnostic Manager creates a single baseline from 8:00 a.m. to 5:00 p.m. on normal workdays. However, you may want to create baselines for different periods of the day depending on peak periods. For example, by clicking the manage baseline button opens up the manage baseline window. On this window in my environment, you can see that I have three different baselines created, each for a particular part of the day. For example, this evening option posts from the time period of 5 p.m. to midnight Monday through Friday. I can leverage these baselines when configuring alert thresholds. By doing so metric thresholds are then dynamic, adjusting based on the time of day and the baseline values. This comes in handy when you want to be aware of unusual activity on the SQL Server instance.

For example, let us take a look at the SQL Server CPU usage metric. First, let us cancel out all of these windows and go back to the alert configuration window. I know that the SQL Servers CPU usage metric is in one of the resource categories, so give me a second here and let me find that category. There we go. You can see here how I currently have the SQL Server CPU usage percent metric configured. If SQL DM detects that the CPU usage is 75 or higher, a warning alert will be generated. If SQL DM detects that the CPU usage is 90 percent or higher, it will generate a critical alert. This is great for the normal work hours, however, if during the off-peak hours perhaps 50 percent usage is something to be of concern when the normal trend is probably around 10 to 15 percent, as an example. In situations like that, you would not get alerts when CPU usage is 50 percent during the off-peak hours.

If you are interested in something like that you would want to enable the baseline thresholds and of course, have a baseline configured to cover the schedule that you are interested in. What this will do is could tell SQLDM to alert based on what values we have created or calculated for the baseline value of at that time period. For instance, here, you can see that the warning level is set to a hundred. Now what that is is a hundred percent, so if the CPU usage is 90 and the baseline value happens to be ninety as well, then that would be a hundred percent of the baseline so here we were to create a warning alert. And the same goes for let us say during our off-peak hours. If the normal usage is 10 percent, for example, but we determined that CPU usage is 50 percent well that is well above a hundred percent or even 120 percent which is indicated here by the critical. We would then raise a critical alert. Using baseline thresholds can be a bit confusing when you start off.

It is important to remember the values that you set for the threshold are percentage comparisons to the baseline value. On that note, this wraps up what I wanted to cover regarding alerts in SQL DM. I hope you found this video helpful and please check our website if you would like to learn more about SQL Diagnostic Manager. Thank you.