SQL Server Metrics

Docs > OpenTelemetry in Datadog > Integrations > SQL Server Metrics

Overview

The SQL Server receiver allows for collection of SQL Server metrics and access to the SQL Server Overview and SQL Server Metrics dashboards. Configure the receiver according to the specifications of the latest version of the sqlserverreceiver.

For more information, see the OpenTelemetry project documentation for the SQL Server receiver.

Setup

To collect SQL Server metrics with OpenTelemetry for use with Datadog:

Configure the SQL Server receiver in your OpenTelemetry Collector configuration.
Optionally, configure the host metrics receiver if your OpenTelemetry Collector is running on the same server as your SQL Server database.
Optionally, configure the file log receiver if your OpenTelemetry Collector is running on the same server as your SQL Server database.
Configure service pipelines.
Ensure the OpenTelemetry Collector is configured to export to Datadog.

SQL Server receiver

The SQL Server receiver supports two collection methods: Windows Performance Counters (Windows only) or direct connection. This configuration uses the direct connection method.

receivers:
  sqlserver:
    server: "<HOST>"
    port: <PORT>
    username: "<USERNAME>"
    password: "<PASSWORD>"
    collection_interval: 15s
    metrics:
      sqlserver.processes.blocked:
        enabled: true
      sqlserver.cpu.count:
        enabled: true
      sqlserver.computer.uptime:
        enabled: true
      sqlserver.database.io:
        enabled: true
      sqlserver.database.latency:
        enabled: true
      sqlserver.database.operations:
        enabled: true

processors:
  resource:
    attributes:
      - key: host.name
        value: "<HOST>"
        action: upsert
  transform/fix_rate_counters:
    metric_statements:
      - context: metric
        statements:
          - convert_gauge_to_sum("cumulative", true) where metric.name == "sqlserver.batch.request.rate"
          - convert_gauge_to_sum("cumulative", true) where metric.name == "sqlserver.batch.sql_compilation.rate"
          - convert_gauge_to_sum("cumulative", true) where metric.name == "sqlserver.batch.sql_recompilation.rate"
          - convert_gauge_to_sum("cumulative", true) where metric.name == "sqlserver.lock.wait.rate"
  cumulativetodelta:
    include:
      match_type: strict
      metrics:
        - sqlserver.database.io
        - sqlserver.database.latency
        - sqlserver.database.operations
        - sqlserver.batch.request.rate
        - sqlserver.batch.sql_compilation.rate
        - sqlserver.batch.sql_recompilation.rate
        - sqlserver.lock.wait.rate
  deltatorate:
    metrics:
      - sqlserver.batch.request.rate
      - sqlserver.batch.sql_compilation.rate
      - sqlserver.batch.sql_recompilation.rate
      - sqlserver.lock.wait.rate

See the SQL Server receiver documentation for detailed configuration options and requirements.

Host metrics receiver

receivers:
  hostmetrics:
    scrapers:
      load:
      cpu:
        metrics:
         system.cpu.utilization:
           enabled: true
      memory:
      network:

File log receiver

receivers:
  filelog:
    include:
      - <PATH_TO_YOUR_SQLSERVER_LOG>
    operators:
      - type: regex_parser
        regex: '^(?P<timestamp>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d+) (?P<message>.*)'
        timestamp:
          parse_from: attributes.timestamp
          layout: "%Y-%m-%d %H:%M:%S.%f"

processors:
  transform/logs:
    log_statements:
      - context: resource
        statements:
          - set(attributes["datadog.host.name"], "<HOST>")
          - set(attributes["datadog.log.source"], "sqlserver")

Service pipelines

service:
  pipelines:
    metrics:
      receivers: [sqlserver]
      processors: [resource, transform/fix_rate_counters, cumulativetodelta, deltatorate]
      exporters: [datadog/exporter]

If you configured the host metrics receiver, add it to a separate metrics pipeline:

    metrics/host:
      receivers: [hostmetrics]
      exporters: [datadog/exporter]

If you configured the file log receiver, add a logs pipeline:

    logs:
      receivers: [filelog]
      processors: [transform/logs]
      exporters: [datadog/exporter]

Data collected

OTEL	DESCRIPTION	FILTER
sqlserver.batch.request.rate	Number of batch requests received by SQL Server.
sqlserver.batch.sql_compilation.rate	Number of SQL compilations needed.
sqlserver.batch.sql_recompilation.rate	Number of SQL recompilations needed.
sqlserver.computer.uptime	Computer uptime.
sqlserver.cpu.count	Number of CPUs.
sqlserver.database.io	The number of bytes of I/O on this file.	`direction`: `read`
sqlserver.database.io	The number of bytes of I/O on this file.	`direction`: `write`
sqlserver.database.latency	Total time that the users waited for I/O issued on this file.	`direction`: `read`
sqlserver.database.latency	Total time that the users waited for I/O issued on this file.	`direction`: `write`
sqlserver.database.operations	The number of operations issued on the file.	`direction`: `write`
sqlserver.database.operations	The number of operations issued on the file.	`direction`: `read`
sqlserver.lock.wait.rate	Number of lock requests resulting in a wait.
sqlserver.page.buffer_cache.hit_ratio	Pages found in the buffer pool without having to read from disk.
sqlserver.page.life_expectancy	Time a page will stay in the buffer pool.
sqlserver.page.split.rate	Number of pages split as a result of overflowing index pages.
sqlserver.processes.blocked	The number of processes that are currently blocked
sqlserver.transaction.rate	Number of transactions started for the database (not including XTP-only transactions).
sqlserver.transaction.write.rate	Number of transactions that wrote to the database and committed.
sqlserver.user.connection.count	Number of users connected to the SQL Server.