Watching Many Servers Without Buying Anything

You can monitor SQL Server with a small scheduled collector and a central database before buying a monitoring product. Keep the first version narrow enough that someone can explain every collected value and every failure.

Store the Collection Context

Use a dedicated monitoring database with an inventory of instance names and owners. Every sample needs a source instance, collection timestamp, and run identifier. Record failures separately so missing data cannot look like a healthy quiet server.

Schedule collection every five minutes as an initial cadence for these broad snapshots. Stagger instances and set connection and command timeouts. Short incidents can still occur between samples, so this is not a replacement for event-based alerts.

CREATE TABLE dbo.CollectionRun
(
    RunId bigint IDENTITY PRIMARY KEY,
    InstanceName sysname NOT NULL,
    StartedAtUtc datetime2 NOT NULL,
    FinishedAtUtc datetime2 NULL,
    Succeeded bit NULL,
    ErrorText nvarchar(2000) NULL
);

Create that table once in the monitoring database, alongside typed tables for the four result sets. Use an approved PowerShell collector through an installed SQL client library, or an existing job framework. SQL Agent or Windows Task Scheduler can provide the schedule.

Collect Instance and Database State

The first query confirms contact with the engine and records startup and database state. Save one row per database per collection. A restart marker is essential when interpreting cumulative counters from later queries.

SELECT @@SERVERNAME AS instance_name,
       SYSUTCDATETIME() AS collected_at_utc,
       i.sqlserver_start_time,
       d.database_id, d.name, d.state_desc,
       d.recovery_model_desc, d.log_reuse_wait_desc
FROM sys.databases AS d
CROSS JOIN sys.dm_os_sys_info AS i;

A successful query does not prove that every application path is healthy. Pair monitoring with a suitable service-level check where needed. If collection fails, retain the exact error and the instance that was attempted.

Collect Raw Wait Statistics

The second query captures raw cumulative wait counters. Calculate differences between consecutive valid samples on the same startup timeline. Keep reset detection in the calculation instead of assuming every total must increase.

SELECT SYSUTCDATETIME() AS collected_at_utc,
       wait_type, waiting_tasks_count,
       wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0;

Filter expected background waits in the reporting layer rather than discarding all raw context immediately. Different workloads need different interpretations. A top-wait chart should lead to an investigation, not automatically prescribe a setting change.

Collect File I/O Counters

The third query captures counts, bytes, and stall time for database files. Derive interval averages from differences, with a positive operation-count denominator. Use file identity and startup context when comparing samples.

SELECT SYSUTCDATETIME() AS collected_at_utc,
       v.database_id, DB_NAME(v.database_id) AS database_name,
       v.file_id, f.type_desc,
       v.num_of_reads, v.io_stall_read_ms,
       v.num_of_writes, v.io_stall_write_ms,
       v.num_of_bytes_read, v.num_of_bytes_written
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS v
JOIN sys.master_files AS f
  ON f.database_id = v.database_id AND f.file_id = v.file_id;

Keep reads and writes separate in the report. A database-wide average can hide a busy log file or one slow data file. Add storage-volume capacity as a later collection if that risk is not already covered elsewhere.

Collect Active Blocking

The fourth query records blocked requests and their immediate blockers. This helps preserve evidence that otherwise disappears when the incident ends. It is still a sample and will miss activity between collection times.

SELECT SYSUTCDATETIME() AS collected_at_utc,
       r.session_id, r.blocking_session_id,
       r.wait_type, r.wait_time, r.total_elapsed_time,
       r.database_id, s.login_name, s.program_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE r.blocking_session_id <> 0
  AND r.session_id <> @@SPID;

The blocker may be a sleeping session with an open transaction, so this query is not a complete chain analysis. Use it to identify the time and sessions for follow-up. Avoid collecting unrestricted SQL text by default when sensitive values could appear.

Operate the Collector Like a Small Service

Grant only the documented permissions needed by the selected views on each installed version. Use approved credential storage and encrypted connections. Do not hide a server-administrator password inside a scheduled script.

Keep per-query result storage tied to CollectionRun and mark completion only after the intended results are saved. Distinguish an empty valid result from a failed query. Test one unreachable instance to prove that other collections still finish.

Set retention, index the time and instance columns used by reports, and monitor the collector itself. This approach has maintenance costs even without a license invoice. Expand it only when a new measurement answers a real operational question.

A small monitoring system is not free of responsibility, it is responsibility you can keep understandable.

This post was rewritten from scratch in September 2026. The original, published on 2019-03-14, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.