Datadog-SQL Server Integration

sql server graph

Overview

This check lets you track the performance of your SQL Server instances. It collects metrics for number of user connections, rate of SQL compilations, and more.

You can also create your own metrics by having the check run custom queries.

SQL Server check can only be run from a Windows environment

Setup

Installation

The SQL Server check is packaged with the Agent, so simply install the Agent on your SQL Server instances. If you need the newest version of the check, install the dd-check-sqlserver package.

Make sure that your SQL Server instance supports SQL Server authentication by enabling “SQL Server and Windows Authentication mode” in the server properties. Server Properties -> Security -> SQL Server and Windows Authentication mode

Configuration

  1. Create a read-only user to connect to your server:
CREATE LOGIN datadog WITH PASSWORD = 'YOUR_PASSWORD';
CREATE USER datadog FOR LOGIN datadog;
GRANT SELECT on sys.dm_os_performance_counters to datadog;
GRANT VIEW SERVER STATE to datadog;
  1. Create a file sqlserver.yaml in the Agent’s conf.d directory. See the sample sqlserver.yaml for all available configuration options:
init_config:

instances:
  - host: <SQL_HOST>,<SQL_PORT>
    username: <SQL_ADMIN_USER>
    password: <SQL_ADMIN_PASSWORD>
    connector: odbc # alternative is 'adodbapi'
    driver: SQL Server

See the example check configuration for a comprehensive description of all options, including how to use custom queries to create your own metrics.

  1. Restart the Agent to start sending SQL Server metrics to Datadog.

Validation

Run the Agent’s info subcommand and look for sqlserver under the Checks section:

  Checks
  ======
    [...]

    sqlserver
    -------
      - instance #0 [OK]
      - Collected 26 metrics, 0 events & 1 service check

    [...]

Compatibility

The sqlserver check is compatible with all Windows and Linux platforms.

Data Collected

Metrics

sqlserver.buffer.cache_hit_ratio
(gauge)
The ratio of data pages found and read from the buffer cache over all data page requests.
shown as fraction
sqlserver.buffer.page_life_expectancy
(gauge)
Duration that a page resides in the buffer pool.
shown as second
sqlserver.stats.batch_requests
(gauge)
The number of batch requests per second.
shown as request
sqlserver.stats.sql_compilations
(gauge)
The number of SQL compilations per second.
shown as operation
sqlserver.stats.sql_recompilations
(gauge)
The number of SQL re-compilations per second.
shown as operation
sqlserver.stats.connections
(gauge)
The number of user connections.
shown as connection
sqlserver.stats.lock_waits
(gauge)
The number of times per second that SQL Server is unable to retain a lock right away for a resource.
shown as lock
sqlserver.access.page_splits
(gauge)
The number of page splits per second.
shown as operation
sqlserver.stats.procs_blocked
(gauge)
The number of processes blocked.
shown as process
sqlserver.buffer.checkpoint_pages
(gauge)
The number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
shown as page

Most of these metrics come from your SQL Server’s sys.dm_os_performance_counters table.

Events

The SQL server check does not include any event at this time.

Service Checks

sqlserver.can_connect:

Returns CRITICAL if the Agent cannot connect to SQL Server to collect metrics, otherwise OK.

Troubleshooting

Need help? Contact Datadog Support.

Further Reading