How to monitor a SQL Server instance using OpenTelemetry

August 19, 2026 · Robert Mayer

When the database slows, every integration slows

Almost every integration touches a database somewhere — a staging table, a lookup, a transactional write. SQL Server is one of the most common, and it’s a classic hidden cause of integration pain. Lock waits and blocked sessions back up the services on top of it. Low page life expectancy means memory pressure and slow reads. Connection-pool exhaustion — often caused by one misbehaving integration — locks everyone else out. If you only watch the application, you see “everything is slow” with no idea why. The answer is usually in the database.

Why OpenTelemetry — not a proprietary agent

The usual route is a database-specific monitoring agent that ships SQL Server metrics into one vendor’s cloud, in that vendor’s format. It works, but it’s another proprietary agent to deploy and license, and the data lands in a silo separate from the rest of your telemetry — so you still can’t easily line up “the database started blocking” with “the integration started timing out.”

OpenTelemetry has a SQL Server receiver in the Collector. One standard pipeline collects SQL Server’s performance metrics and sends them, over OTLP, into the same backend as your services, your message broker, and your file flows. Because OTLP is an open standard implemented broadly, the configuration is portable and the data is yours — self-host the Collector and nothing about your database leaves your environment. The database stops being a separate monitoring island and becomes part of the integration picture.

One expectation to set: in the contrib distribution the receiver’s metrics are at beta stability and its logs are still in development. Beta is entirely usable — plenty of people run this in production — but pin your Collector version and read the changelog before upgrading.

Two ways to connect

The SQL Server receiver works in one of two modes:

  1. Windows performance counters — run the Collector on the Windows host running SQL Server; it reads the perf counters locally. Only available on Windows, and you’ll want to run the Collector as administrator to get the full counter set.
  2. Direct connection — the Collector connects to the instance over the standard database protocol and queries system DMVs. This is cross-platform (you can run the Collector on Linux) and it unlocks a large set of additional metrics — locks, memory areas, per-file I/O and latency, index internals, resource pools — that perf counters don’t expose.

For most integration estates the direct connection is the better choice: it’s portable and gives you the richer metric set.

Which SQL Server are you actually monitoring?

“SQL Server” covers three quite different products in Azure, and they don’t behave the same. Sort this out before you write any config, because it decides your connection mode, your permissions, and whether half this post applies to you.

On-prem or Azure VM (IaaS) Azure SQL Managed Instance Azure SQL Database (single / elastic pool)
Connection mode Perf counters or direct Direct only — no host to run on Direct only
View-state permission VIEW SERVER STATE (pre-2022) / VIEW SERVER PERFORMANCE STATE (2022+) VIEW SERVER STATE VIEW DATABASE STATE — and on Basic, S0, S1 and elastic pools, the server admin, the Entra admin, or ##MS_ServerPerformanceStateReader##
SQL Agent and msdb Yes Yes No — Elastic Jobs or Azure Automation instead
The Agent job query below Works Works Doesn’t run at all

The practical upshot: on IaaS everything here applies unchanged — it’s ordinary SQL Server on a host you own. On Managed Instance you lose the perf-counter mode and keep almost everything else. On Azure SQL Database you’re in different territory: different permission model, no msdb, and no Agent jobs to ask about.

Be honest with yourself about that third column before you budget a morning for this. And note that the upstream receiver docs don’t make any claim about Azure support either way — they talk about “a SQL Server instance” and leave the platform question alone — so if you’re on PaaS, run it against a non-production database first and see which metrics actually arrive.

One thing that is portable across all three: the SQL query receiver covered further down. It’s an ordinary database connection — if the query runs in SSMS, it becomes a metric. Which is a decent argument for leaning on it rather than on platform-specific server metrics.

Prerequisites

An OpenTelemetry Collector build with contrib receivers (e.g. otelcol-contrib), and a dedicated monitoring login on the SQL Server instance.

For direct connection, that login needs two things — and this is the step that trips most people up, because the receiver fails quietly with a thin metric set rather than a loud permission error:

  1. At least one of CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DATABASE.
  2. Permission to view server state: SQL Server pre-2022 needs VIEW SERVER STATE; SQL Server 2022 and later needs VIEW SERVER PERFORMANCE STATE.

VIEW ANY DATABASE plus the appropriate view-state permission is the least-privilege combination for a read-only monitoring account.

The Collector configuration (direct connection)

Diagram made with OTelFlow — paste a Collector config, get a shareable flow diagram, free.

To enable direct connection you must specify username, password, server, and port together — they’re individually optional, but all four are required to switch the mode on. For finer control use a datasource connection string instead; it can’t be combined with the other four.

A note on the exporter name: the current component name is otlp_http. The older otlphttp still works as a deprecated alias and will be removed in a future release, so use otlp_http in new configs and update old ones when convenient. (This is part of an upstream effort to snake-case all multi-word component names.)

What to actually watch (the integration lens)

Here’s where the two connection modes really diverge — and where the docs are easy to misread. Some of the most useful metrics are both direct-connection-only and disabled by default, so turning on a direct connection alone won’t make them appear.

What you want to see Metric Availability
Throughput pulse sqlserver.batch.request.rate On by default, either mode
Memory pressure sqlserver.page.life_expectancy On by default, either mode
Connections climbing toward the pool limit sqlserver.user.connection.count On by default, either mode
Lock contention, as a rate sqlserver.lock.wait.rate On by default, either mode
Average lock wait time sqlserver.lock.wait_time.avg On by default, Windows only
Blocked processes right now sqlserver.processes.blocked Direct connection + opt-in
Cumulative lock waits sqlserver.lock.wait.count, sqlserver.lock.wait_time.total Direct connection + opt-in
Per-file I/O, latency and operations sqlserver.database.io, sqlserver.database.latency, sqlserver.database.operations Direct connection + opt-in

The four defaults give you a decent pulse for free. The opt-in ones are the diagnostic payload — sqlserver.processes.blocked in particular is the single most direct answer to “why did every integration on this database stall at once.”

Enable them explicitly:

receivers:
  sqlserver:
    # ...connection settings as above...
    metrics:
      sqlserver.processes.blocked:
        enabled: true
      sqlserver.database.io:
        enabled: true
      sqlserver.database.latency:
        enabled: true
      sqlserver.lock.wait_time.total:
        enabled: true

The per-file metrics carry physical_filename, logical_filename, file_type and direction attributes, so you can tell a slow data file from a slow log file rather than just “disk is bad.”

Check documentation.md in collector-contrib for the exact metric set in your Collector version — the optional list is long (60-plus metrics) and it moves.

Beyond server health: monitoring your actual data

Here’s the part that matters most, and the part almost every SQL Server monitoring guide leaves out.

Everything above tells you the instance is healthy. None of it tells you whether the integration is working. “The database is fine” is cold comfort when four thousand orders are sitting in a staging table because nobody is draining it — the server is genuinely, measurably fine, and the business is stopped. Server metrics answer “is SQL Server having a bad day.” Integration monitoring needs to answer “is the data moving,” and no amount of page life expectancy will tell you that.

Two things get you there, both still inside OpenTelemetry: the receiver’s own query logs, and — the important one — your own SQL, turned into metrics.

The receiver’s own query logs

The SQL Server receiver can emit logs as well as metrics: top_query_collection reports the most expensive queries over a lookback window, and query_sample_collection captures currently-executing queries. When an integration slows down and the infrastructure metrics look unremarkable, this is usually where the answer is — one unindexed query in a nightly job. Both are still development-stability, so treat them as a diagnostic aid rather than something to alert on:

receivers:
  sqlserver:
    # ...connection settings as above...
    events:
      db.server.top_query:
        enabled: true
      db.server.query_sample:
        enabled: true
    top_query_collection:
      lookback_time: 60s
      top_query_count: 200

Monitoring the data itself: the SQL query receiver

If you take one thing from this post, take this one.

The Collector’s SQL query receiver (sqlqueryreceiver) runs a query you write on an interval and turns the result into metrics or logs. Any query. Your tables, your definition of healthy. It’s the only part of SQL Server monitoring that knows what your business does, and it’s the part that turns a database from infrastructure you watch into an integration you can actually monitor.

It’s also the portable option — it works the same on-prem, on a VM, on Managed Instance and on Azure SQL Database, because it’s just a database connection. If the query runs in SSMS, it becomes a metric.

Start with backlog depth. Group by the status column and one query covers every state at once:

receivers:
  sqlquery:
    driver: sqlserver
    datasource: "sqlserver://otel_monitor:${env:SQLSERVER_PASSWORD}@10.0.0.10:1433?database=orders"
    collection_interval: 60s
    queries:
      - sql: "SELECT state, COUNT(*) AS row_count FROM dbo.orders GROUP BY state"
        metrics:
          - metric_name: orders.rows
            value_column: row_count
            attribute_columns: [state]

One metric, one attribute, and you can now alert on state=pending climbing.

But depth alone is the weaker signal. A pending count of 400 might be a busy Tuesday afternoon or a queue that hasn’t moved since 02:00 — identical number, very different night. The signal that separates them is age:

      - sql: >
          SELECT DATEDIFF(second, MIN(created_at), SYSUTCDATETIME()) AS oldest_pending_seconds
          FROM dbo.orders WHERE state = 'pending'
        metrics:
          - metric_name: orders.pending.oldest_age
            value_column: oldest_pending_seconds
            unit: s

Depth tells you how much work is waiting. Age tells you whether anything is moving. Alert on the second one and you catch the stall that a count would have hidden behind normal-looking volume — the same argument as monitoring file age on a drop folder, applied to a table.

Scheduled jobs: the silent failure inside the database

Monitoring scope isn’t always a whole instance. Often what you care about is one or two databases, or a handful of SQL Agent jobs that quietly move data every night.

That last category is its own outage class. A job that runs the nightly export stops firing — disabled by someone during a migration, or failing on step 3 every time — and nothing in the server metrics moves at all. CPU normal, locks normal, batch requests normal. Every dashboard green, zero exports. SQL Server knows; nobody asked it.

msdb has the answer, so ask it on a schedule — on IaaS or Managed Instance, at least; Azure SQL Database has no SQL Agent and no msdb, so this one doesn’t apply there (see the table above):

      - sql: >
          SELECT j.name AS job_name,
                 DATEDIFF(second,
                          MAX(msdb.dbo.agent_datetime(h.run_date, h.run_time)),
                          SYSDATETIME()) AS seconds_since_last_success
          FROM msdb.dbo.sysjobs j
          JOIN msdb.dbo.sysjobhistory h ON h.job_id = j.job_id
          WHERE h.step_id = 0 AND h.run_status = 1
          GROUP BY j.name
        metrics:
          - metric_name: sqlagent.job.seconds_since_last_success
            value_column: seconds_since_last_success
            attribute_columns: [job_name]
            unit: s

step_id = 0 is the job-outcome row and run_status = 1 is success, so this measures time since each job last finished cleanly. A job that should run hourly and now reads 40,000 seconds is a silent outage you’d otherwise find out about from a partner.

Two practical notes: reading msdb.dbo.sysjobs needs more than the monitoring permissions above — SQLAgentReaderRole in msdb at minimum — and agent_datetime returns server-local time, so pair it with SYSDATETIME() rather than the UTC variant unless your server runs on UTC.

This is also the honest answer to “do I have to monitor the whole instance?” No. Point the SQL query receiver at the two databases and five jobs you actually depend on, and skip the rest.

Troubleshooting: x509: negative serial number

If you’re testing against SQL Server in Docker, you may hit the Collector failing to parse the server certificate with x509: negative serial number. It’s not your config. Contrib adopted Go 1.23 in v0.121.0, and Go 1.23 stopped accepting certificates with negative serial numbers — which the mssql Docker image generates. Restore the old behaviour with an environment variable on the Collector:

GODEBUG=x509negativeserial=1

The point

Server health is the floor, not the goal. Collecting it through OpenTelemetry — once, in a portable and vendor-neutral way — puts the database in the same pane as the services and queues around it, so the next time an integration mysteriously slows down, the cause is one correlation away instead of a separate tool you have to go open.

But the metrics that will actually wake you up for a good reason are the ones you wrote yourself: rows waiting, oldest row age, job last succeeded. Those are the ones that know what your integration is for. The sqlserver receiver tells you the database is having a bad day. The sqlquery receiver tells you the orders stopped moving at 02:14 — which is the thing anyone was ever going to call you about.