Supported OS
The SQL Server integration tracks the performance of your SQL Server instances. It collects metrics for number of user connections, rate of SQL compilations, and more.
Enable Database Monitoring (DBM) for enhanced insight into query performance and database health. In addition to the standard integration, Datadog DBM provides query-level metrics, live and historical query snapshots, wait event analysis, database load, query explain plans, and blocking query insights.
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
Note: To install Database Monitoring for SQL Server, select your hosting solution on the documentation site for instructions.
Proceed with the following steps in this guide only if you are installing the standard integration alone.
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;
To collect file size metrics per database, ensure the user you created (datadog) has connect permission access to your databases by running:
GRANT CONNECT ANY DATABASE to datadog;
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.
(Required for AlwaysOn and sys.master_files metrics) To gather AlwaysOn and sys.master_files metrics, grant the following additional permission:
GRANT VIEW ANY DEFINITION to datadog;
To configure this check for an Agent running on a host:
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"
Extra configuration steps are required to get the SQL Server integration running on a Linux host:
odbc.ini and odbcinst.ini files into the /opt/datadog-agent/embedded/etc folder.conf.yaml file to use the odbc connector and specify the proper driver as indicated in the odbcinst.ini file.Available for Agent versions >6.0
Collecting logs is disabled by default in the Datadog Agent, enable it in your datadog.yaml file:
logs_enabled: true
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.
For containerized environments, see the Autodiscovery Integration Templates for guidance on applying the parameters below.
| Parameter | Value |
|---|---|
<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.
Available for Agent versions >6.0
Collecting logs is disabled by default in the Datadog Agent. To enable it, see Kubernetes Log Collection.
| Parameter | Value |
|---|---|
<LOG_CONFIG> | {"source": "sqlserver", "service": "sqlserver"} |
Run the Agent’s status subcommand and look for sqlserver under the Checks section.
| sqlserver.access.page_splits (gauge) | The number of page splits per second. Shown as operation |
| sqlserver.ao.is_primary_replica (gauge) | Denotes if a replica is a primary(1) or secondary(0). Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, database_name, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role |
| sqlserver.ao.ag_sync_health (gauge) | Availability group synchronization health: 0 = Not healthy, 1 = Partially healthy, 2 = Healthy. Tags: availability_group, availability_group_name, synchronization_health_desc |
| sqlserver.ao.replica_status (gauge) | Denotes an Availability Group replica's status. Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, database_name, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role |
| sqlserver.ao.replica_sync_state (gauge) | Replica synchronization state: 0 = Not synchronizing, 1 = Synchronizing, 2 = Synchronized, 3 = Reverting, 4 = Initializing. Tags: availability_group, availability_group_name, synchronization_health_desc |
| sqlserver.ao.log_send_queue_size (gauge) | Amount of log records of the primary database that has not been sent to the secondary databases. Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, database_name, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_roleShown as byte |
| sqlserver.ao.log_send_rate (gauge) | Average rate at which primary replica instance sent data during last active period. Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, database_name, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_roleShown as byte |
| sqlserver.ao.redo_queue_size (gauge) | Amount of log records in the log files of the secondary replica that has not yet been redone. Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, database_name, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_roleShown as byte |
| sqlserver.ao.redo_rate (gauge) | Average rate at which the log records are being redone on a given secondary database. Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, database_name, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_roleShown as byte |
| sqlserver.ao.low_water_mark_for_ghosts (gauge) | A monotonically increasing number for the database indicating a low water mark used by ghost cleanup on the primary database. Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, database_name, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role |
| sqlserver.ao.filestream_send_rate (gauge) | The rate at which the FILESTREAM files are shipped to the secondary replica. Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, database_name, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_roleShown as byte |
| sqlserver.ao.secondary_lag_seconds (gauge) | The number of seconds that the secondary replica is behind the primary replica during synchronization. Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, database_name, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_roleShown as second |
| sqlserver.ao.replica_failover_mode (gauge) | Replica failover mode: 0 = Automatic failover, 1 = Manual failover. Tags: replica_server_name, availability_group, availability_group_name, failover_mode_desc |
| sqlserver.ao.replica_failover_readiness (gauge) | Replica failover readiness: 0 = Not ready for failover, 1 = Ready for failover. Tags: replica_server_name, availability_group, availability_group_name, failover_mode_desc |
| 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. Tags: availability_group, availability_group_name, synchronization_health_desc |
| 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. Tags: availability_group, availability_group_name, synchronization_health_desc |
| sqlserver.ao.quorum_type (gauge) | Type of quorum used by the WSFC cluster. Tags: quorum_type, quorum_state, failover_cluster |
| sqlserver.ao.quorum_state (gauge) | State of the WSFC quorum. Tags: quorum_type, quorum_state, failover_cluster |
| sqlserver.ao.member.type (gauge) | The type of member that's a part of the WSFC quorum. Tags: member_name, member_type, member_state, failover_cluster |
| sqlserver.ao.member.state (gauge) | The state of a member that's a part of the WSFC quorum. Tags: member_name, member_type, member_state, failover_cluster |
| sqlserver.ao.member.number_of_quorum_votes (gauge) | Number of quorum votes possessed by this quorum member. Tags: member_name, member_type, member_state, failover_cluster |
| sqlserver.fci.status (gauge) | Status of the node in a SQL Server failover cluster instance. Tags: node_name, status, failover_cluster |
| sqlserver.fci.is_current_owner (gauge) | Whether or not this node is the current owner of the SQL Server FCI. Tags: node_name, status, failover_cluster |
| 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. If DBM is enabled, this metric is tagged with status, db, and user. 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 = RecoveryPending, 4 = Suspect, 5 = Emergency, 6 = Offline, 7 = Copying, 10 = OfflineSecondary |
| sqlserver.database.files.size (gauge) | Current size of the database file Shown as kibibyte |
| sqlserver.database.files.state (gauge) | Database file state: 0 = Online, 1 = Restoring, 2 = Recovering, 3 = Recovery_Pending, 4 = Suspect, 5 = Unknown, 6 = Offline, 7 = Defunct |
| sqlserver.database.master_files.size (gauge) | Current size of the database file. For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file. Note: Use sqlserver.database.files.size for the actual size of FILESTREAM containers.Shown as kibibyte |
| sqlserver.database.master_files.state (gauge) | Database file 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 INROWDATA allocation unit. |
| sqlserver.database.avg_fragmentation_in_percent (gauge) | Logical fragmentation for indexes, or extent fragmentation for heaps in the INROWDATA 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 INROWDATA 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.files.reads (count) | Number of reads issued on the file. Tags: logical_name, file_location, db, stateShown as read |
| sqlserver.files.read_bytes (count) | Bytes read from the file. Tags: logical_name, file_location, db, stateShown as byte |
| sqlserver.files.read_io_stall (count) | Total time that users waited for reads on the file. Tags: logical_name, file_location, db, stateShown as millisecond |
| sqlserver.files.read_io_stall_queued (count) | Total latency from IO governance pools for reads on the file. Tags: logical_name, file_location, db, stateShown as millisecond |
| sqlserver.files.writes (count) | Number of writes issued on the file. Tags: logical_name, file_location, db, stateShown as write |
| sqlserver.files.written_bytes (count) | Bytes written to the file. Tags: logical_name, file_location, db, stateShown as byte |
| sqlserver.files.write_io_stall (count) | Total time that users waited for writes on the file. Tags: logical_name, file_location, db, stateShown as millisecond |
| sqlserver.files.write_io_stall_queued (count) | Total latency from IO governance pools for writes on the file. Tags: logical_name, file_location, db, stateShown as millisecond |
| sqlserver.files.io_stall (count) | Total time that users waited for I/O to complete on the file. Tags: logical_name, file_location, db, stateShown as millisecond |
| sqlserver.files.size_on_disk (gauge) | Number of bytes used on the disk for this file. Tags: logical_name, file_location, db, stateShown as byte |
| 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.server.uptime (gauge) | Total time elapsed since the last computer restart. Shown as second |
| sqlserver.server.cpu_count (gauge) | The number of logical CPUs or vCPUs on the server. Shown as byte |
| sqlserver.server.committed_memory (gauge) | The amount of memory committed to the memory manager Shown as byte |
| sqlserver.server.physical_memory (gauge) | Total physical memory on the machine Shown as byte |
| sqlserver.server.virtual_memory (gauge) | Amount of virtual memory available to the process in user mode. Shown as byte |
| sqlserver.server.target_memory (gauge) | Amount of memory that can be consumed by the memory manager. When this value is larger than the committed memory, then the memory manager will try to obtain more memory. When it is smaller, the memory manager will try to shrink the amount of memory committed. Shown as byte |
| 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.queries.count (count) | Total count of executed queries per query (DBM only) Shown as query |
| sqlserver.queries.time (count) | Total elapsed time for executed queries per query (DBM only) Shown as nanosecond |
| sqlserver.queries.clr_time (count) | Total time consumed inside Microsoft .NET Framework common language runtime (CLR) objects for executed queries per query (DBM only) Shown as nanosecond |
| sqlserver.queries.worker_time (count) | Total CPU time consumed by executed queries per query (DBM only) Shown as nanosecond |
| sqlserver.queries.rows (count) | Total number of rows returned by executed queries per query (DBM only) Shown as row |
| sqlserver.queries.physical_reads (count) | Total number of physical reads performed by executed queries per query (DBM only) Shown as read |
| sqlserver.queries.logical_reads (count) | Total number of logical reads performed by executed queries per query (DBM only) Shown as read |
| sqlserver.queries.logical_writes (count) | Total number of logical writes performed by executed queries per query (DBM only) Shown as write |
| sqlserver.queries.columnstore_segment_reads (count) | Total columnstore segments read by executed queries per query (DBM only) Shown as segment |
| sqlserver.queries.columnstore_segment_skips (count) | Total columnstore segments skipped by executed queries per query (DBM only) Shown as segment |
| sqlserver.queries.memory_grant (count) | The total amount of reserved memory received by executions of this query per query. It will always be 0 for querying a memory-optimized table (DBM only). Shown as byte |
| sqlserver.queries.used_memory_grant (count) | The total amount of reserved memory used by executions of this query per query. It will always be 0 for querying a memory-optimized table (DBM only). Shown as byte |
| sqlserver.queries.ideal_memory_grant (count) | The total amount of ideal memory grant estimated by executions of this query per query (DBM only) Shown as byte |
| sqlserver.queries.reserved_threads (count) | The total sum of reserved parallel threads used by executions of this query per query (DBM only) Shown as thread |
| sqlserver.queries.used_threads (count) | The total sum of used parallel threads used by executions of this query per query (DBM only) Shown as thread |
| sqlserver.queries.dop (count) | The total sum of degree of parallelism used by executions of this query per query (DBM only) |
| sqlserver.queries.spills (count) | The total number of pages spilled by execution of this query per query (DBM only) |
| sqlserver.queries.duration.max (gauge) | The age of the longest running query per user, db, and app. (DBM only) Shown as nanosecond |
| sqlserver.queries.duration.sum (gauge) | The sum of the age of all running queries per user, db, and app. (DBM only) Shown as nanosecond |
Most of these metrics come from your SQL Server’s sys.dm_os_performance_counters table.
The SQL server check does not include any events.
sqlserver.can_connect
Returns CRITICAL if the Agent is unable to connect to the monitored SQL Server instance. Returns OK otherwise.
Statuses: ok, critical
sqlserver.database.can_connect
Returns CRITICAL if the Agent is unable to connect to the autodiscovered SQL Server Database. Returns OK otherwise.
Statuses: ok, critical
Need help? Contact Datadog support.