For extensive monitoring, create custom SQL Server metrics.

This post will show you how to take it a step further by collecting custom SQL Server metrics so you can track and create the functionality you want. When the Agent conducts its built-in SQL Server check, you can configure it to collect custom metrics and report them.

We’ll teach you how to collect and track custom metrics in three different ways:

1. Adding performance counters that aren’t included by default in SQL Server integration queries
2. Using a stored procedure that the user has defined
3. Integration with Windows Management Instrumentation (WMI)​

Custom metrics with the performance counters view

Even though the Agent now collects a range of essential data, you may be interested in monitoring other performance items such as page lookups, log flushes per second, or queued requests. By performing the following query, you can get a list of all the performance counters you can track:

SELECT counter_name, instance_name, cntr_value FROM sys.dm_os_performance_counters; 

You’ll see something that looks like this:

counter_name instance_name cntr_value -- Page lookups/sec 30617439 Log Flushes/sec
tempdb 5664 Log Flushes/sec model 7 Log Flushes/sec demo_db 15152 Queued requests internal 0 

Edit the SQL Server file type to collect metrics automatically from particular performance counters. Create a custom metrics entry for each metric you want to track. By adding the following setup, we can gather the metrics “Page lookups/sec,” “Queued Requests,” and “Log Flushes/sec,” as well as “Index Searches/sec.”

 # ... custom_metrics: - name: sqlserver.buffer.page_lookups counter_name: Page lookups/sec - name: 
sqlserver.workload.queued_requests counter_name: Queued Requests instance_name: internal - name: 
sqlserver.databases.log_flushes counter_name: Log Flushes/sec instance_name: ALL tag_by: db - name:
sqlserver.index_searches counter_name: Index Searches/sec # ... 

You must provide values for name and counter name for each entry. The name value maps to the counter name column of sys.dm os performance counters, whereas the counter name maps to the metric’s name. The configuration above will cause the metric to appear as sqlserver.buffer.page lookups in the case of “Page lookups/sec.”

Can use the instance name field of sys.dm os performance counters. To find out what instance name means in the context of the performance objects you’re interested in, consult the documentation for those objects. Log Flushes/sec is a counter in the object SQLServer:Databases in our example. Other performance objects, such as the Buffer Manager object, which contains Page lookups/sec, have only one instance.

You have two alternatives for transmitting metrics to Datadog if a performance counter has numerous instances. One method is to give an instance name in the custom metrics section to collect metrics from a single sample. We’ve changed the Queued Requests item to exclusively collect metrics from the internal instance in the example above.

Set the value of instance name to ALL if you wish to collect metrics for all instances. Then, add a tag by line for each performance counter instance, which creates a key-value tag pair. If the metric Log Flushes/sec records for the instances tempdb, model, and demo db, the tags db:tempdb, db:model, and db:demo db will create. While you can name the prefix whatever you like, you may want to call it after the item that each instance represents (a database, a resource pool, etc.)

After restarting the Agent, you can add your custom metrics to dashboards and alerts. Below is a graph of the custom metric sqlserver.index searches, which we called after the Access Methods performance object counter Index Searches/sec (see above).

Custom metrics from stored procedures

While the performance counters view is useful for analyzing the health and performance of your databases, you can also use SQL Server’s comprehensive variety of concepts, stored procedures, and functions to obtain even more data. For example, you might wish to keep track of the size of specific tables in disk and memory—valuable information that isn’t available as a performance counter. 

To develop and track your custom metrics, you’ll need to do the following: 

1. Make a stored procedure that returns a temporary table containing the metrics you’d like to report. 
2. Include an entry for the stored procedure you generated in the SQL Server integration configuration file. 

The Agent will run the stored method and send the results every few seconds. 

With the stored method sp_spaceused, we’ll query metrics for a database’s disk utilization in the following example (available since SQL Server 2012). We’ll create a stored procedure to package our call to sp_spaceused in a manner that can parse into three metrics:

• The size of disk of the database’s data
• The size of indexes
• The total length of data and transaction logs

It’s just some of how stored procedures can be used to create custom metrics reports.

To generate and gather metrics, create a stored procedure. 

Can use any T-SQL queries in a stored procedure for reporting custom metrics, as long as it results in a table with a specific structure. The Agent requires custom metrics from a stored procedure to take the form of a temporary table, as seen in the SQL Server integration’s example YAML file. The following columns must be present in the table: 

• metric: the metric’s name as it appears on the screen 
• type: kind of metric: a scale, a pace, or a histogram 
• value: the metric’s value 
• tags: the tags that will be displayed 

For example, db:DATABASE NAME>, role:primary, you can put as many titles as you wish, separated by a comma.

We’ve created a stored procedure called GetDiskMetrics in this example. The first step in this stored procedure is to run sp_spaceused and save the results in a temporary database. It allows us to pick and choose specific metrics from the data. 

sp_spaceused returns strings of integers and their units, with index size and data in kilobytes (for example, 1528 KB) and database size in megabytes (e.g., 80 MB). We’ll create a function that eliminates the units, converts strings to floats, and saves the results in the table. 

When writing your stored procedure, you can convert the values you’re keeping in the table to floats. Certain data types will be converted automatically by SQL Server, while others will result in an error. The ExtractFloat function, for example, returns a string that SQL Server converts to a float before entering.

USE [<database name>]; GO -- Remove units from the results of sp_spaceused CREATE FUNCTION 
[dbo].[ExtractFloat] ( @StringWithFloat nvarchar(50) ) RETURNS float BEGIN RETURN (SELECT SUBSTRING( 
@StringWithFloat, 0, (select PATINDEX('% %', (@StringWithFloat))) )) END GO -- Create a stored 
procedure with the name GetDiskMetrics CREATE PROCEDURE [dbo].[GetDiskMetrics] AS BEGIN -- Remove 
row counts from result sets SET NOCOUNT ON; -- Create a temporary table per integration instructions
 CREATE TABLE #Datadog ( [metric] VARCHAR(255) NOT NULL, [type] VARCHAR(50) NOT NULL, [value] 
FLOAT NOT NULL, [tags] VARCHAR(255) ); -- Declare a temporary table to store the results of 
sp_spaceused DECLARE @disk_use_table table( database_name varchar(128), database_size varchar(18), 
unallocated_space varchar(18), reserved varchar(18), data varchar(18), index_size varchar(18), unused 
varchar(18) ); INSERT INTO @disk_use_table EXEC sp_spaceused @oneresultset=1; -- Remove the units
 from our custom metrics and insert them into the table INSERT INTO (metric, type, value, tags) VALUES
('sqlserver.disk.database_size_mb', 'gauge', (SELECT dbo.ExtractFloat((SELECT [database_size] 
FROM @disk_use_table))), 'db:<DATABASE_NAME>,role:primary'), ('sqlserver.disk.index_size_kb', 'gauge', 
(SELECT dbo.ExtractFloat((SELECT [index_size] FROM @disk_use_table))), 'db:<DATABASE_NAME>,role:primary'), 
('sqlserver.disk.data_size_kb', 'gauge', (SELECT dbo.ExtractFloat((SELECT [data] FROM @disk_use_table))), 
'db:<DATABASE_NAME>,role:primary'); 	-- Return the table 	SELECT * FROM #Datadog; END 

The stored procedure generates three custom metrics:

• sqlserver.disk.database size mb: Database size in megabytes, including data and transaction log files. 
• sqlserver.disk.index size kb: The total size of the database’s indexes. 
• sqlserver.disk.data size kb: The full length of the database’s data. 

The values of the tags column in the table, in this example role:primary and db:DATABASE NAME>, will be automatically applied to the metrics. We’re also keeping track of each custom measure as a gauge, which reports the metric’s current value at each check. Our documentation has more information on metric kinds, gauges, rates, and histograms. The function ExtractFloat and the stored method GetDiskMetrics are both declared above. Before configuring GetDiskMetrics, you should double-check that it has been appropriately communicated in SQL Server. Can use this query to verify that the stored procedure adds.

SELECT name FROM sys.procedures WHERE name = "GetDiskMetrics"; 

The final product should look like this.

name -------------- GetDiskMetrics 

Configure the Agent so that the stored process is executed. 

Configure the Agent to run the stored procedure that reports custom metrics that you created earlier. You’ll need to update the existing host section of the SQL Server integration YAML file. Please include the name of the stored procedure and the database the Agent will use when calling it.

There are three things to keep in mind when utilizing stored procedures to create custom metrics. In the YAML file for the integration, you can specify the connector and the interface between the Agent and SQL Server. You won’t be able to collect custom metrics with a stored procedure if you choose odbc as the connection instead of the default of adodbapi. Second, retrieving custom metrics will cause SQL Server to consume additional resources because the Agent will run the stored procedure with each check. Third, the table’s custom metrics are subject to limitations as any other custom metric. For further information, see our documentation.

Custom metrics from Windows Management Instrumentation

Using SQL Server on Windows, you can also use Windows Management Instrumentation to collect custom metrics (WMI). WMI is a Microsoft Windows operating system technology that allows applications to send and receive data. WMI is used by many applications, including SQL Server, to transmit information about resources such as drivers, storage, and processes. If you’re running SQL Server on Windows, you can also collect custom metrics by using.

You’ll need to update the WMI integration’s configuration file to get the Agent to send metrics from WMI. Give the names of WMI classes you’d like to collect metrics from under instances. You’ll see metrics as arrays with three components under each item for each category: the name of a WMI class property, the name of the metric, and the metric type. With the following configuration, for example, you can collect the number of failed SQL Server jobs:

instances: - class: Win32_PerfRawData_SQLSERVERAGENT_SQLAgentJobs metrics: - [Failedjobs, 
sqlserver.jobs.failed_jobs, gauge] # ... 

Then restart the Agent to enable the WMI integration.

SQL Server metrics for tailored monitoring

We’ve looked at metrics that can reveal SQL Server metrics essential operation and demonstrated how to utilize various monitoring tools to acquire real-time views and detailed reports in this series. By combining live observation and on-demand insights with distributed tracing and log management.

Custom metrics enable you to keep track of every measure collected by SQL Server and use them to optimize your databases. You can combine these metrics with those from SQL Server and the rest of your stack to see where performance issues are coming from and where you should focus your optimization efforts.

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.