Monitor SQL Server 2025 with Built-in Functions

SQL Server has a set of scalar functions^[1], each prefixed with @@, that return a single cumulative value reflecting instance activity since the service last started. Older documentation sometimes called these "global variables," and that label still turns up informally; current Transact-SQL reference material groups most of them under Configuration Functions instead. Neither name changes what they do.

These functions genuinely still work in SQL Server 2025; none of them are deprecated, and nothing in this lesson is a retirement notice the way two earlier lessons in this module were. What has changed is their role. Where they were once a primary way to check instance activity, they are now better suited to a quick, no-setup spot check typed directly into a query window than to serious ongoing monitoring, which is better served by the DMVs, Query Store, and Extended Events sessions this module has already covered.

@@CONNECTIONS	Number of attempted connections since SQL Server was last started.
@@CPU_BUSY	Milliseconds SQL Server has spent busy on the CPU since startup, based on clock ticks.
@@IDLE	Milliseconds SQL Server has been idle since startup.
@@IO_BUSY	Milliseconds SQL Server has spent doing I/O since startup.
@@MAX_CONNECTIONS	The maximum number of simultaneous user connections the instance is built to support, a license/build-based ceiling rather than a live or configured count. In practice this returns a very large fixed number and says little about actual connection headroom on any particular day.
@@PACKET_ERRORS	Total number of network packet errors since startup.
@@PACK_RECEIVED	Total number of network packets received since startup.
@@PACK_SENT	Total number of network packets sent since startup.
@@SERVERNAME	Name of the local server, as used when logging in. Returns NULL if the server name was never explicitly set.
@@SERVICENAME	Name of the registry key SQL Server is running under, typically MSSQLSERVER, or the instance name for a named instance.
@@TIMETICKS	Microseconds per clock tick on this system; a hardware-dependent constant, not an activity count.
@@TOTAL_ERRORS	Total number of disk read/write errors since startup.
@@TOTAL_READ	Number of disk reads performed since startup.
@@TOTAL_WRITE	Number of disk writes performed since startup.
@@VERSION	SQL Server version, edition, and build information, as a single formatted string.

These are plain scalar expressions and can be used anywhere an expression is allowed, most simply in a bare SELECT:

SELECT @@TOTAL_WRITE;
SELECT @@VERSION;

They can also be assigned to a variable or used inside a conditional, exactly like any other scalar expression:

DECLARE @StartupErrors bigint = @@TOTAL_ERRORS;
IF @@IDLE > @@CPU_BUSY
    PRINT 'Instance has been more idle than busy since startup.';

Their values are cumulative since the instance last started and are not reset by anything a user does; restarting the service is the only way to zero them out. This matters for interpretation: a large number from @@TOTAL_ERRORS on a server that has been running for eight months is not necessarily cause for alarm by itself, since it has had eight months to accumulate, while the same number three hours after a restart would be.

sp_monitor, covered earlier in this module, is not a separate mechanism built from scratch; it is, under the hood, a stored procedure that queries several of these exact scalar functions, @@CPU_BUSY, @@IO_BUSY, @@IDLE, @@PACK_RECEIVED, @@PACK_SENT, @@PACKET_ERRORS, @@TOTAL_READ, @@TOTAL_WRITE, @@CONNECTIONS, and @@TOTAL_ERRORS among them, captures two snapshots a configurable interval apart, and reports both the running totals and the change between snapshots. Running SELECT @@CPU_BUSY; directly and running sp_monitor are not two unrelated ways of checking CPU activity; the second is substantially the first, formatted and differenced for convenience.

This also explains a limitation this module already flagged for sp_monitor's own connections column: it inherits @@CONNECTIONS' cumulative-since-startup counting directly, which is why it reports total login attempts rather than currently open sessions. Querying @@CONNECTIONS by hand does not avoid that limitation, since the limitation lives in the function itself, not in sp_monitor's formatting of it. A live count of currently open connections requires a DMV instead, specifically sys.dm_exec_connections, which returns one row per connection actually open right now rather than a running total of every connection attempted since the service started.

Seeing how sp_monitor derives its percentage columns is a useful exercise in its own right, since it demystifies a stored procedure's output by reducing it to the same scalar functions already covered in this lesson. Capturing two manual snapshots a known interval apart and subtracting them reproduces, by hand, the same change figures sp_monitor reports automatically:

DECLARE @CpuBefore bigint = @@CPU_BUSY, @IdleBefore bigint = @@IDLE;
WAITFOR DELAY '00:00:10';
SELECT
    @@CPU_BUSY - @CpuBefore AS cpu_busy_change,
    @@IDLE - @IdleBefore AS idle_change;

sp_monitor automates exactly this pattern, snapshot, wait, snapshot, subtract, across every column it reports, and adds the running totals alongside the deltas. Running the manual version once is a reasonable way to confirm, firsthand, that there is no hidden mechanism inside sp_monitor beyond these same functions being called twice and compared.

@@CONNECTIONS versus sys.dm_exec_connections is the clearest example of a broader pattern running through this entire list: several of these functions answer "how much has accumulated since startup," while the question actually being asked is usually "what is the current state." @@MAX_CONNECTIONS is a milder version of the same mismatch, returning a license-based ceiling that essentially never changes and rarely helps diagnose an actual connection-capacity problem; sys.dm_exec_connections' row count, compared against the server's actual, currently configured user connections setting, is the more useful pairing for that question.

@@VERSION and @@SERVERNAME have a different kind of modern alternative worth knowing about: the SERVERPROPERTY() function. @@VERSION returns version, edition, and build information bundled into one formatted string meant for human reading, which makes it awkward to parse programmatically if, say, a script needs just the build number to decide whether a patch is installed. SERVERPROPERTY('ProductVersion') returns that build number alone, cleanly, as does SERVERPROPERTY('Edition') for the edition name. Similarly, @@SERVERNAME reflects the name used at login and can go stale if the underlying machine name changes without sp_dropserver/sp_addserver being run to update it, while SERVERPROPERTY('ComputerNamePhysicalNetBIOS') reports the actual current operating-system machine name regardless of what SQL Server's login-time name says. Neither function replaces the other outright; @@VERSION remains the fastest way to eyeball version information in a query window, and SERVERPROPERTY() is the better choice the moment that information needs to be parsed, compared, or acted on by code.

The network-related functions, @@PACKET_ERRORS, @@PACK_RECEIVED, and @@PACK_SENT, follow the same cumulative-counter pattern as @@CONNECTIONS, and the same caution applies: a nonzero @@PACKET_ERRORS on a server that has been up for months says very little on its own, since even a healthy network connection accumulates the occasional error over a long enough window. sys.dm_os_performance_counters, covered in this module's first lesson, exposes several network-related counters that can be filtered to a specific time window rather than read as one running total since startup, which makes it the better choice for answering "are packet errors increasing right now" rather than "how many have there been, total, for an unknown period."

These functions' cumulative-since-startup behavior, a limitation for live checks, is actually convenient for a different purpose: a lightweight, homegrown trend log, built on SQL Server Agent, covered earlier in this module, without needing to configure Query Store or Extended Events for something this simple. A small logging table and a scheduled job can capture a handful of these values on a fixed interval:

CREATE TABLE dbo.InstanceActivityLog (
    log_time datetime2 NOT NULL DEFAULT SYSDATETIME(),
    cpu_busy bigint,
    io_busy bigint,
    idle bigint,
    connections bigint,
    total_errors bigint
);

A SQL Server Agent job step scheduled every few minutes, running a single INSERT that reads the current values of @@CPU_BUSY, @@IO_BUSY, @@IDLE, @@CONNECTIONS, and @@TOTAL_ERRORS into that table, turns a set of cumulative counters nobody is watching into an actual trend line that can be charted or queried later:

INSERT INTO dbo.InstanceActivityLog (cpu_busy, io_busy, idle, connections, total_errors)
SELECT @@CPU_BUSY, @@IO_BUSY, @@IDLE, @@CONNECTIONS, @@TOTAL_ERRORS;

This is not a replacement for Query Store, which tracks query-level detail these instance-wide counters cannot see, nor for Extended Events, which captures discrete occurrences rather than running totals. It is a narrow, specific use case: a cheap, dependency-free way to notice that @@TOTAL_ERRORS jumped overnight, or that @@CONNECTIONS is growing faster week over week than it used to, without building anything more elaborate than a table and a job step already familiar from earlier in this module.

How many attempted connections since SQL Server last started?	@@CONNECTIONS
How long has SQL Server been using the CPU since startup?	@@CPU_BUSY
How long has SQL Server been idle since startup?	@@IDLE
How long has SQL Server spent on I/O since startup?	@@IO_BUSY
What is the maximum number of simultaneous connections the instance supports?	@@MAX_CONNECTIONS
How many network packet errors have occurred?	@@PACKET_ERRORS
How many network packets have been received?	@@PACK_RECEIVED
How many network packets have been sent?	@@PACK_SENT
What is the local server's login name?	@@SERVERNAME
What registry key was used to start the service?	@@SERVICENAME
How many microseconds per clock tick on this system?	@@TIMETICKS
How many disk I/O errors have occurred since startup?	@@TOTAL_ERRORS
How many disk reads since startup?	@@TOTAL_READ
How many disk writes since startup?	@@TOTAL_WRITE
What version, edition, and build is this instance?	@@VERSION

@@SERVICENAME and @@TIMETICKS deserve a brief, honest note, since neither comes up often in practice. @@SERVICENAME is genuinely useful exactly once per server, when first confirming whether a particular instance is running under the default MSSQLSERVER service or under a named instance's own service, information usually already known from how the instance was set up in the first place rather than something checked repeatedly. @@TIMETICKS answers a question almost nobody asks directly, how many microseconds make up one clock tick on this particular system, and exists mainly as a unit-conversion helper for interpreting @@CPU_BUSY, @@IO_BUSY, and @@IDLE in a true wall-clock unit rather than raw ticks, on the rare system where the tick length is not simply one millisecond. Listing both here is less an endorsement to use them often and more a completeness note: a reference page that quietly dropped the two least-used functions in the list would be incomplete in exactly the way the original quick-reference table already was.

Placed alongside everything else this module has covered, these scalar functions occupy a specific, narrow niche: the fastest possible way to get one number, right now, with zero setup, at the cost of that number almost always being a running total since startup rather than a current state. sp_monitor, built directly from several of them, adds differencing and formatting but inherits the same underlying counters and the same cumulative-counting caveats. Activity Monitor and the DMVs covered in Lessons 4 through 9 answer "what is happening right now" with far more structure and detail, at the cost of needing an actual query, a report window, or a session definition rather than a single bare SELECT. Query Store and Extended Events add the dimension neither sp_monitor nor these functions can offer at all, history and event capture, at the cost of needing to be enabled or configured in advance rather than being available unconditionally the moment a connection opens.

None of this makes one tier of tool simply better than another; it makes them suited to different moments. These functions suit the first thirty seconds of looking at an unfamiliar server, before anything else is known about it. Everything else this module has covered suits the investigation that follows once a real question has taken shape.

These functions earn a place in day-to-day work mostly as a first, immediate check rather than as a recorded or recurring one. Typing SELECT @@VERSION; the moment a new connection opens, to confirm which instance and build is actually being talked to, or SELECT @@SERVERNAME; when a script's output needs to be labeled with the server it ran against, costs nothing and needs no setup. The moment a value needs to be tracked over time, compared across restarts, or correlated with something else happening on the instance, that is the signal to move to a DMV, Query Store, or an Extended Events session instead, exactly as this module has argued for sp_monitor, DBCC, and the older locking commands already covered.

A practical rule of thumb: if the question starts with "what is," one of these functions or SERVERPROPERTY() is probably the fastest answer. If the question starts with "how has this changed" or "what is happening right now across every session," a DMV is almost always the better tool, and reaching for @@CONNECTIONS to answer either of those two questions will produce a number that looks like an answer without actually being one.

Put together, this module has now covered four distinct layers of SQL Server monitoring, each with its own moment to be reached for: the near-instant scalar check covered in this lesson; the formatted snapshot-and-difference reports of sp_monitor and Activity Monitor; the structured, on-demand detail of DMVs and DMFs; and the persistent, structured history of Query Store and Extended Events. A server rarely needs all four running at once, but knowing which layer a given question actually belongs to, rather than reaching for whichever tool happens to be most familiar, is the skill this module has been building toward one lesson at a time.

[1]Scalar functions: functions that return a single value, as opposed to a table-valued function, which returns a result set.