Custom SQL Server metrics for detailed monitoring

We've shown in Part 3 of this series how Datadog can help you monitor your SQL Server databases within the context of your application. In this post, we'll show you how to go one step further by collecting custom SQL Server metrics that let you choose the exact functionality you want to monitor and improve. You can configure the Agent to collect custom metrics and report them every time it runs its built-in SQL Server check.

We'll show you two ways to collect and monitor custom metrics:

1. Specifying performance counters beyond those the SQL Server integration queries by default
2. Using the Windows Management Instrumentation (WMI) integration

Custom Datadog metrics with the performance counters view

Although the Agent already collects a number of important metrics from the performance counters dynamic management view, you might be interested in monitoring additional performance objects such as page lookups per second, log flushes per second, or queued requests. You can see a list of all the performance counters you can monitor by running the following query:

1
SELECT counter_name, instance_name, cntr_value FROM sys.dm_os_performance_counters;

You'll see something resembling the following:

1
counter_name         instance_name         cntr_value
2
--------------------------------------------------------
3
Page lookups/sec                           30617439
4
Log Flushes/sec      tempdb                5664
5
Log Flushes/sec      model                 7
6
Log Flushes/sec      demo_db               15152
7
Queued requests      internal              0

To collect metrics automatically from specific performance counters, edit the SQL Server configuration file, which the Agent looks for within C:\ProgramData\Datadog\conf.d\sqlserver.d. Create an entry under custom_metrics for each metric you want to collect. For example, we can collect the metrics "Page lookups/sec," "Queued Requests," and "Log Flushes/sec," plus "Index Searches/sec," by adding the configuration below:

1
    # ...
2
    custom_metrics:
3
        - name: sqlserver.buffer.page_lookups
4
          counter_name: Page lookups/sec
5

6
        - name: sqlserver.workload.queued_requests
7
          counter_name: Queued Requests
8
          instance_name: internal
9

10
        - name: sqlserver.databases.log_flushes
11
          counter_name: Log Flushes/sec
12
          instance_name: ALL
13
          tag_by: db
14

15
        - name: sqlserver.index_searches
16
          counter_name: Index Searches/sec
17
    # ...

For each entry, you must specify values for name and counter_name. The name value will be the name of the metric as you want it to appear in Datadog, whereas the counter_name maps to the counter_name column of sys.dm_os_performance_counters. In the case of "Page lookups/sec," the configuration above will cause the metric to appear in Datadog as sqlserver.buffer.page_lookups.

Some performance objects are associated with multiple instances within SQL Server, and you can identify these with the instance_name column of sys.dm_os_performance_counters. You'll want to check the documentation for the performance objects you're interested in to see what instance_name means in that context. In our example above, Log Flushes/sec is a counter within the object SQLServer:Databases. There's a separate instance of the object (and its counters) for each database. The resource pool performance object has a separate instance for each resource pool. Other performance objects, like the Buffer Manager object where you'll find Page lookups/sec, always have a single instance.

If a performance counter has multiple instances, you have two options for sending metrics to Datadog. One is to collect metrics from a single instance, by specifying instance_name in the custom_metrics section. In our example above, we've edited the item for Queued Requests to gather metrics only from the internal instance.

If you want to collect metrics associated with every instance, set the value of instance_name to ALL. Then add a tag_by line, which creates a key-value tag pair for each instance of a performance counter. If the metric Log Flushes/sec is reported for instances tempdb, model, and demo_db, for example, a tag_by prefix of db will create the tags db:tempdb, db:model, and db:demo_db. While you can name the prefix anything you'd like, you may want to name it after the object that each instance represents (a database, a resource pool, etc.).

After restarting the Agent, you'll be able to add your custom metrics to dashboards and alerts, just like any other metric in Datadog. Below, we're graphing the custom metric sqlserver.index_searches, which we've named from the counter Index Searches/sec within the Access Methods performance object (see above).

Close dialog

Custom Datadog metrics from Windows Management Instrumentation

If you’re running SQL Server on Windows, you can also collect custom metrics by using Windows Management Instrumentation (WMI). WMI is a core feature of the Microsoft Windows operating system that allows applications to broadcast and receive data. Applications commonly use WMI to communicate information about resources, such as drivers, disks, or processes, including SQL Server. Datadog's WMI integration can monitor the hundreds of WMI classes you'll find in a Windows environment, making this is a convenient way to add custom metrics for SQL Server.

To configure the Agent to send metrics from WMI, you'll need to edit the WMI integration's configuration file. Under instances, list the names of the WMI classes from which you want to gather metrics. Under the item for each class, you'll list metrics as arrays with three elements: the name of a property of the WMI class, the name of the metric you'd like to report to Datadog, and the metric type.

You can collect the number of failed SQL Server jobs with the following configuration, for example:

1
instances:
2
    - class: Win32_PerfRawData_SQLSERVERAGENT_SQLAgentJobs
3
      metrics:
4
        - [Failedjobs, sqlserver.jobs.failed_jobs, gauge]
5
    # ...

Then enable the WMI integration by restarting the Agent.

Click here to see all of the WMI classes that report data from SQL Server.

SQL Server metrics for tailored monitoring

In this series, we've surveyed metrics that can expose SQL Server's core functionality, and have shown you how to use a number of monitoring tools to get real-time views and detailed reports. We've demonstrated how you can combine live observation and on-demand insights by adding distributed tracing and log management, all with Datadog.

With custom metrics, it's possible to monitor every metric SQL Server collects internally, and to use this as a basis for optimizing your databases. With Datadog, you can correlate these metrics with others from SQL Server and the rest of your stack, making it clear where performance issues are originating or where you should focus your optimization efforts.

If you are not using Datadog and want to gain visibility into the health and performance of SQL Server and more than 850 other supported technologies, you can get started by signing up for a 14-day free trial.