SQL Server

Supported OS Linux Mac OS Windows

Graphique SQL Server

Présentation

Le check SQL Server surveille les performances de vos instances SQL Server. Il recueille des métriques concernant le nombre de connexions utilisateur, le nombre de compilations SQL, et plus encore.

Vous pouvez également créer vos propres métriques en demandant au check d’exécuter des requêtes personnalisées.

Configuration

Installation

Le check SQL Server est inclus avec le package de l’Agent Datadog. Vous n’avez donc rien d’autre à installer sur vos instances SQL Server.

Vérifiez que votre instance SQL Server prend en charge l’authentification SQL Server en activant « Mode d’authentification SQL Server et Windows » dans les propriétés du serveur :

Propriétés -> Sécurité -> Mode d’authentification SQL Server et Windows

Prérequis

  1. Créez un utilisateur en lecture seule pour vous connecter à votre serveur :

        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. Assurez-vous que votre instance SQL Server effectue son écoute sur un port fixe spécifique. Par défaut, les instances nommées et SQL Server Express sont configurés pour utiliser des ports dynamiques. Consultez la documentation de Microsoft pour en savoir plus.

  3. Cette étape est obligatoire pour les métriques AlwaysOn. Une autorisation supplémentaire doit être accordée pour rassembler des métriques AlwaysOn :

        GRANT VIEW ANY DEFINITION to datadog;
    

Configuration

Host

Pour configurer ce check lorsque l’Agent est exécuté sur un host :

  1. Modifiez le fichier sqlserver.d/conf.yaml dans le dossier conf.d/ à la racine du répertoire de configuration de votre Agent. Consultez le fichier d’exemple sqlserver.d/conf.yaml pour découvrir toutes les options de configuration disponibles.

    init_config:
    
    instances:
      - host: "<SQL_HOST>,<SQL_PORT>"
        username: datadog
        password: "<YOUR_PASSWORD>"
        connector: odbc # alternative is 'adodbapi'
        driver: SQL Server
    

    Consultez un exemple de configuration du check pour obtenir une description complète de toutes les options, mais également pour découvrir comment utiliser les requêtes personnalisées pour créer vos propres métriques.

    Remarque : le fournisseur (par défaut) SQLOLEDB est déconseillé. Pour utiliser le nouveau fournisseur MSOLEDBSQL, définissez la variable adoprovider sur MSOLEDBSQL dans votre fichier sqlserver.d/conf.yaml après avoir téléchargé le nouveau fournisseur depuis Microsoft. Vous pouvez également utiliser l’authentification Windows sans fournir de nom d’utilisateur ni de mot de passe en indiquant ce qui suit :

    connection_string: "Trusted_Connection=yes"
    
  2. Redémarrez l’Agent.

Linux

Des étapes de configuration supplémentaires sont requises pour exécuter l’intégration SQL Server sur un host Linux :

  1. Installez un pilote SQL Server ODBC, comme Microsoft ODBC Driver.
  2. Copiez les fichiers odbc.ini et odbcinst.ini dans le dossier /opt/datadog-agent/embedded/etc.
  3. Modifiez le fichier conf.yaml de façon à utiliser le connecteur odbc et indiquez le pilote approprié, comme indiqué dans le fichier odbcinst.ini.
Collecte de logs

Disponible à partir des versions > 6.0 de l’Agent

  1. La collecte de logs est désactivée par défaut dans l’Agent Datadog. Vous devez l’activer dans datadog.yaml :

    logs_enabled: true
    
  2. Ajoutez ce bloc de configuration à votre fichier sqlserver.d/conf.yaml pour commencer à recueillir vos logs SQL Server :

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

    Modifiez les valeurs des paramètres path et service en fonction de votre environnement. Consultez le fichier d’exemple sqlserver.d/conf.yaml pour découvrir toutes les options de configuration disponibles.

  3. Redémarrez l’Agent.

Consultez la documentation de Datadog pour découvrir comment configurer l’Agent afin de recueillir les logs dans un environnement Kubernetes.

Environnement conteneurisé

Consultez la documentation relative aux modèles d’intégration Autodiscovery pour découvrir comment appliquer les paramètres ci-dessous à un environnement conteneurisé.

Collecte de métriques
ParamètreValeur
<NOM_INTÉGRATION>sqlserver
<CONFIG_INIT>vide ou {}
<CONFIG_INSTANCE>{"host": "%%host%%,%%port%%", "username": "datadog", "password": "<MOTDEPASSEUNIQUE>", "connector": "odbc", "driver": "FreeTDS"}

Consultez la documentation relative aux template variables Autodiscovery pour découvrir comment transmettre <MOTDEPASSEUNIQUE> en tant que variable d’environnement plutôt que sous forme d’étiquette.

Collecte de logs

Disponible à partir des versions > 6.0 de l’Agent

La collecte des logs est désactivée par défaut dans l’Agent Datadog. Pour l’activer, consultez la section Collecte de logs avec Kubernetes.

ParamètreValeur
<CONFIG_LOG>{"source": "sqlserver", "service": "sqlserver"}

Validation

Lancez la sous-commande status de l’Agent et cherchez sqlserver dans la section Checks.

Données collectées

Métriques

sqlserver.access.full_scans
(gauge)
Number of unrestricted full scans per second. These can be either base-table or full-index scans. (Perf. Counter: Access Methods - Full Scans/sec)
Shown as operation
sqlserver.access.index_searches
(gauge)
Number of index searches per second. These are used to start a range scan, reposition a range scan, revalidate a scan point, fetch a single index record, and search down the index to locate where to insert a new row. (Perf. Counter: Access Methods - Index Searches/sec)
Shown as operation
sqlserver.access.page_splits
(gauge)
The number of page splits per second. (Perf. Counter: Access Methods - Page Splits/sec)
Shown as operation
sqlserver.access.probe_scans
(gauge)
Number of probe scans per second that are used to find at most one single qualified row in an index or base table directly. (Perf. Counter: Access Methods - Probe Scans/sec)
Shown as operation
sqlserver.access.range_scans
(gauge)
Number of qualified range scans through indexes per second. (Perf. Counter: Access Methods - Range Scans/sec)
Shown as operation
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.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, db, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role
Shown as byte
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, db, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role
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, db, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role
Shown 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, db, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role
Shown 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, db, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role
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.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.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.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.quorum_state
(gauge)
State of the WSFC quorum. Tags: quorum_type, quorum_state, failover_cluster
sqlserver.ao.quorum_type
(gauge)
Type of quorum used by the WSFC cluster. Tags: quorum_type, quorum_state, failover_cluster
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, db, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role
Shown 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, db, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role
Shown as byte
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.replica_status
(gauge)
Denotes an Availability Group replica's status. Tags: availability_group, availability_group_name, replica_server_name, failover_mode, availability_mode, db, 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.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, db, replica_id, database_id, database_state, synchronization_state, failover_cluster, replica_role
Shown as second
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.buffer.cache_hit_ratio
(gauge)
The ratio of data pages found and read from the buffer cache over all data page requests. (Perf. Counter: Buffer Manager - Buffer cache hit ratio)
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. (Perf. Counter: Buffer Manager - Checkpoint pages/sec)
Shown as page
sqlserver.buffer.page_life_expectancy
(gauge)
Duration that a page resides in the buffer pool. (Perf. Counter: Buffer Manager - Page life expectancy)
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. (Perf. Counter: Buffer Manager - Page reads/sec)
Shown as page
sqlserver.buffer.page_writes
(gauge)
Indicates the number of physical database page writes that are issued per second. (Perf. Counter: Buffer Manager - Page writes/sec)
Shown as page
sqlserver.cache.object_counts
(gauge)
Number of cache objects in the cache. (Perf. Counter: Plan Cache - Cache Object Counts)
Shown as object
sqlserver.cache.pages
(gauge)
Number of 8-kilobyte (KB) pages used by cache objects. (Perf. Counter: Plan Cache - Cache Pages)
Shown as object
sqlserver.database.active_transactions
(gauge)
Number of active transactions across all databases on the SQL Server instance. Tags: db. (Perf. Counter: Databases - Active Transactions).
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. Tags: db, object_name, index_id, index_name
sqlserver.database.avg_fragmentation_in_percent
(gauge)
Logical fragmentation for indexes, or extent fragmentation for heaps in the INROWDATA allocation unit. Tags: db, object_name, index_id, index_name
sqlserver.database.backup_count
(gauge)
The total count of successful backups made for a database. Note: This metric is not emitted on Azure managed databases. Tags: db
sqlserver.database.backup_restore_throughput
(gauge)
Read/write throughput for backup and restore operations of a database per second. Tags: db. (Perf. Counter: Databases - Backup/Restore Throughput/sec)
sqlserver.database.files.size
(gauge)
Current size of the database file. Tags: db, file_id, file_type, file_name, file_location, database_files_state_desc
Shown as kibibyte
sqlserver.database.files.space_used
(gauge)
Current used space of the database file. Tags: db, file_id, file_type, file_name, file_location, database_files_state_desc
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. Tags: db, file_id, file_type, file_name, file_location, database_files_state_desc
sqlserver.database.fragment_count
(gauge)
The number of fragments in the leaf level of an INROWDATA allocation unit. Tags: db, object_name, index_id, index_name
sqlserver.database.index_page_count
(gauge)
Total number of index or data pages. Tags: db, object_name, index_id, index_name
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. Tags: db, database_state_desc, database_recovery_model_desc
sqlserver.database.is_read_only
(gauge)
Whether or not the database is marked as READONLY. 0 = READWRITE, 1 = READ_ONLY. Tags: db, database_state_desc, database_recovery_model_desc
sqlserver.database.is_in_standby
(gauge)
Whether or not the database is read-only for restore log. Tags: db, database_state_desc, database_recovery_model_desc
sqlserver.database.log_bytes_flushed
(gauge)
Total number of log bytes flushed. Tags: db. (Perf. Counter: Databases - Log Bytes Flushed/sec)
Shown as byte
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. Tags: db. (Perf. Counter: Databases - Log Flush Wait Time)
Shown as millisecond
sqlserver.database.log_flushes
(gauge)
Number of log flushes per second. Tags: db. (Perf. Counter: Databases - Log Flushes/sec)
Shown as flush
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. Tags: db, file_id, file_type, file_location, database_files_state_desc
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. Tags: db, file_id, file_type, file_location, database_files_state_desc
sqlserver.database.state
(gauge)
Database state: 0 = Online, 1 = Restoring, 2 = Recovering, 3 = RecoveryPending, 4 = Suspect, 5 = Emergency, 6 = Offline, 7 = Copying, 10 = OfflineSecondary. Tags: db, database_state_desc, database_recovery_model_desc
sqlserver.database.transactions
(gauge)
Number of transactions started for the SQL Server instance per second. Tags: db. (Perf. Counter: Databases - Transactions/sec)
Shown as transaction
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. Tags: db. (Perf. Counter: Databases - Write Transactions/sec)
Shown as transaction
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.fci.status
(gauge)
Status of the node in a SQL Server failover cluster instance. Tags: node_name, status, failover_cluster
sqlserver.files.io_stall
(count)
Total time that users waited for I/O to complete on the file. Tags: logical_name, file_location, db, state
Shown as millisecond
sqlserver.files.read_bytes
(count)
Bytes read from the file. Tags: logical_name, file_location, db, state
Shown as byte
sqlserver.files.read_io_stall_queued
(count)
Total latency from IO governance pools for reads on the file. Tags: logical_name, file_location, db, state
Shown as millisecond
sqlserver.files.read_io_stall
(count)
Total time that users waited for reads on the file. Tags: logical_name, file_location, db, state
Shown as millisecond
sqlserver.files.reads
(count)
Number of reads issued on the file. Tags: logical_name, file_location, db, state
Shown as read
sqlserver.files.size_on_disk
(gauge)
Number of bytes used on the disk for this file. Tags: logical_name, file_location, db, state
Shown as byte
sqlserver.files.write_io_stall_queued
(count)
Total latency from IO governance pools for writes on the file. Tags: logical_name, file_location, db, state
Shown as millisecond
sqlserver.files.write_io_stall
(count)
Total time that users waited for writes on the file. Tags: logical_name, file_location, db, state
Shown as millisecond
sqlserver.files.writes
(count)
Number of writes issued on the file. Tags: logical_name, file_location, db, state
Shown as write
sqlserver.files.written_bytes
(count)
Bytes written to the file. Tags: logical_name, file_location, db, state
Shown as byte
sqlserver.index.user_lookups
(count)
Number of bookmark lookups by user queries. Tags: db, table, index_name
Shown as occurrence
sqlserver.index.user_scans
(count)
Number of scans by user queries that did not use 'seek' predicate. Tags: db, table, index_name
Shown as scan
sqlserver.index.user_seeks
(count)
Number of seeks by user queries. Tags: db, table, index_name
Shown as occurrence
sqlserver.index.user_updates
(count)
Number of updates by user queries. This includes Insert, Delete, and Updates representing the number of operations done, not the actual rows affected. Tags: db, table, index_name
Shown as update
sqlserver.latches.latch_wait_time
(gauge)
Average latch wait time (in milliseconds) for latch requests that had to wait. (Perf. Counter: Locks - Average Latch Wait Time (ms))
Shown as millisecond
sqlserver.latches.latch_waits
(gauge)
Number of latch requests that could not be granted immediately. (Perf. Counter: Locks - Latch Waits/sec)
Shown as request
sqlserver.locks.deadlocks
(gauge)
Number of lock requests per second that resulted in a deadlock. (Perf. Counter: Locks - Number of Deadlocks/sec)
Shown as request
sqlserver.log_shipping_primary.time_since_backup
(gauge)
Seconds since the last transaction log backup operation on the primary server. (Tags: primary_db, primary_server, primary_id)
Shown as second
sqlserver.log_shipping_primary.backup_threshold
(gauge)
Seconds allowed to elapse between backup operations before a SQL Server job alert is generated. (Tags: primary_db, primary_server, primary_id)
Shown as second
sqlserver.log_shipping_secondary.time_since_copy
(gauge)
Seconds since last copy operation to the secondary server. (Tags: primary_db, primary_server, secondary_db, secondary_server, secondary_id)
Shown as second
sqlserver.log_shipping_secondary.time_since_restore
(gauge)
Seconds since the last restore operation on the secondary server. (Tags: primary_db, primary_server, secondary_db, secondary_server, secondary_id)
Shown as second
sqlserver.log_shipping_secondary.last_restored_latency
(gauge)
The amount of time, in seconds, that elapsed between when the log backup was created on the primary and when it was restored on the secondary. (Tags: primary_db, primary_server, secondary_db, secondary_server, secondary_id)
Shown as second
sqlserver.log_shipping_secondary.restore_threshold
(gauge)
e>Seconds allowed to elapse between restore operations before a SQL Server job alert is generated. (Tags: `primary_db`, `primary_server`, `secondary_db`, `secondary_server`, `secondary_id`) Shown as second
sqlserver.memory.connection
(gauge)
Specifies the total amount of dynamic memory the server is using for maintaining connections. (Perf. Counter: Memory Manager - Connection Memory (KB))
Shown as kibibyte
sqlserver.memory.database_cache
(gauge)
Specifies the amount of memory the server is currently using for the database pages cache. (Perf. Counter: Memory Manager - Database Cache Memory (KB))
Shown as kibibyte
sqlserver.memory.granted_workspace
(gauge)
Specifies the total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, and index creation operations. (Perf. Counter: Memory Manager - Granted Workspace Memory (KB))
Shown as kibibyte
sqlserver.memory.grants_outstanding
(gauge)
Specifies the total number of processes that have successfully acquired a workspace memory grant. (Perf. Counter: Memory Manager - Memory Grants Outstanding)
sqlserver.memory.lock
(gauge)
Specifies the total amount of dynamic memory the server is using for locks. (Perf. Counter: Memory Manager - Lock Memory (KB))
Shown as kibibyte
sqlserver.memory.log_pool_memory
(gauge)
Total amount of dynamic memory the server is using for Log Pool. (Perf. Counter: Memory Manager - Log Pool Memory (KB))
Shown as kibibyte
sqlserver.memory.memory_grants_pending
(gauge)
Specifies the total number of processes waiting for a workspace memory grant (Perf. Counter: Memory Manager - Memory Grants Pending)
sqlserver.memory.optimizer
(gauge)
Specifies the total amount of dynamic memory the server is using for query optimization. (Perf. Counter: Memory Manager - Optimizer Memory (KB))
Shown as kibibyte
sqlserver.memory.sql_cache
(gauge)
Specifies the total amount of dynamic memory the server is using for the dynamic SQL cache. (Perf. Counter: Memory Manager - SQL Cache Memory (KB))
Shown as kibibyte
sqlserver.memory.stolen
(gauge)
Specifies the amount of memory the server is using for purposes other than database pages. (Perf. Counter: Memory Manager - Stolen Server Memory (KB))
Shown as kibibyte
sqlserver.memory.total_server_memory
(gauge)
Specifies the amount of memory the server has committed using the memory manager. (Perf. Counter: Memory Manager - Total Server Memory (KB))
Shown as kibibyte
sqlserver.procedures.count
(count)
Total count of executed stored procedures per procedure (DBM only). Tags: db, procedure_name.
Shown as query
sqlserver.procedures.logical_reads
(count)
Total number of logical reads performed by executed stored procedures per procedure (DBM only). Tags: db, procedure_name.
Shown as read
sqlserver.procedures.logical_writes
(count)
Total number of logical writes performed by executed stored procedures per procedure (DBM only). Tags: db, procedure_name.
Shown as write
sqlserver.procedures.physical_reads
(count)
Total number of physical reads performed by executed stored procedures per procedure (DBM only). Tags: db, procedure_name.
Shown as read
sqlserver.procedures.spills
(count)
The total number of pages spilled by execution of this stored procedure per procedure (DBM only). Tags: db, procedure_name.
sqlserver.procedures.time
(count)
Total elapsed time for executed stored procedures per procedure (DBM only)
Shown as nanosecond
sqlserver.procedures.worker_time
(count)
Total CPU time consumed by executed stored procedures per procedure (DBM only). Tags: db, procedure_name.
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.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.count
(count)
Total count of executed queries per query (DBM only)
Shown as query
sqlserver.queries.dop
(count)
The total sum of degree of parallelism used by executions 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
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.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.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.physical_reads
(count)
Total number of physical reads performed by executed queries per query (DBM only)
Shown as read
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.rows
(count)
Total number of rows returned by executed queries per query (DBM only)
Shown as row
sqlserver.queries.spills
(count)
The total number of pages spilled by execution of this query per query (DBM only)
sqlserver.queries.time
(count)
Total elapsed time for executed queries per query (DBM only)
Shown as nanosecond
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.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.worker_time
(count)
Total CPU time consumed by executed queries per query (DBM only)
Shown as nanosecond
sqlserver.replica.flow_control_sec
(gauge)
Number of times flow-control initiated in the last second. Flow Control Time (ms/sec) divided by Flow Control/sec is the average time per wait. (Perf. Counter: Database Replica - Flow Control/sec)
sqlserver.replica.transaction_delay
(gauge)
Total delay in waiting for unterminated commit acknowledgment for all the current transactions, in milliseconds. (Perf. Counter: Database Replica - Transaction Delay)
Shown as millisecond
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. Tags: 'scheduler_id', parent_node_id
Shown as worker
sqlserver.scheduler.current_tasks_count
(gauge)
Number of current tasks that are associated with this scheduler. Tags: 'scheduler_id', parent_node_id
Shown as task
sqlserver.scheduler.current_workers_count
(gauge)
Number of workers that are associated with this scheduler. Tags: 'scheduler_id', parent_node_id
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. Tags: 'scheduler_id', parent_node_id
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. Tags: 'scheduler_id', parent_node_id
Shown as unit
sqlserver.server.committed_memory
(gauge)
The amount of memory committed to the memory manager
Shown as byte
sqlserver.server.cpu_count
(gauge)
The number of logical CPUs or vCPUs on the server.
Shown as byte
sqlserver.server.physical_memory
(gauge)
Total physical memory on the machine
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.server.uptime
(gauge)
Total time elapsed since the last computer restart.
Shown as second
sqlserver.server.virtual_memory
(gauge)
Amount of virtual memory available to the process in user mode.
Shown as byte
sqlserver.stats.auto_param_attempts
(gauge)
Number of auto-parameterization attempts per second. (Perf. Counter: SQL Statistics - Auto-Param Attempts/sec)
Shown as attempt
sqlserver.stats.batch_requests
(gauge)
The number of batch requests per second. (Perf. Counter: Buffer Manager - Batch Requests/sec)
Shown as request
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.failed_auto_param_attempts
(gauge)
Number of failed auto-parameterization attempts per second. (Perf. Counter: SQL Statistics - Failed Auto-Params/sec)
Shown as attempt
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. (Perf. Counter: Locks - Lock Waits/sec)
Shown as lock
sqlserver.stats.procs_blocked
(gauge)
The number of processes blocked. (Perf. Counter: General Statistics - Processes blocked)
Shown as process
sqlserver.stats.safe_auto_param_attempts
(gauge)
Number of safe auto-parameterization attempts per second. (Perf. Counter: SQL Statistics - Safe Auto-Params/sec)
Shown as attempt
sqlserver.stats.sql_compilations
(gauge)
The number of SQL compilations per second. (Perf. Counter: SQL Statistics - SQL Compilations/sec)
Shown as operation
sqlserver.stats.sql_recompilations
(gauge)
The number of SQL re-compilations per second. (Perf. Counter: SQL Statistics - SQL Re-Compilations/sec)
Shown as operation
sqlserver.task.context_switches_count
(gauge)
Number of scheduler context switches that this task has completed. Tags: 'scheduler_id'
Shown as unit
sqlserver.task.pending_io_byte_average
(gauge)
Average byte count of I/Os that are performed by this task. Tags: 'scheduler_id'
Shown as byte
sqlserver.task.pending_io_byte_count
(gauge)
Total byte count of I/Os that are performed by this task. Tags: 'scheduler_id'
Shown as byte
sqlserver.task.pending_io_count
(gauge)
Number of physical I/Os that are performed by this task. Tags: 'scheduler_id'
Shown as unit
sqlserver.tempdb.file_space_usage.free_space
(gauge)
The amount of free space in the tempdb database file. Note: This metric is not emitted on Azure managed databases. Tags: db
Shown as mebibyte
sqlserver.tempdb.file_space_usage.internal_object_space
(gauge)
The amount of space used by internal objects in the tempdb database file. Note: This metric is not emitted on Azure managed databases. Tags: db
Shown as mebibyte
sqlserver.tempdb.file_space_usage.mixed_extent_space
(gauge)
The amount of space used by mixed extents in the tempdb database file. Note: This metric is not emitted on Azure managed databases. Tags: db
Shown as mebibyte
sqlserver.tempdb.file_space_usage.user_object_space
(gauge)
The amount of space used by user objects in the tempdb database file. Note: This metric is not emitted on Azure managed databases. Tags: db
Shown as mebibyte
sqlserver.tempdb.file_space_usage.version_store_space
(gauge)
The amount of space used by the version store in the tempdb database file. Note: This metric is not emitted on Azure managed databases.
Shown as mebibyte
sqlserver.transactions.longest_transaction_running_time
(gauge)
The time (in seconds) that the oldest active transaction has been running. Only works if database is under read committed snapshot isolation level. (Perf. Counter: Transactions - Longest Transaction Running Time)
Shown as second
sqlserver.transactions.version_cleanup_rate
(gauge)
The cleanup rate of the version store in tempdb. (Perf. Counter: Transactions - Version Cleanup rate (KB/s))
Shown as kibibyte
sqlserver.transactions.version_generation_rate
(gauge)
The generation rate of the version store in tempdb. (Perf. Counter: Transactions - Version Generation rate (KB/s))
Shown as kibibyte
sqlserver.transactions.version_store_size
(gauge)
The size of the version store in tempdb. (Perf. Counter: Transactions - Version Store Size (KB))
Shown as kibibyte

La plupart de ces métriques proviennent de la table sys.dm_os_performance_counters de SQL Server.

Événements

Le check SQL Server n’inclut aucun événement.

Checks de service

sqlserver.can_connect :
Renvoie CRITICAL si l’Agent ne parvient pas à se connecter à SQL Server pour recueillir des métriques. Si ce n’est pas le cas, renvoie OK.

Dépannage

Besoin d’aide ? Contactez l’assistance Datadog.

Développement

Consultez la documentation principale sur les outils de développement pour découvrir comment tester et développer des intégrations reposant sur l’Agent.

Pour aller plus loin