Hello, and welcome to this video presentation in which we’ll explain how to set custom SQL metrics for monitoring SQL Server.
ApexSQL Monitor collects and displays various SQL Server and Operating system metrics but also allows for creating and adding a custom specific metrics.
To create a custom metric in the main menu of ApexSQL Monitor, click the Configuration link and choose the Custom metrics tab:
Under the Custom metrics page, press the Add button:
The Add custom metric page will open:
Now, let’s create a custom metric with a brief explanation of each option under the Add custom metric page.
In the Name field, set the new metric name, for example, DisabledUsersCount and in the Description box, provide a note about a purpose of the custom metric.
From the Category drop down box, one of the three available performance categories can be selected:
The System performance category provides an extensive selection of the predefined system counters for monitoring Windows OS or some specific part of Windows system.
Under the Type drop down box you can choose one of thirteen available metric groups:
And under the Metric drop down box a various predefined system metric can be chosen depending of what is selected in the Type drop down box:
With SQL Server performance chosen from the Category drop down box, a custom SQL matric will be created which will be executed on the chosen SQL Server instance.
When the Database performance category is selected, then a custom metric will be executed on each database that is in the monitored SQL Server instance.
In our example, from the Category drop down box, we will select SQL Server performance and in the Query text box paste SQL code which will count disabled SQL Server users:
In the Units box, we will enter a measurement counter text which will be displayed in the metric chart.
The Use percent scale for chart (0-100) check box is for the metric that returns a value as a percentage. For our example, we will leave this option unchecked.
In the Period box, enter a time period which will indicate how often the custom metric will collect data from the targeted SQL Server instance. For the purpose of this example, we will enter a period of 60 seconds.
Under the Metric return type section, one of four different methods of how the returned values from the SQL custom metric will be calculated can be chosen.
When the Execution radio button is selected, then the values returned from the executed SQL custom script will be used and displayed in the chart:
If the Delta radio button is selected, then the value that will be shown in the chart is taken as a calculation of the difference between the two executions of the executed SQL custom script:
This calculation is generally used for cumulative metrics when it is crucial to identify pressure on the server, in various periods of time, by visualizing changes between measurements. For example, the calculation of the number of SQL batches SQL Server received.
Similar to the Delta, if the Rate radio button is selected, then the values that will be shown in the chart will be taken as the calculation of the difference between two executions of the executed SQL custom script divided by the time that has passed between those two executions:
This is a metric that is mostly used to detect whether the pressure on the SQL Server is a consequence of rate of change.
When the Execution time radio button is selected, it shows the time required for executing a query:
This is a useful counter type when there is a need for testing the behavior of a query during different periods of the day or week. For example, monitoring round-trip time of a transaction across different periods of time.
For our example, we will select the Execution radio button.
Now, let’s test our SQL code to see if it is working.
Press the Test button.
From the Select tags window, under the Server drop down box, choose a SQL Server instance on which you want to execute the SQL code and press OK:
Next, the following message will appear:
Please note, when creating queries for custom metrics, the query must return a single numeric value; otherwise, the following error message may appear when testing your query:
As you may notice, on the bottom of the Add custom metric page, there is a Trigger alerts check box. When this check box is active, additional options can be set for alerts which will be triggered for the SQL custom created metric depending on results that custom metric returns:
In the Thresholds boxes, enter the threshold values for which different alerts will be triggered.
In front of each threshold, there is a box with different colored squares.
The first square, the blue one represents a low severity alert, the yellow represents a medium and the red represents a high severity alert
In the Trigger alerts for values above defined thresholds and the Trigger alerts for values below defined thresholds radio buttons, you can define when the alerts will be triggered.
Use the Trigger alerts for values above defined thresholds radio button when you the alerts are to be triggered when the values returned from the custom metric are above defined thresholds.
Or, select the Trigger alerts for values below defined thresholds radio button when you want the alerts to be triggered when the values returned from the custom metric are below defined thresholds.
In the Alerts period box, set the duration after which the alert will be triggered:
In order an alert to be triggered, returned values from the custom metric during the specified time in the Alert period box must exceed the defined threshold value.
Now, if everything is set, let’s save our custom metric.
Our metric will now appear in the custom metric table:
All custom metrics from the table can be exported into XML by pressing the Export button and importing them by pressing the Import button:
Now, the newly created custom metric is automatically shown in SQL Server list under the Metric page:
To see the chart and values that this custom metric collects, go to the instance dashboard and, in the SQL Server section, press the Details link:
This will open the SQL Server metric page with our custom metric chart:
To see the measured values better, click on the chart.
This will open the Chart details page in which the measured values can be reviewed in more detail:
This chart can be printed or downloaded as a PNG, JPEG, SVG image or as a PDF document:
Now, let’s go to the Alerts page and see if some alerts for the custom created metric are triggered.
As can be seen, two alerts are triggered. One is medium and the other is a high severity alert:
Thanks for watching. For more information, please visit apexsql.com