Microsoft SQL Server OpenTelemetry Assets

Microsoft SQL Server is a relational database management system used for OLTP, data warehousing, and mixed workloads. These assets provide dashboards, alert rules, and SLO templates for monitoring SQL Server using the OpenTelemetry sqlserverreceiver, covering memory, concurrency, I/O, TempDB, and query performance.

The Microsoft SQL Server OpenTelemetry assets have been tested with OpenTelemetry SQL Server receiver v0.146.0.

SQL Server tested against:

• Microsoft SQL Server 2019, 2022

You need Elasticsearch for storing and searching your data and Kibana for visualizing and managing it. You can use our hosted Elasticsearch Service on Elastic Cloud, which is recommended, or self-manage the Elastic Stack on your own hardware.

When using direct connection mode (recommended for Linux and container deployments), the SQL Server user must have:

1. At least one of: CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DATABASE
2. VIEW SERVER STATE (SQL Server pre-2022) or VIEW SERVER PERFORMANCE STATE (SQL Server 2022 and later)

On Windows, you can alternatively use Windows Performance Counters; run the collector as administrator to collect all counters.

Configure the OpenTelemetry Collector or Elastic Distribution of OpenTelemetry (EDOT) Collector with the SQL Server receiver and the Elasticsearch exporter. Use the following placeholders in the YAML:

• <SQL_SERVER_HOST> — Hostname or IP address of the SQL Server instance (for example sqlserver.example.com)
• <SQL_SERVER_PORT> — Port of the SQL Server instance (default 1433)
• <SQL_SERVER_USER> — Username for SQL Server authentication
• <SQL_SERVER_PASSWORD> — Password; use ${env:SQL_SERVER_PASSWORD} to read from an environment variable

receivers:
  sqlserver:
    collection_interval: 10s
    server: <SQL_SERVER_HOST>
    port: <SQL_SERVER_PORT>
    username: <SQL_SERVER_USER>
    password: ${env:SQL_SERVER_PASSWORD}
    events:
      db.server.query_sample:
        enabled: true
      db.server.top_query:
        enabled: true
    top_query_collection:
      lookback_time: 60s
      max_query_sample_count: 1000
      top_query_count: 250
      collection_interval: 60s
    query_sample_collection:
      max_rows_per_query: 100

exporters:
  elasticsearch/otel:
    endpoints: ["https://<ES_ENDPOINT>"]
    auth:
      api_key: "${env:ES_API_KEY}"
    mapping:
      mode: otel

service:
  pipelines:
    metrics/sqlserver:
      receivers: [sqlserver]
      exporters: [elasticsearch/otel]
    logs/sqlserver:
      receivers: [sqlserver]
      exporters: [elasticsearch/otel]
		

Refer to the metadata.yaml of the OpenTelemetry SQL Server receiver for details on available metrics.

The receiver emits log events for db.server.query_sample (currently executing queries) and db.server.top_query (most expensive queries). These events include query text, session and blocking details, CPU time, logical reads, and wait types. Refer to the logs-documentation.md for the full schema.

This integration includes one or more Kibana dashboards that visualizes the data collected by the integration. The screenshots below illustrate how the ingested data is displayed.

← →

×

×

×

×

×