SQL Server

Supported OS Linux Mac OS Windows

통합 버전22.6.0

SQL Server 그래프

개요

SQL Server 통합은 SQL Server 인스턴스의 성능을 추적하며, 사용자 연결 수, SQL 컴파일 속도 등 여러 메트릭을 수집합니다.

쿼리 성능 및 데이터베이스 상태를 더욱 정밀하게 모니터링하려면 Database Monitoring(DBM)을 활성화하세요. Datadog DBM은 표준 통합 외에도 쿼리 수준 메트릭, 실시간 및 과거 쿼리 스냅샷, 대기 이벤트 분석, 데이터베이스 부하, 쿼리 실행 계획, 차단 쿼리 인사이트를 제공합니다.

SQL Server 2012, 2014, 2016, 2017, 2019, 2022가 지원됩니다.

설정

이 페이지는 SQL Server Agent 표준 통합에 관해 다룹니다. SQL Server용 Database Monitoring에 관한 정보는 Datadog Database Monitoring을 참고하세요.

설치

SQL Server 점검은 Datadog Agent 패키지에 포함되어 있어, SQL Server 인스턴스에 추가로 설치할 필요가 없습니다.

서버 속성에서 “SQL Server and Windows Authentication mode"를 활성화하여 SQL Server 인스턴스가 SQL Server 인증을 지원하는지 확인하세요.

Server Properties -> Security -> SQL Server and Windows Authentication mode

사전 요구 사항

참고: SQL Server용 Database Monitoring을 설치하려면 문서 사이트에서 호스팅 솔루션을 선택하여 관련 정보를 확인하세요.

SQL Server 점검에서 지원되는 SQL Server 버전은 Database Monitoring에서 지원되는 버전과 동일합니다. 현재 지원되는 버전은 Setting up SQL Server 페이지의 Self-hosted 항목에서 확인할 수 있습니다.

표준 통합을 단독으로 설치하는 경우에만 이 가이드의 다음 단계를 진행하세요.

서버에 연결하려면 읽기 전용 로그인을 생성합니다.

    CREATE LOGIN datadog WITH PASSWORD = '<PASSWORD>';
    USE master;
    CREATE USER datadog FOR LOGIN datadog;
    GRANT SELECT on sys.dm_os_performance_counters to datadog;
    GRANT VIEW SERVER STATE to datadog;

데이터베이스당 파일 크기 메트릭을 수집하려면 다음을 실행하여 생성한 사용자(datadog)가 데이터베이스에 대한 연결 권한 액세스를 가지고 있는지 확인하세요.

    GRANT CONNECT ANY DATABASE to datadog;

(AlwaysOn 및 sys.master_files 메트릭에 필요) AlwaysOn 및 sys.master_files 메트릭을 수집하려면 다음 추가 권한을 부여하세요.
```
    GRANT VIEW ANY DEFINITION to datadog;
```

설정

호스트

호스트에서 실행 중인 에이전트에 이 점검을 구성하는 방법:

Agent 구성 디렉터리의 루트의 conf.d/ 폴더에 있는 sqlserver.d/conf.yaml 파일을 편집합니다. 사용 가능한 모든 구성 옵션은 샘플 sqlserver.d/conf.yaml에서 확인하세요.
```
init_config:

instances:
  - host: "<SQL_HOST>,<SQL_PORT>"
    username: datadog
    password: "<YOUR_PASSWORD>"
    connector: adodbapi
    adoprovider: MSOLEDBSQL19  # Replace with MSOLEDBSQL for versions 18 and previous
```
포트 autodiscovery를 사용한다면 SQL_PORT에 0을 입력하세요. 예시 점검 구성에서 커스텀 쿼리로 자체 메트릭을 생성하는 방법을 포함한 전체 옵션 정보를 확인해 보세요.
SQL Server 설정에 따라 지원되는 드라이버를 사용하세요.
참고: Windows Authentication을 사용하면 사용자 이름과 비밀번호를 별도로 지정하지 않아도 됩니다. 방법은 다음과 같습니다.
```
connection_string: "Trusted_Connection=yes"
```
에이전트를 재시작합니다.

Linux

Linux 호스트에서 SQL Server 통합을 실행하려면 추가 구성 단계가 필요합니다.

예를 들어 Microsoft ODBC 드라이버 또는 FreeTDS 드라이버와 같은 ODBC SQL Server 드라이버를 설치합니다.
odbc.ini과 odbcinst.ini 파일을 /opt/datadog-agent/embedded/etc 폴더에 복사합니다.
odbc 커넥터를 사용하도록 conf.yaml 파일을 구성하고 odbcinst.ini 파일에 표시된 대로 적절한 드라이버를 지정합니다.

로그 수집

에이전트 버전 > 6.0에서 사용 가능

로그 수집은 Datadog 에이전트에서 기본적으로 비활성화되어 있습니다. datadog.yaml 파일에서 활성화합니다.
```
logs_enabled: true
```
이 구성 블록을 sqlserver.d/conf.yaml 파일에 추가하여 SQL Server 로그 수집을 시작합니다.
```
logs:
  - type: file
    encoding: utf-16-le
    path: "<LOG_FILE_PATH>"
    source: sqlserver
    service: "<SERVICE_NAME>"
```
path 및 service 파라미터 값을 환경에 따라 변경합니다. 사용 가능한 모든 구성 옵션은 샘플 sqlserver.d/conf.yaml을 참고하세요.
에이전트를 재시작합니다.

컨테이너화된 환경

컨테이너화된 환경의 경우 자동탐지 통합 템플릿에 아래 파라미터를 적용하는 방법이 안내되어 있습니다.

메트릭 수집

파라미터	값
`<INTEGRATION_NAME>`	`sqlserver`
`<INIT_CONFIG>`	비어 있음 또는 `{}`
`<INSTANCE_CONFIG>`	`{"host": "%%host%%,%%port%%", "username": "datadog", "password": "<UNIQUEPASSWORD>", "connector": "odbc", "driver": "FreeTDS"}`

<UNIQUEPASSWORD>를 레이블 대신 환경 변수로 전달하려면 Autodiscovery 템플릿 변수를 참고하세요.

로그 수집

에이전트 버전 > 6.0에서 사용 가능

Datadog 에이전트에서 기본적으로 로그 수집이 비활성화되어 있습니다. 활성화하려면 [쿠버네티스 로그 수집]을 확인하세요.

파라미터	값
`<LOG_CONFIG>`	`{"source": "sqlserver", "service": "sqlserver"}`

검증

Agent의 상태 하위 명령을 실행하고 Checks 섹션에서 sqlserver를 찾습니다.

수집한 데이터

메트릭


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.agent.active_jobs.duration (gauge)	Duration of currently running jobs on sqlserver agent (DBM Only) Shown as second
sqlserver.agent.active_jobs.step_info (gauge)	Presence of a most recent completed step for active jobs running on the sqlserver agent (DBM Only)
sqlserver.agent.completed_jobs.duration (gauge)	Duration of completed jobs on sqlserver agent (DBM Only) Shown as second
sqlserver.agent.completed_jobs.executions (gauge)	Number of executions of completed jobs on sqlserver agent (DBM Only) Shown as execution
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 IN_ROW_DATA allocation unit. Tags: `db`, `object_name`, `schema`, `index_id`, `index_name`
sqlserver.database.avg_fragmentation_in_percent (gauge)	Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. Tags: `db`, `object_name`, `schema`, `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 IN_ROW_DATA allocation unit. Tags: `db`, `object_name`, `schema`, `index_id`, `index_name`
sqlserver.database.index_page_count (gauge)	Total number of index or data pages. Tags: `db`, `object_name`, `schema`, `index_id`, `index_name`
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.is_read_only (gauge)	Whether or not the database is marked as READ_ONLY. 0 = READ_WRITE, 1 = READ_ONLY. Tags: `db`, `database_state_desc`, `database_recovery_model_desc`
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.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.replica.transaction_delay (gauge)	Total delay in waiting for unterminated commit acknowledgment for all the current transactions, in milliseconds. Tags: `db`. (Perf. Counter: `Database Replica - Transaction Delay`) Shown as millisecond
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. 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.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 (count)	Total time that users waited for reads on the file. Tags: `logical_name`, `file_location`, `db`, `state` Shown 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`, `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 (count)	Total time that users waited for writes on the file. Tags: `logical_name`, `file_location`, `db`, `state` Shown 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`, `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`, `schema` 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`, `schema` Shown as scan
sqlserver.index.user_seeks (count)	Number of seeks by user queries. Tags: `db`, `table`, `index_name`, `schema` 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`, `schema` 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.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_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_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)	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.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.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 kilobyte
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 kilobyte
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 kilobyte
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.table.data_size (gauge)	The size in kibibytes of data stored in this table excluding internal index pages and allocation-management pages. Tags: `database` `schema` `table` Shown as kibibyte
sqlserver.table.row_count (gauge)	The number of rows in this table. Tags: `database` `schema` `table` Shown as row
sqlserver.table.total_size (gauge)	The total size of the table in kibibytes. Tags: `database` `schema` `table` Shown as kibibyte
sqlserver.table.used_size (gauge)	The size in kibibytes of data stored in this table including internal index pages and allocation-management pages. Tags: `database` `schema` `table` Shown as kibibyte
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
sqlserver.xe.events_not_in_xml (gauge)	Number of generated events that are missing in the XML representation of the ring buffer. Tags: `session_name` Shown as event
sqlserver.xe.session_status (gauge)	Status of the node in a SQL Server failover cluster instance. Tags: `session_name`

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

대부분의 메트릭은 SQL Server의 sys.dm_os_performance_counters 테이블에서 제공됩니다.

이벤트

SQL Server 점검은 이벤트를 포함하지 않습니다.

서비스 점검

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

트러블슈팅

도움이 필요하신가요? Datadog 지원팀에 문의하세요.

Agent를 ARM aarch64 프로세서에서 실행하면 점검 14.0.0 버전부터 알려진 문제가 발생합니다. Python 종속성이 로드되지 않고 Agent의 상태 하위 명령을 실행할 때 다음 메시지가 표시됩니다.

Loading Errors
  ==============
    sqlserver
    ---------
      Core Check Loader:
        Check sqlserver not found in Catalog
      JMX Check Loader:
        check is not a jmx check, or unable to determine if it's so
      Python Check Loader:
        unable to import module 'sqlserver': No module named 'sqlserver'

이 문제는 점검 버전 15.2.0과 Agent 버전 7.49.1 이상부터 발생하지 않습니다.

SQL Server

개요

설정

설치

사전 요구 사항

설정

호스트

Linux

로그 수집

컨테이너화된 환경

메트릭 수집

로그 수집

검증

수집한 데이터

메트릭

이벤트

서비스 점검

트러블슈팅

참고 자료