Monitoring Is Not Diagnosis: Why SQL Server Alerts Are Only the Beginning

Not long ago, I was dealing with another case where everything looked fairly obvious at first. Situations like this are a good reminder of why SQL Server monitoring cannot replace diagnosis.

A SQL Server was noticeably slow, the monitoring system had raised the expected alerts, and the usual suspects quickly came up. CPU, storage, network, perhaps a query, or perhaps an application suddenly doing more work than before.

There is nothing unusual about that. Anyone who has worked with SQL Server for a while will know these situations. A dashboard turns red, someone receives a monitoring email, a process takes longer than expected, and before long, the first theory about the cause appears.

And that is exactly where things become interesting for me.

Not because monitoring is bad. Quite the opposite. Without monitoring, we often miss the first indication that something is happening at all. But a monitoring alert is not yet a diagnosis. At first, it is only a signal.

That may sound obvious, but in practice, this distinction is often overlooked.

“CPU is high” quickly becomes “the server needs more CPU.”
When Network Waits appear, the network suddenly becomes the main suspect.
Storage waits often lead directly to a storage ticket.
And when a query runs for a long time, the idea of a missing index is rarely far away.

Sometimes that conclusion is correct. It would certainly make things easier.

Quite often, however, it is only the first hypothesis. When that hypothesis is treated as the cause too early, we may end up changing the wrong thing.

Monitoring is important, but it does not explain the cause

I want to make one thing clear from the beginning because it matters to me: this article is not an argument against monitoring.

I consider good monitoring essential. Especially for SQL Servers used in production, I want to know when a problem started, how long it lasted, whether it happens regularly, whether it correlates with certain jobs, reports, imports, releases or workload peaks, and whether the system’s behaviour has changed over time.

Without this kind of data, troubleshooting quickly becomes a mixture of memory, intuition and frantic attempts to reproduce the issue.

Monitoring provides visibility. It helps narrow things down, reveals patterns and makes communication easier because we no longer have to say only, “it felt slow.” At least we have a time window and a few measurements to work with.

But monitoring usually does not answer why something happened.

An alert initially tells us:

Something here looks unusual.

It does not automatically tell us:

This is the cause, and this action will solve the problem.

From my perspective, this distinction between SQL Server monitoring and diagnosis is frequently underestimated in day-to-day operations.

For me, diagnosis therefore does not begin with the alert itself. It begins with the question of which assumption that alert triggers.

SQL Server monitoring and diagnosis: from signal to hypothesis

When I look at a performance issue, I first try to separate a few things in my mind. This may sound a little theoretical, but it helps enormously when trying not to head in one direction too early.

For me, this kind of analysis has several stages:

• Signal
• Symptom
• Hypothesis
• Validation
• Cause
• Decision

A signal is what draws attention. This might be high CPU, a particular wait type, a long runtime, a red dashboard or a Query Store regression.

The symptom describes how the problem presents itself. A report that used to take two minutes suddenly takes twenty. An application responds slowly during certain actions. An import no longer completes within its expected time window. Users report timeouts.

The hypothesis is my first testable assumption. A particular query may be consuming an unusual amount of CPU. A report may be returning too much data to the client. An execution plan may have changed.

Validation is the stage where I deliberately try to confirm or disprove that hypothesis. This is where a plausible assumption either becomes a technically defensible assessment or falls apart.

The cause is what I can demonstrate technically after that validation. The decision comes afterwards: what do we change, what do we continue to observe, and what do we deliberately leave unchanged?

This may sound like semantics, but it matters in practice. If I declare a hypothesis to be the cause too early, I often start looking only for evidence that supports it. Anything that does not fit is then easily ignored or somehow made to fit.

That is particularly dangerous with SQL Server performance because very different causes can look remarkably similar.

High CPU does not automatically mean a CPU problem

High CPU is a good example because the initial reaction is often immediate.

The monitoring system shows high CPU on the SQL Server. Perhaps it is constant, perhaps it appears only in peaks, perhaps it started after a particular deployment, or perhaps it always happens at a certain time.

Of course, the server may simply be undersized. That happens. It is not unrealistic, especially with consolidated systems, older virtual machines or workloads that have grown steadily over the years.

Even so, that would not be my first conclusion.

First, I would want to know which work is actually consuming that CPU.

This is a typical part of SQL Server monitoring and diagnosis for me: not just seeing the measurement, but understanding the work behind it.

Active requests as a first step

As an initial check, we could look at the active requests. Not because this query will identify the cause, but because it can help us ask better questions next.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.row_count,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name,
    t.text AS sql_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

The CPU values are not the only interesting part here. I also want to see whether individual sessions stand out, which application they come from, whether large numbers of logical reads are involved, whether many rows are being processed, and whether the load is spread broadly or concentrated in a few queries.

Query Store as a second perspective

If Query Store is enabled, I would usually try to narrow down the affected period and identify which queries stood out during that time. Query Store stores runtime statistics in intervals, so the time window needs to be part of the query. When averages from several intervals are combined, they should also be weighted by the number of executions.

A simple starting point might look like this:

DECLARE @StartTime datetime2 = '2026-06-29T10:15:00';
DECLARE @EndTime   datetime2 = '2026-06-29T10:45:00';

SELECT TOP (20)
    qsq.query_id,
    qsp.plan_id,
    OBJECT_NAME(qsq.object_id) AS object_name,
    SUM(rs.count_executions) AS executions,
    SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) AS weighted_avg_duration,
    SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) AS weighted_avg_cpu_time,
    SUM(rs.avg_logical_io_reads * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) AS weighted_avg_logical_reads
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
    ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON qsp.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time < @EndTime
  AND rsi.end_time > @StartTime
GROUP BY
    qsq.query_id,
    qsp.plan_id,
    qsq.object_id
ORDER BY weighted_avg_cpu_time DESC;

The dates in this example are placeholders. In a real investigation, I would replace them with the period reported by the users or shown in the monitoring system.

Even with the correct time window, this is not a finished diagnosis.

The query provides candidates. We still need to investigate further. Does the time period actually match the reported problem? Has the execution count changed? Is there another plan? Have logical reads increased? Has the volume of data changed? Is the query reasonably expensive for its business purpose, or is SQL Server doing far more work than necessary?

That final question is often the decisive one. High CPU may mean that the hardware is insufficient. But it may also mean that SQL Server is doing a great deal of unnecessary work.

Adding more CPU may help temporarily, but it does not necessarily solve the real problem.

Wait Stats need time and context

Wait Stats are one of the tools I use frequently, but they are also a good example of how quickly we can head in the wrong direction.

A simple query might look like this:

SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
  AND wait_type NOT LIKE 'BROKER%'
  AND wait_type NOT LIKE 'XE%'
ORDER BY wait_time_ms DESC;

This provides an initial overview. Nothing more.

The filter list is deliberately short and would usually be insufficient for a real analysis. Instance-wide Wait Stats are also cumulative. They cover the period since the last reset or the last restart of the SQL Server instance. When I am investigating a specific problem, a static top list is only of limited value.

A delta over a specific time period, or while a reproducible problem is occurring, is usually more useful.

So the question is not only:

What is at the top?

It is more useful to ask:

What changed while the problem was happening?

And then the real questions begin. Which workload was running at that time? Is the wait consistently high, or only during a particular process? Do we have a baseline? Which queries were involved? Does any of this match what the users or the application are reporting?

A wait type is a clue. It is not a verdict.

Network Waits do not always mean a network problem

Network Waits are a particularly good example because infrastructure is often blamed very quickly.

When SQL Server is waiting for data to be transferred to or consumed by the client, it naturally looks like a network problem at first. And yes, sometimes the network really is the problem.

But not always.

I have seen more than one situation where the network or infrastructure was initially suspected, but the real question turned out to be why so much data was being transferred to the client in the first place.

A report retrieving several million rows. An application using SELECT *. A screen that filters data in the client even though the filtering would be better handled in the database. A tool that processes result sets slowly. All of these can cause SQL Server to wait.

Active requests in the context of Network Waits

An initial check might look like this:

SELECT TOP (20)
    r.session_id,
    r.status,
    r.wait_type,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.row_count,
    s.host_name,
    s.program_name,
    t.text AS sql_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
  AND r.database_id > 4
ORDER BY
    r.total_elapsed_time DESC,
    r.logical_reads DESC,
    r.row_count DESC;

At this point, I would not focus on a single value such as row_count. For active requests, that value depends on the current execution state and does not automatically represent the final result set. What matters more is the combination of duration, logical reads, wait type, application context and the amount of data the query is processing or returning to the client.

So I am not looking for a fixed threshold here. I am interested in the path of the analysis.

If I see a session with Network Waits, I would not immediately open a network ticket. I would first check which query is running, which client is involved, how long the request has already been running, how many logical reads are being generated, which row count is currently visible, and whether the application may be consuming the result set slowly or causing unnecessary work.

The network may still be involved. But my first question would not be, “why is the network slow?” It would be, “what is happening here between the query, the client, the volume of data and the way that data is processed?”

That is a different way of looking at the same signal.

Query Store connects signals with queries

Query Store is useful for SQL Server monitoring and diagnosis because it can connect a general monitoring signal with specific queries, plans and time periods.

The fundamentals and prerequisites of Query Store are well documented in the Microsoft documentation for Query Store.

The monitoring system may only tell us:

The SQL Server was slow between 10:15 and 10:45.

Query Store can then help determine which queries stood out during that period, whether plans changed, whether individual queries suddenly ran significantly longer, or whether particular wait categories became visible for particular plans.

But Query Store is not a diagnosis button. It provides data that still needs to be interpreted.

Checking wait categories in Query Store

If Query Store Wait Stats are available, we can examine which wait categories stand out for particular plans during the relevant Query Store intervals:

DECLARE @StartTime datetime2 = '2026-06-29T10:15:00';
DECLARE @EndTime   datetime2 = '2026-06-29T10:45:00';

SELECT TOP (20)
    qsq.query_id,
    qsp.plan_id,
    ws.wait_category_desc,
    SUM(ws.total_query_wait_time_ms) AS total_wait_time_ms,
    COUNT(*) AS wait_stat_rows
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
    ON qsq.query_id = qsp.query_id
JOIN sys.query_store_wait_stats AS ws
    ON qsp.plan_id = ws.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON ws.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time < @EndTime
  AND rsi.end_time > @StartTime
GROUP BY
    qsq.query_id,
    qsp.plan_id,
    ws.wait_category_desc
ORDER BY total_wait_time_ms DESC;

I would still not stop at this query.

The interpretation remains the important part. Does the time period match the reported problem? Is the query really connected to the symptom? Perhaps the plan changed. Perhaps the workload is expected from a business perspective. The visible waits may also be a consequence rather than the cause. I would additionally check whether there is a connection with a release, job, import or data growth.

Query Store is powerful when we ask the right questions. As just another dashboard, it is considerably less useful.

How I approach SQL Server monitoring and diagnosis

I am not a fan of excessively long checklists because real environments rarely allow us to follow them in a perfectly linear way. Even so, a rough structure helps me avoid jumping straight into corrective actions.

Narrow down the time period, symptom and workload

First, I would narrow down the time period. When did the problem occur? When did it begin? Was it a one-off event or does it recur? Did it happen after a deployment, during a job, during a report, or perhaps always at a particular time?

Next, I would try to describe the symptom clearly. “SQL Server is slow” is not enough. I want to know which application is affected, which database is involved, which users or screens experience the problem, and whether a specific query can be identified. The comparison is just as important: how long did the process take before, and how long does it take now?

The next step is to understand the workload. Not every load is a problem. A month-end process is allowed to look different from a normal Tuesday morning. An import may consume resources if it has a defined processing window. The question is not only whether load exists, but whether that load is plausible at that time and in that form.

Formulate and test a hypothesis

Only then would I formulate a specific hypothesis. A particular query may have generated significantly more logical reads since the last deployment. A report may be returning a very large result set to the client. A plan change may have increased the runtime of a query.

I then look for data that matches this hypothesis. I do not export everything that happens to be available. Instead, I check the relevant sources deliberately: active requests, Query Store, execution plans, Wait Stats, blocking, job history, the SQL Server error log, Windows counters, storage latency, application logs and deployment times.

Just as importantly, I deliberately allow for alternative hypotheses.

What would I need to see for my first assumption to be wrong?

This is one of the uncomfortable parts of troubleshooting because we naturally want to be right quickly. But it often saves time not to defend the first idea too early.

Some of these thoughts also connect with my other SQL Server articles on SQL-aus-Hamburg, where I repeatedly return to the idea that we should not jump too quickly from a symptom to a cause.

Common shortcuts that lead in the wrong direction

One of the most common shortcuts is to treat an alert as if it already represented a diagnosis. It does not. An alert is a starting point. It shows that something is unusual, but it does not explain why it is unusual or which action would make sense.

The same applies to Wait Stats. The highest wait is not automatically the cause of a problem. Waits need a time period, a workload and context. Without this interpretation, we may see what SQL Server waited for, but we still do not necessarily know why that wait occurred or whether it is connected to the reported problem.

High CPU also leads quickly to the assumption that more hardware is required. Sometimes that is correct, especially when a system has grown over the years and the available resources no longer match the workload. But first, I want to know which work is generating that CPU. If a query suddenly performs significantly more logical reads or a plan change causes far more work, additional CPU may reduce the symptom without resolving the cause.

The reaction to storage waits is similar. Storage can be the problem, but not every storage wait means that opening a storage ticket is the right next step. A query may simply be reading too much data, a suitable access path may be missing, or the workload may not match what was expected.

Network Waits are also frequently interpreted as a network problem too quickly. Sometimes the network is involved. But the query may also be returning too many rows, the client may be processing them slowly, or the application may be filtering data only after it reaches the client.

Tools and health checks still need interpretation

Tools do not remove the need for assessment either. Query Store provides useful data about queries, plans, runtimes, wait categories and time periods, but someone still has to decide whether the period matches, whether the query is part of the problem, whether a plan change matters and whether the measured load is expected.

The distinction between a health check and troubleshooting matters for the same reason. A health check does not automatically answer an acute question such as, “why was the application slow yesterday between 10:15 and 10:45?” A health check assesses general condition, risks and typical weaknesses. Troubleshooting investigates a specific problem within a specific time period.

One more note about permissions

The queries shown here are deliberately intended as starting points, not as a complete analysis package. Depending on the SQL Server version and environment, suitable permissions are required, such as VIEW SERVER STATE, VIEW DATABASE STATE or the corresponding newer, more granular permissions.

This should be managed properly in production environments.

And as always, not every script that appears harmless in a test environment should be run in every production environment without review.

Monitoring is the start of the analysis

For me, the most important point is that monitoring is necessary, but it is not the end of the analysis.

Without monitoring, we lack visibility. With monitoring, we can at least see that something is happening. But visibility does not automatically create understanding.

The first suspicion remains a hypothesis, even when it sounds highly plausible. Especially with SQL Server performance, the first explanation may be wrong because CPU, waits, storage, network, query plans and application behaviour are closely connected.

A single value is rarely enough. Context is usually more important than an isolated measurement. Time period, workload, change, query context and business expectations determine whether a signal is relevant.

SQL Server monitoring and diagnosis therefore needs more than a red spike on a dashboard. It needs a connection between the signal, the time period, the workload and a testable technical hypothesis.

The system where a problem becomes visible is also not necessarily the system causing it. Storage, network or CPU may be affected without being the first place where we should make changes.

This is why SQL Server monitoring and diagnosis should be treated as two connected, but clearly separate, parts of troubleshooting.

An alert, a wait type, a CPU graph or a red dashboard tells us that something is unusual. The real work begins afterwards: narrow down the time period, understand the symptom, formulate a hypothesis, check the relevant data and be prepared to discard the first assumption.

Not changing something as quickly as possible, but first understanding exactly what we are reacting to.

That may feel slower than we would like in an urgent situation. In the end, however, it is usually the more reliable approach.