--- title: "Custom SQL Server metrics for detailed monitoring" description: "Gather custom SQL Server metrics with performance counters and the WMI integration." author: "Rishabh Moudgil, Paul Gottschling" date: 2018-05-04 tags: ["infrastructure monitoring", "database monitoring", "microsoft", "sql server", "sql-server", "sql"] blog_type_id: the-monitor locale: ko --- We've shown in [Part 3](https://www.datadoghq.com/blog/sql-server-performance.md) 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](#custom-datadog-metrics-with-the-performance-counters-view) beyond those the SQL Server integration queries by default 1. Using the [Windows Management Instrumentation](#custom-datadog-metrics-from-windows-management-instrumentation) (WMI) integration ## Custom Datadog metrics with the performance counters view Although the Agent already collects a number of [important metrics](https://docs.datadoghq.com/integrations/sqlserver.md) from the [performance counters dynamic management view](https://www.datadoghq.com/blog/sql-server-monitoring-tools.md#dynamic-management-views), you might be interested in monitoring additional [performance objects](https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/use-sql-server-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: ```text SELECT counter_name, instance_name, cntr_value FROM sys.dm_os_performance_counters; ``` You'll see something resembling the following: ```text 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 ``` To collect metrics automatically from specific performance counters, edit the SQL Server [configuration file](https://docs.datadoghq.com/integrations/sqlserver.md#configuration), which the Agent looks for within **C:\ProgramData\Datadog\conf.d\sqlserver.d**. [Create an entry](https://docs.datadoghq.com/integrations/faq/how-can-i-collect-more-metrics-from-my-sql-server-integration.md) 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: ```text # ... 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 # ... ``` 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](https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/use-sql-server-objects) 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`](https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-databases-object?view=sql-server-2017). There's a separate instance of the object (and its counters) for each database. The [resource pool performance object](https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-resource-pool-stats-object?view=sql-server-2017) 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`](https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-access-methods-object?view=sql-server-2017) performance object (see [above](#custom-datadog-metrics-with-the-performance-counters-view)). ![Selecting performance counters for custom SQL Server metrics](https://web-assets.dd-static.net/42588/1776358987-sql-server-metrics-sql-server-metrics-index-searches-counter.png) ## 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](https://msdn.microsoft.com/en-us/library/aa394582(v=vs.85).aspx) (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](https://docs.datadoghq.com/integrations/wmi_check.md) can monitor the [hundreds of WMI classes](https://docs.datadoghq.com/integrations/faq/how-to-retrieve-wmi-metrics.md) 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](https://docs.datadoghq.com/integrations/wmi_check.md#configuration). Under `instances`, list the names of the [WMI classes](https://msdn.microsoft.com/en-us/library/windows/desktop/aa394554.aspx) 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](https://docs.datadoghq.com/developers/metrics/types.md?tab=count). You can collect the number of failed SQL Server jobs with the following configuration, for example: ```text instances: - class: Win32_PerfRawData_SQLSERVERAGENT_SQLAgentJobs metrics: - [Failedjobs, sqlserver.jobs.failed_jobs, gauge] # ... ``` Then enable the WMI integration by restarting the Agent. [Click here](http://wutils.com/wmi/root/cimv2/win32_perfrawdata/) 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](https://www.datadoghq.com/blog/sql-server-monitoring.md) that can expose SQL Server's core functionality, and have shown you how to use a number of [monitoring tools](https://www.datadoghq.com/blog/sql-server-monitoring-tools.md) 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](https://www.datadoghq.com/blog/sql-server-performance.md). 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 1,000 other supported technologies, you can get started by signing up for a .