SQL Server

SQL Server

Agent Check Agent Check

Linux Mac OS Windows OS Supported

SQL server Graph

Overview

The SQL Server check tracks 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.

Setup

Installation

The SQL Server check is included in the Datadog Agent package. No additional installation is necessary on your SQL Server instances.

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

Prerequisite

  1. Create a read-only login to connect to your server:

        CREATE LOGIN datadog WITH PASSWORD = '<PASSWORD>';
        CREATE USER datadog FOR LOGIN datadog;
        GRANT SELECT on sys.dm_os_performance_counters to datadog;
        GRANT VIEW SERVER STATE to datadog;
    
  2. Make sure your SQL Server instance is listening on a specific fixed port. By default, named instances and SQL Server Express are configured for dynamic ports. See Microsoft’s documentation for more details.

  3. (Required for AlwaysOn metrics) An additional permission needs to be granted to gather AlwaysOn metrics:

        GRANT VIEW ANY DEFINITION to datadog;
    

Configuration

Host

To configure this check for an Agent running on a host:

  1. Edit the sqlserver.d/conf.yaml file, in the conf.d/ folder at the root of your Agent’s configuration directory. See the sample sqlserver.d/conf.yaml for all available configuration options:

    init_config:
    
    instances:
      - host: "<SQL_HOST>,<SQL_PORT>"
        username: datadog
        password: "<YOUR_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.

    Note: The (default) provider SQLOLEDB is being deprecated. To use the newer MSOLEDBSQL provider, set the adoprovider variable to MSOLEDBSQL in your sqlserver.d/conf.yaml file after having downloaded the new provider from Microsoft. It is also possible to use the Windows Authentication and not specify the username/password with:

    connection_string: "Trusted_Connection=yes"
    
  2. Restart the Agent.

Linux

Extra configuration steps are required to get the SQL Server integration running on a Linux host:

  1. Install an ODBC SQL Server driver, for example the Microsoft ODBC driver.
  2. Copy the odbc.ini and odbcinst.ini files into the /opt/datadog-agent/embedded/etc folder.
  3. Configure the conf.yaml file to use the odbc connector and specify the proper driver as indicated in the odbcinst.ini file.
Log collection

Available for Agent versions >6.0

  1. Collecting logs is disabled by default in the Datadog Agent, enable it in your datadog.yaml file:

    logs_enabled: true
    
  2. Add this configuration block to your sqlserver.d/conf.yaml file to start collecting your SQL Server logs:

    logs:
      - type: file
        encoding: utf-16-le
        path: "<LOG_FILE_PATH>"
        source: sqlserver
        service: "<SERVICE_NAME>"
    

    Change the path and service parameter values based on your environment. See the sample sqlserver.d/conf.yaml for all available configuration options.

  3. Restart the Agent.

See Datadog’s documentation for additional information on how to configure the Agent for log collection in Kubernetes environments.

Containerized

For containerized environments, see the Autodiscovery Integration Templates for guidance on applying the parameters below.

Metric collection
ParameterValue
<INTEGRATION_NAME>sqlserver
<INIT_CONFIG>blank or {}
<INSTANCE_CONFIG>{"host": "%%host%%,%%port%%", "username": "datadog", "password": "<UNIQUEPASSWORD>", "connector": "odbc", "driver": "FreeTDS"}

See Autodiscovery template variables for details on passing <UNIQUEPASSWORD> as an environment variable instead of a label.

Log collection

Available for Agent versions >6.0

Collecting logs is disabled by default in the Datadog Agent. To enable it, see Kubernetes log collection.

ParameterValue
<LOG_CONFIG>{"source": "sqlserver", "service": "sqlserver"}

Validation

Run the Agent’s status subcommand and look for sqlserver under the Checks section.

Data Collected

Metrics

sqlserver.access.page_splits
(gauge)
The number of page splits per second.
Shown as operation
sqlserver.ao.ag_sync_health
(gauge)
Availability group synchronization health: 0 = Not healthy, 1 = Partially healthy, 2 = Healthy
sqlserver.ao.replica_sync_state
(gauge)
Replica synchronization state: 0 = Not synchronizing, 1 = Synchronizing, 2 = Synchronized, 3 = Reverting, 4 = Initializing
sqlserver.ao.replica_failover_mode
(gauge)
Replica failover mode: 0 = Automatic failover, 1 = Manual failover
sqlserver.ao.replica_failover_readiness
(gauge)
Replica failover readiness: 0 = Not ready for failover, 1 = Ready for failover
sqlserver.ao.primary_replica_health
(gauge)
Recovery health of primary replica: 0 = In progress, 1 = Online. The metric is not emitted if on a secondary replica
sqlserver.ao.secondary_replica_health
(gauge)
Recovery health of secondary replica: 0 = In progress, 1 = Online. The metric is not emitted if on a primary replica
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.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
sqlserver.buffer.page_life_expectancy
(gauge)
Duration that a page resides in the buffer pool.
Shown as second
sqlserver.buffer.page_reads
(gauge)
Indicates the number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases.
Shown as page
sqlserver.buffer.page_writes
(gauge)
Indicates the number of physical database page writes that are issued per second.
Shown as page
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.stats.procs_blocked
(gauge)
The number of processes blocked.
Shown as process
sqlserver.stats.auto_param_attempts
(gauge)
Number of auto-parameterization attempts per second.
Shown as attempt
sqlserver.stats.failed_auto_param_attempts
(gauge)
Number of failed auto-parameterization attempts per second.
Shown as attempt
sqlserver.stats.safe_auto_param_attempts
(gauge)
Number of safe auto-parameterization attempts per second.
Shown as attempt
sqlserver.cache.object_counts
(gauge)
Number of cache objects in the cache.
Shown as object
sqlserver.cache.pages
(gauge)
Number of 8-kilobyte (KB) pages used by cache objects.
Shown as object
sqlserver.database.backup_restore_throughput
(gauge)
Read/write throughput for backup and restore operations of a database per second.
sqlserver.database.log_bytes_flushed
(gauge)
Total number of log bytes flushed.
Shown as byte
sqlserver.database.log_flushes
(gauge)
Number of log flushes per second.
Shown as flush
sqlserver.database.log_flush_wait
(gauge)
Total wait time (in milliseconds) to flush the log. On an Always On secondary database, this value indicates the wait time for log records to be hardened to disk.
Shown as millisecond
sqlserver.database.transactions
(gauge)
Number of transactions started for the SQL Server instance per second.
Shown as transaction
sqlserver.database.write_transactions
(gauge)
Number of transactions that wrote to all databases on the SQL Server instance and committed, in the last second.
Shown as transaction
sqlserver.database.state
(gauge)
Database state: 0 = Online, 1 = Restoring, 2 = Recovering, 3 = Recovery_Pending, 4 = Suspect, 5 = Emergency, 6 = Offline, 7 = Copying, 10 = Offline_Secondary
sqlserver.database.files.size
(gauge)
Current size of the database file
Shown as kibibyte
sqlserver.database.files.state
(gauge)
Database state: 0 = Online, 1 = Restoring, 2 = Recovering, 3 = Recovery_Pending, 4 = Suspect, 5 = Unknown, 6 = Offline, 7 = Defunct
sqlserver.database.active_transactions
(gauge)
Number of active transactions across all databases on the SQL Server instance.
Shown as transaction
sqlserver.database.avg_fragment_size_in_pages
(gauge)
The average number of pages in one fragment on the leaf level of an IN_ROW_DATA allocation unit.
sqlserver.database.avg_fragmentation_in_percent
(gauge)
Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.
sqlserver.database.backup_count
(gauge)
The total count of successful backups made for a database.
sqlserver.database.fragment_count
(gauge)
The number of fragments in the leaf level of an IN_ROW_DATA allocation unit.
sqlserver.database.is_sync_with_backup
(gauge)
Whether or not the database is marked for replication synchronization with backup. 0 = Not marked for replication sync, 1 = Marked for replication sync.
sqlserver.memory.memory_grants_pending
(gauge)
Specifies the total number of processes waiting for a workspace memory grant
sqlserver.memory.total_server_memory
(gauge)
Specifies the amount of memory the server has committed using the memory manager.
Shown as kibibyte
sqlserver.scheduler.current_tasks_count
(gauge)
Number of current tasks that are associated with this scheduler.
Shown as task
sqlserver.scheduler.current_workers_count
(gauge)
Number of workers that are associated with this scheduler.
Shown as worker
sqlserver.scheduler.active_workers_count
(gauge)
Number of workers that are active. An active worker is never preemptive, must have an associated task, and is either running, runnable, or suspended.
Shown as worker
sqlserver.scheduler.runnable_tasks_count
(gauge)
Number of workers, with tasks assigned to them, that are waiting to be scheduled on the runnable queue.
Shown as task
sqlserver.scheduler.work_queue_count
(gauge)
Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up.
Shown as unit
sqlserver.task.context_switches_count
(gauge)
Number of scheduler context switches that this task has completed.
Shown as unit
sqlserver.task.pending_io_count
(gauge)
Number of physical I/Os that are performed by this task.
Shown as unit
sqlserver.task.pending_io_byte_count
(gauge)
Total byte count of I/Os that are performed by this task.
Shown as byte
sqlserver.task.pending_io_byte_average
(gauge)
Average byte count of I/Os that are performed by this task.
Shown as byte
sqlserver.fci.status
(gauge)
Status of the node in a SQL Server failover cluster instance
sqlserver.fci.is_current_owner
(gauge)
Whether or not this node is the current owner of the SQL Server FCI

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 events.

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.

Development

See the main documentation for more details about how to test and develop Agent based integrations.

Further Reading