Every environment I've worked in has the same blind spot: Kubernetes is instrumented within an inch of its life, the load balancers have dashboards, the message queues have dashboards, and then somewhere underneath all of it sits a SQL Server instance holding the actual ledger, completely dark. Nobody notices until a report starts timing out and the first question in the incident channel is "is the database okay?" - and nobody can answer it in under five minutes.
This is the setup I landed on to fix that: SQL Server's Dynamic Management Views feeding Prometheus through sql_exporter, visualized in Grafana, sitting on the same dashboard shelf as everything else. No agents installed on the database host, no changes to the SQL Server instance itself, just read-only queries against DMVs on a schedule.
Why SQL Server doesn't monitor like a Linux box
Node Exporter works because Linux exposes almost everything through /proc and /sys - stable, well-known files that a scraper can just read. SQL Server doesn't have an equivalent. What it has instead is a large set of Dynamic Management Views and Functions (sys.dmos, sys.dmexec, sys.dmdb*) that return the same kind of information, but as query results rather than files. That means "exporting metrics" from SQL Server really means "running a SQL query on an interval and turning the result set into a metric" - which is exactly what sql_exporter does.
The metrics that actually matter for an OLTP workload are different from generic host metrics too. CPU and memory on the box matter less than:
- Wait stats - what threads are actually waiting on, which tells you why things are slow, not just that they're slow
- Blocking chains - one session holding a lock that ten others are queued behind
- Page Life Expectancy (PLE) - how long pages survive in buffer cache before eviction, a proxy for memory pressure
- Batch requests/sec and compilations/sec - throughput and whether the plan cache is thrashing
- tempdb contention - a classic silent killer in busy OLTP systems
None of that comes from node_exporter. All of it comes from DMVs.
Setting up sql_exporter
sqlexporter runs as its own small service, connects to SQL Server over the standard driver, runs your configured queries, and exposes the results on /metrics for Prometheus to scrape - the same pattern as Node Exporter, just pointed at a database instead of a filesystem.
sql_exporter.yml:
global:
scrape_timeout: 10s
min_interval: 5s
target:
data_source_name: 'sqlserver://monitor_user:${MONITOR_PASSWORD}@sql-prod-01:1433?database=master'
collectors: ['sql_server_core']
collector_files:
- 'collectors/*.yml'Create a dedicated read-only login for this - never point it at an admin account:
CREATE LOGIN monitor_user WITH PASSWORD = 'use-a-real-secret-here';
CREATE USER monitor_user FOR LOGIN monitor_user;
GRANT VIEW SERVER STATE TO monitor_user;
GRANT VIEW ANY DEFINITION TO monitor_user;VIEW SERVER STATE is the key grant - it's what allows querying the sys.dmos and sys.dmexec views without also handing out data access.
The collector: DMV queries that earn their panel
This is collectors/sqlservercore.yml. Each metric is just a name, a help string, and the SQL that populates it.
collector_name: sql_server_core
metrics:
- metric_name: sqlserver_page_life_expectancy
type: gauge
help: 'Seconds a page stays in buffer pool before eviction'
values: [page_life_expectancy_seconds]
query: |
SELECT cntr_value AS page_life_expectancy_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
- metric_name: sqlserver_batch_requests_per_sec
type: counter
help: 'Cumulative batch requests processed'
values: [batch_requests]
query: |
SELECT cntr_value AS batch_requests
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec'
- metric_name: sqlserver_blocked_sessions
type: gauge
help: 'Number of sessions currently blocked by another session'
values: [blocked_count]
query: |
SELECT COUNT(*) AS blocked_count
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
- metric_name: sqlserver_wait_time_ms
type: gauge
help: 'Cumulative wait time in ms, by wait type'
key_labels: [wait_type]
values: [wait_time_ms]
query: |
SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
'SLEEP_TASK','SLEEP_SYSTEMTASK','BROKER_TO_FLUSH'
)
- metric_name: sqlserver_tempdb_free_mb
type: gauge
help: 'Free space in tempdb data files, MB'
values: [free_mb]
query: |
SELECT SUM(size * 8 / 1024) AS free_mb
FROM tempdb.sys.database_files
WHERE type = 0sqlserverwaittimems is the one that pays off the most. It's a cumulative counter per wait type, so once it's in Prometheus you can graph rate() over it and see exactly which wait category is climbing - PAGEIOLATCHSH climbing means storage latency, LCKMX climbing means lock contention, CXPACKET/CXCONSUMER climbing means parallelism skew. That's the difference between "the database is slow" and "the database is slow because of X," which is the entire point of doing this.
Wiring it into Prometheus
Standard scrape config, nothing SQL-specific about this part:
scrape_configs:
- job_name: 'sql_server'
scrape_interval: 15s
static_configs:
- targets: ['sql-exporter-host:9399']
labels:
instance: 'sql-prod-01'
environment: 'production'Building the dashboard
Four rows, roughly in the order I'd look at them during an incident:
Row 1 - Is it under pressure right now?
- Page Life Expectancy, single stat with thresholds (green > 300s, amber 100-300s, red < 100s)
- Batch Requests/sec: rate(sqlserverbatchrequestspersec[1m])
- Blocked sessions, single stat, red if > 0 for more than a minute
Row 2 - What's it waiting on?
- Top wait types, table panel sorted descending: topk(8, rate(sqlserverwaittime_ms[5m]))
- This single panel is usually the first thing the on-call DBA looks at
Row 3 - Is it going to run out of something?
- tempdb free space over time
- Buffer cache hit ratio
- Connection count vs. configured max
Row 4 - Longer trend context
- 7-day PLE trend (memory pressure creeping up over days is easy to miss on a single-day view)
- Batch requests/sec, 7-day, to catch slow throughput decay
A dashboard nobody looks at during an incident is decoration, not observability. If your top wait-types panel isn't the thing someone screen-shares in the first two minutes of a "why is the app slow" call, the layout is wrong - move it to row one.
Alerting on the same data
A handful of alert rules cover most of what actually pages people:
groups:
- name: sql_server
rules:
- alert: SQLServerLowPLE
expr: sqlserver_page_life_expectancy < 100
for: 5m
labels: { severity: warning }
annotations:
summary: "Page Life Expectancy under 100s on {{ $labels.instance }}"
- alert: SQLServerBlockingChain
expr: sqlserver_blocked_sessions > 5
for: 2m
labels: { severity: critical }
annotations:
summary: "{{ $value }} sessions blocked on {{ $labels.instance }}"
- alert: SQLServerTempdbLow
expr: sqlserver_tempdb_free_mb < 2048
for: 10m
labels: { severity: warning }
annotations:
summary: "tempdb free space under 2GB on {{ $labels.instance }}"What this actually changed
The honest answer is that most days nobody looks at this dashboard at all, which is fine - that's not the point of it. The point is the day something is slow, "is the database okay" stops being a question someone has to RDP into a box and run sp_whoisactive to answer, and starts being a dashboard link dropped in the incident channel. That alone has cut the "let's rule out the database" step of an incident from ten minutes to about ten seconds, and it's the same DMVs that were always there - they just needed a scraper and a place to be drawn.
If you're running the Prometheus/Grafana stack already from setting up host monitoring, this bolts onto it directly; the database just becomes one more scrape target instead of a separate tool with its own login and its own tab.
