Overall System Performance for SQL Server

The last several lessons have zoomed in: which connections exist, what they are waiting on, and what locks they hold. This lesson zooms back out. sp_monitor is a built-in stored procedure that gives a quick, high-level snapshot of the whole instance: CPU activity, disk I/O, network I/O, and connection counts, in one call, without the detail a DMV query provides. It will not replace anything covered so far in this module, but it is often the fastest first check when the question is simply "how is this instance doing overall" rather than "what is this one session doing."

That distinction, instance-wide versus session-specific, is worth holding onto through the rest of this lesson. Everything from the earlier lessons in this module, sp_who, Activity Monitor, sys.dm_tran_locks, all answer questions about particular sessions or particular locks. sp_monitor answers a question none of them are built to answer well on their own: taken together, is the instance as a whole under any real pressure right now.

sp_monitor is still a current, documented SQL Server system stored procedure. It takes no parameters:

sp_monitor

and returns a single row of cumulative and interval statistics about the instance since it was last started. No VIEW SERVER STATE permission is required the way it is for the DMVs covered earlier in this module; sp_monitor has its own, more permissive execution requirements, which is part of why it remains a convenient first stop.

The output groups into four areas: a measurement interval (when this run happened and how many seconds since the last one), activity time (CPU busy, I/O busy, and idle seconds), network packets (received, sent, errors), and I/O counts and login attempts (total reads, total writes, total errors, connections).

A sample run illustrates the point. Suppose sp_monitor reports a measurement interval of 300 seconds between runs, with cpu_busy at 1200(60)-20%, io_busy at 400(15)-5%, and idle at 8400(240)-80% for activity time; packets_received at 25000(500), packets_sent at 30000(700), and packet_errors at 0(0) for network packets; and total_read at 4000(80), total_write at 2000(40), total_errors at 0(0), and connections at 150(6) for I/O counts and login attempts. Read together, this is a healthy five-minute window: the instance spent the large majority of the interval idle rather than CPU- or I/O-bound, both error columns sit at zero, and the connection and packet counts are modest and growing steadily rather than spiking.

A contrasting run makes the healthy baseline easier to recognize. Suppose instead io_busy came back as 19500(285)-95% for the same 300-second interval, with total_errors at 12(3) rather than zero. The 95% I/O busy figure alone is past the rough 90% threshold worth paying attention to, and three new errors since the last run is not something to wave off regardless of how small that number looks next to a total_read count in the thousands. In this version of the scenario, the next move is not another sp_monitor call; it is dropping into the tools this module has already covered, Activity Monitor's Resource Waits pane to see whether I/O waits specifically are driving the busy percentage, and a look at the Windows or SQL Server error logs to identify what the three errors actually were.

Older explanations of sp_monitor tend to generalize the number format as always three parts: seconds since startup, seconds since the last run in parentheses, then a percentage. That is only true for the three activity time columns, cpu_busy, io_busy, and idle. Those are the only columns that report a percentage for the interval; in 1200(60)-20%, 1200 is seconds since SQL Server started, 60 is seconds since the previous sp_monitor run, and 20% is the share of that interval spent in that state.

Every other count field, the network packet columns and the I/O counts and login attempts columns, uses a simpler total(change) format with no trailing percentage: 150(6) means 150 total connections since startup, 6 of them since the last run. Describing every column as having two or three numbers including a percentage overstates what most of the output actually shows, and can lead to looking for a percentage that is not there on six of the ten reported columns.

The connections column is easy to misread given everything this module has already covered about sp_who and sys.dm_exec_sessions. It is not a live count of currently open sessions the way those tools are; it is a cumulative counter of logins and attempted logins since the instance started, successful or not. A sp_monitor connections value of 150 does not mean 150 sessions are open right now, only that 150 login attempts have occurred since startup. For a live, current count of actual open sessions, sp_who or sys.dm_exec_sessions, both covered earlier in this module, remain the right tools; sp_monitor answers a different question entirely.

This high-level information is useful for a quick gut check on instance health. Both error columns, packet_errors and total_errors, should normally sit at zero; a nonzero value in either is worth investigating regardless of how small the rest of the output looks. A cpu_busy percentage consistently above 90% is a sign the instance could use more or faster CPU capacity, and an io_busy percentage consistently above 90% points toward spreading I/O across more disk subsystems.

One caveat worth keeping in mind on modern multi-core hardware: CPU time can accumulate across CPUs, so these figures are not a normalized, single-core-equivalent CPU dashboard, and should be read as a rough signal rather than an exact utilization percentage. For a more precise, per-core view, Windows Performance Monitor and the SQL Server-specific counters covered in this module's first lesson go further than sp_monitor is designed to.

After installing SQL Server, sp_monitor must be run once before its output is meaningful; the first run's counters will not be correct, since there is no prior interval yet to measure against.

sp_monitor's single biggest limitation is baked into what its numbers actually measure: everything in the total(change) and total(change)-percentage columns accumulates from the moment SQL Server last started, not from some rolling recent window. On an instance that has been running for months without a restart, the totals reflect months of accumulated activity. A genuine problem that started an hour ago can be almost invisible in that context, since it is diluted across every hour the instance has been up. The change value in parentheses, the seconds-since-last-run figure, is the part actually worth watching closely on a long-running instance; the raw totals mostly tell you how long the server has been up and how busy it has been in aggregate, not what is happening right now.

sp_monitor also reports at the instance level only. It cannot tell you which database, which session, or which query is responsible for a high cpu_busy or io_busy reading, the way Activity Monitor's Processes pane or a targeted DMV query can. And it has no memory of its own between calls beyond the one prior run it compares against; there is no built-in history to review trends over a day or a week the way a dedicated monitoring product, or even a scheduled job that logs sp_monitor's output to a table, would provide.

None of this makes sp_monitor a poor tool; it makes it a specific one. It was never meant to replace session-level or lock-level investigation, and judged against that narrower job, a two-second, zero-configuration instance pulse, it still does exactly what it was designed to do.

For a T-SQL-queryable view that goes beyond what sp_monitor exposes, sys.dm_os_performance_counters is worth knowing about alongside it. It surfaces the same family of counters Windows Performance Monitor reads, buffer cache hit ratio, page life expectancy, batch requests per second, and hundreds of others, as a queryable result set rather than a live graphical tool:

SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
  AND counter_name = 'Page life expectancy';

Unlike sp_monitor's small, fixed set of columns, this DMV exposes a very large number of counters across every major SQL Server subsystem, which makes it considerably more powerful but also less of a quick-glance tool. For a two-second health check, sp_monitor remains the simpler starting point; for a specific, named counter, Page Life Expectancy being a classic example of a memory pressure indicator, this DMV is the more direct route than opening Performance Monitor's own interface.

By this point, the module has covered quite a few tools, and it is worth being clear about where each one actually earns its place. sp_monitor answers "how is the instance doing overall, right now, with zero setup." Activity Monitor answers "which sessions are active and is anything blocked," with a live, refreshing view. sys.dm_tran_locks and the other DMVs covered in this module answer precise questions about specific sessions, locks, or waits. sys.dm_os_performance_counters answers a narrower but deeper question than sp_monitor does, for a specific named counter rather than a handful of broad categories. Third-party monitoring products answer questions that need history: how did this metric trend over the last month, and should an alert have fired two hours ago.

None of these replaces the others, and a quick sp_monitor call is rarely the end of an investigation; it is usually the first signal that tells you whether to keep looking anywhere else at all. Treating it as a triage step rather than a complete answer is the difference between using it well and expecting more from ten columns of cumulative counters than they were ever designed to provide.

As covered in this module's first lesson, a range of third-party monitoring products exist that go well beyond what sp_monitor offers: dashboards, historical trending, alerting, and root-cause analysis among them. Broadly, these fall into a few categories: dedicated SQL Server monitoring suites built specifically around the engine's own DMVs and counters, general-purpose infrastructure monitoring platforms that treat SQL Server as one monitored component among many, and cloud-native monitoring built into the platform itself for instances running as a managed service rather than on self-managed hardware or virtual machines.

Choosing between them comes down to the same factors as any monitoring tool: the features that matter for your environment, ease of use, how well it scales as the number of monitored instances grows, and cost, licensing for these products is typically per-instance or per-core and can add up quickly across a large fleet. Most offer free trials worth testing against your own workload before committing, since a tool that handles a single instance well does not always scale cleanly to dozens.

sp_monitor is not a substitute for that kind of tooling; it is a zero-setup, built-in way to get a quick read on an instance without installing or configuring anything, and a reasonable first step before deciding whether a dedicated monitoring product is worth the investment for a given environment.

Fitting into the daily monitoring routine this module has been building, a quick EXEC sp_monitor; is a reasonable first move at the start of a check, before Activity Monitor, before sp_who, before anything lock-specific. A clean result, low CPU and I/O busy percentages, zero errors, a connection count that looks unremarkable, is often enough to confirm nothing urgent needs attention, which frees the rest of the routine for the more targeted tools this module has covered: Activity Monitor for live session detail, sys.dm_tran_locks when something is blocked, and Extended Events when a problem needs to be caught over time rather than at a single glance.

The order matters more than it might seem. Starting with the broadest, cheapest check and only reaching for narrower tools once that check gives a reason to keeps a daily routine fast on the normal days, which is most of them, while still catching the days that need real investigation. Running every tool in this module on every check, every day, is not a more thorough routine; it is mostly wasted effort on days when the instance is simply fine, and sp_monitor's whole value is in confirming that quickly.

The lack of built-in history noted above has a practical, built-in fix: a SQL Server Agent job, covered in this module's first lesson for alerting, can just as easily run on a schedule and insert sp_monitor's output into a logging table. A minimal version looks like this:

CREATE TABLE dbo.sp_monitor_log (
    captured_at   DATETIME2 DEFAULT SYSDATETIME(),
    cpu_busy      VARCHAR(50),
    io_busy       VARCHAR(50),
    idle          VARCHAR(50),
    total_errors  VARCHAR(50),
    connections   VARCHAR(50)
);

INSERT INTO dbo.sp_monitor_log (cpu_busy, io_busy, idle, total_errors, connections)
EXEC sp_monitor;

Scheduled every few minutes through SQL Server Agent, this turns sp_monitor's single-point-in-time snapshot into a genuine, queryable history: a cheap, zero-dependency alternative to a full third-party product when the need is simply "let me see how CPU and I/O busy have trended over the past week," without the limitation of everything being diluted into a since-startup total. It will not match a dedicated monitoring platform's dashboards or alerting logic, but it closes the specific gap this lesson has been pointing at, at the cost of a few lines of Transact-SQL and an Agent job rather than a licensing fee.

Pairing this with Database Mail, covered in this module's first lesson, extends it one step further: an Agent job step can check the newly logged row against a threshold and fire an alert only when, say, io_busy crosses 90% for the interval, which turns a passive log into something closer to the alerting a commercial monitoring product would otherwise be needed for.

Click the Quiz link below to test your knowledge of the concepts presented in this module so far.
SQL System - Quiz
The next lesson will cover how to use the database consistency checker to check system performance.