Skip to main content

Monitoring Microsoft SQL Server

SQL Server is monitored by the Windows agent, installed as a service the way the Windows install describes. On a default instance nothing else is needed: the agent connects over shared memory with Windows integrated authentication, so there is no login to create and no password to store.

How SQL Server is recognised​

Two signed definitions match by the Windows service name:

  • MSSQLSERVER, the default instance, reported as the service mssql;
  • MSSQL$SQLEXPRESS, the Express named instance, reported as mssql-express and reached through the instance path localhost/SQLEXPRESS.

Every minute each runs one query over the dynamic management views: sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests, sys.databases and sys.dm_os_performance_counters, within a 10-second budget. The connection uses the shared-memory protocol and the account the agent service runs under, so the query goes through the driver on the same machine and no credential is involved. The default instance additionally asks SERVERPROPERTY('ErrorLogFileName') for the path of the ERRORLOG. A companion definition for the log runs every 30 seconds.

What is collected​

Sessions. Connections, user sessions, and requests currently blocked by another session, the signal behind the built-in rule.

Databases. How many databases exist and how many are not online.

Throughput and cache. The batch requests counter and the page life expectancy of the buffer pool, in seconds.

Facts shown on the host page: the ERRORLOG path, for the default instance.

SignalMetric
Connections and user sessionssvc.mssql.connections, svc.mssql.user_sessions
Requests blocked on lockssvc.mssql.blocked_requests
Databases total and not onlinesvc.mssql.databases, svc.mssql.databases_offline
Batch requestssvc.mssql.batch_requests
Page life expectancysvc.mssql.page_life_expectancy

The Express instance emits the same set under svc.mssql-express.*. Names and units are in the metrics reference.

Logs. The ERRORLOG of the default instance ships by default at notice level and above, read from the path the server reports. The file is UTF-16 and is decoded as such; the level comes from the Error: and Warning: markers in each line. See Logs.

Alerts out of the box​

RuleFires whenSeverityShips
SQL Server requests blocked on locksone or more requests blocked by another session, held for 10 minuteswarningauto

The rule arms itself the moment the metric starts flowing for a workspace, and its metric pattern svc.mssql%.blocked_requests covers the default and the Express instance alike. Blocking for a few seconds is how a busy server works; a request still blocked after ten minutes of continuous samples is a lock somebody is not releasing. Thresholds and hysteresis are in the rules catalog.

When a path is not where SQL Server says it is​

The ERRORLOG path comes from the server itself. To point the agent elsewhere, from PowerShell as Administrator:

wakora --set mssql.error-log="C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG"

Keys are listed in Agent configuration.

Limitations​

  • Only the default instance and a named instance called SQLEXPRESS are matched. Another named instance has no stock definition.
  • The Express definition collects no ERRORLOG path, so its log is not shipped; only the default instance's ERRORLOG is.
  • Shared memory means the agent must run on the same machine as the instance; a remote SQL Server is not covered.
  • Integrated authentication uses the account the agent service runs under; if that account cannot log in to the instance, the check fails with the server's error.
  • Database sizes, backup history, Always On state, wait statistics and per-query figures are not collected.
  • Windows only; there is no definition for SQL Server on Linux.