Advanced Monitoring of SQL Server with VBScript on Monitis

MS SQL ServerIn previous articles, we learned how to access Performance Counters metrics, how to access SQL Server data, and also how to add a Monitis page with multiple External Monitors. In this article we will put everything together and create a Monits page with the most important parameters for monitoring an SQL Server in production.
When monitoring an SQL Server installation, it is important to keep monitored the most basic metrics such as Processor and Disk. There are also several important metrics specific to SQL Server, as well as the size and the usage of databases and logs.

The parameters we are going to monitor are:

% Processor Time
Processor is crucial to SQL Server. If this resource usage is constantly above 60-70%, you will have performance degradation. Also check that % Privileged time stays below 10%. SQL Server consumes User Time and if you have high Privileged Time, you will have to check outside SQL Server, such as drivers or virtual machines running on the same hardware.

% Disk time
Your SQL Server should run mostly on memory, as you will not get good performance if the disk is used a lot. If you have a high rate of disk utilization, also check %Disk Read Time. If Read Time values are close to Total Disk time, you are probably missing some indexes in your table and queries are not executing efficiently. In general %Disk time should stay below 50-60% on average. If consistently above and there is no more database optimization that you can perform, then you will have to plan how to separate your databases and logs on different physical disks.

Batch requests per second
This is the number of requests that clients are sending to the server. There is no particular value to look for; it depends on your specific workload. It is important to analyze how this value varies during the day and how this corresponds to the effective, useful work you expect.

User connections
The number of connections to the SQL Server. This number depends on the number of users but also the number of running applications and how they connect to the server.

Buffer cache hit ratio
This value should stay above 90%. If less, consider expanding the physical memory.

Page Look-ups
Page read and Page writes
This correlates to how database pages are requested and physical read/write operations. In an ideal condition SQL Server should work mostly in memory.

SQL Server Memory
This refers to how SQL Server tries to allocate to assigned memory. Target KB is the memory SQL Server is configured to use. Total KB is the memory that SQL Server is currently able to allocate due to competition with other applications on the system.

Database and log size and usage
Knowing how space is allocated and used helps you plan for management of hardware resources and database maintenance.

For some of the metrics we have not suggested a threshold value, and this is because there is no correct value to suggest and everything depends on your specific environment and workload. You will have to let the monitoring run for a couple of days and then use the charts to establish a baseline for your specific environment. Once you have a well established baseline you can set a threshold in order to be alerted when abnormal activities are taking place.

Creating a page with the Custom Monitors

Powerful Monits API allows you to write script that performs a lot of work on your behalf.
The following script creates all the necessary custom monitors and adds them to a new page.

[gist id=1295420]

Uploading values

Values for this script comes from several sources: most from the Performance Counter WMI classes and some from querying SQL Server. See the script below:

[gist id=1295445]

After letting the monitors run for a while, your console will look like this:

Configuring thresholds and notifications

Monitis allows you to configure thresholds on monitored value so you can be alerted if any of the samples is above or below an acceptable value.

In order to be alerted you need to configure at least one email address in your Monitis account:

  1. Click on the Account menu,then Contats, then Add Contact:
  2. Fill the values in the windows that will pop up and then click Add

To configure the notification rules follow these steps:

  1. Move the mouse cursor over the bar at the top of the Custom Monitor you want to configure. A set of icons will appear in the right corner

Click on the orange pencil icon, the widget will expand and some buttons will appear:

  1. Click on the Notification Rules button, another window will pop up:
  1. In Event value configure the threshold value. Choose to alert when the current value is more than, less than or equal to the configured value.
  2. If the chart includes more than one value in Event Parameter choose the value to set the alert for.
  3. In order to avoid being alerted for each transient peak, set a number in “Failures required to trigger an alert”. For example, if your monitoring the Processor with sample rate of 5 minutes, don’t set a 12 in this value because you will be alerted only if the CPU usage exceeds the threshold for longer that one hour.
  4. Click Add Rule and then Done

Just like all our other posts that give advice and instruction on IT tasks, we hope that this information makes your life a little bit easier. Speaking of ease, please take a few minutes to read about how easy we’re making it for Zvi Schreiber of Inc to monitor his company’s virtual computer!