Performance Monitoring Tools and Techniques in SQL Server

In this lesson, you will learn the tools and techniques DBAs use to monitor SQL Server performance in the real world.
Monitoring is not “looking at CPU.” Monitoring is building a system that can answer questions fast:
What is slow? Why is it slow? When did it start? What changed?
Is it getting worse?

Big Idea: The goal of monitoring is fast diagnosis with evidence.
When production is slow, the DBA who wins is the DBA who can point to specific metrics, specific queries, and specific bottlenecks—
not guesses.

Class Overview (What students will learn today)

The difference between triage monitoring and long-term monitoring.
What to measure: latency, throughput, concurrency, waits, CPU, memory, I/O, TempDB, blocking.
Core SQL Server monitoring tools: DMVs, Query Store, Extended Events, Performance Monitor counters.
How to find “top queries” by CPU, reads, and duration.
How to interpret wait stats correctly (clues, not verdicts).
How to avoid monitoring noise and alert fatigue.
A simple monitoring blueprint you can implement in any environment.

Recommended Class Duration

120–180 minutes (concepts + tools + labs)

Prerequisites

Basic SQL Server tuning knowledge (CPU/memory/I/O basics)
Basic understanding of execution plans (at a high level)
Optional: familiarity with Windows Performance Monitor

Learning Objectives (By the end of this class, students can…)

Explain why “CPU is high” is not a root cause by itself.
Use DMVs to identify top queries and active sessions during slowness.
Use Query Store to compare performance over time and detect regressions.
Use wait stats in context to identify likely bottlenecks.
Describe when to use Extended Events vs Profiler (and why Profiler is not recommended for modern monitoring).
Design a lightweight monitoring plan: what to collect, how often, and how to alert.

Part 1 — Monitoring vs Troubleshooting (Triage vs Long-Term) (20–35 min)

There are two monitoring modes:

1) Triage Monitoring (live incident mode)

Goal: identify the bottleneck quickly while the problem is happening.
Tools: DMVs, live waits, active requests, blocking chains, TempDB activity.
Time scale: seconds to minutes.

2) Long-Term Monitoring (trend mode)

Goal: detect regressions and understand patterns over days/weeks.
Tools: Query Store, PerfMon counters, scheduled DMV snapshots, monitoring platforms.
Time scale: hours to months.

Key teaching point: If you only monitor during incidents, you will miss the “slow creep.”
If you only do long-term charts, you may fail during a live outage. You need both.

Part 2 — The Metrics That Matter (The DBA Language) (25–45 min)

Monitoring is about measuring the right things. These are the most important categories:

Latency: how long requests take (response time).
Throughput: how many requests/transactions per second.
Concurrency: how many users can run simultaneously before blocking/waits explode.
CPU: signal for compute-bound workloads or excessive parallelism.
Memory: buffer pool health, memory grants, caching behavior.
I/O: read/write latency, throughput, log write pressure.
Waits: what SQL Server is waiting on (clues to bottlenecks).
Blocking: lock waits, long transactions, deadlocks.
TempDB: contention, spills, version store growth.

Key teaching point: A slow system is usually caused by one limiting resource (CPU, I/O, blocking, memory grants),
not by “SQL Server being slow.”

Part 3 — Core Monitoring Tools (What Each Tool Is For) (30–55 min)

Tool A: DMVs (Dynamic Management Views)

DMVs are your “live dashboard.” They show what SQL Server is doing right now (and some aggregated history).
DMVs are the first stop during an incident.

Tool B: Query Store (Best for performance history)

Query Store captures query performance over time. It helps you answer:
“Did this query become slower after a deployment?” and “Which plan change caused regression?”

Tool C: Extended Events (XEvents)

Extended Events is the modern replacement for Profiler/Trace for detailed event capture
(deadlocks, long queries, wait events, errors). It is powerful and usually lower overhead when configured correctly.

Tool D: Windows Performance Monitor (PerfMon)

PerfMon provides OS-level counters for CPU, memory, disk, and SQL Server objects. It is essential for understanding
whether the bottleneck is SQL Server internal or the OS/hardware layer.

Key teaching point: You do not need 50 tools. You need the right tool for the question you are answering.

Part 4 — Wait Stats (How to Use Them Correctly) (25–45 min)

Wait stats show where SQL Server spends time waiting. They are one of the fastest ways to narrow down bottlenecks—
but only if interpreted in context.

Correct way to think about waits

Waits are clues, not verdicts.
Look at waits during the problem window (not only “since startup”).
Correlate waits with what the system is doing (top queries, blocking, I/O latency, CPU).
Many waits are normal; focus on what is abnormal for your workload.

Common wait categories (high-level)

CPU pressure: scheduler-related waits (often appears with high CPU and runnable tasks).
I/O pressure: waits related to reading pages from disk (often aligns with high read latency).
Blocking: lock waits (LCK_*), blocking chains, long transactions.
Memory grants: queries waiting for memory to execute (often spills/sorts/hashes).
Log pressure: log write waits under heavy write workloads.

Key teaching point: Wait stats tell you “where time went,” but you still must identify
the specific queries and patterns causing the waits.

Part 5 — Incident Triage Toolkit (What to Run When Production Is Slow) (30–60 min)

The following scripts are a practical “first response kit.” They are safe and read-only.
Students should practice running these scripts and interpreting results.

Script 1 — What is running right now? (active requests)

/* Active requests (what's running now?) */
SELECT
  r.session_id,
  r.status,
  r.command,
  r.cpu_time,
  r.total_elapsed_time,
  r.logical_reads,
  r.reads,
  r.writes,
  r.wait_type,
  r.wait_time,
  r.wait_resource,
  DB_NAME(r.database_id) AS database_name,
  SUBSTRING(t.text, (r.statement_start_offset/2)+1,
    CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), t.text))
         ELSE (r.statement_end_offset - r.statement_start_offset)/2 + 1
    END) AS running_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

How to interpret: Look for long elapsed time, high logical reads, high CPU, and blocking waits.
The running statement tells you what query is actually executing.

Script 2 — Find blocking chains (who is blocking whom)

/* Blocking overview */
SELECT
  r.session_id,
  r.blocking_session_id,
  r.status,
  r.wait_type,
  r.wait_time,
  r.wait_resource,
  r.total_elapsed_time,
  SUBSTRING(t.text, (r.statement_start_offset/2)+1,
    CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), t.text))
         ELSE (r.statement_end_offset - r.statement_start_offset)/2 + 1
    END) AS statement_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

How to interpret: Blocking often causes a “slow system” even if CPU is low.
The next step is to inspect the blocking session and transaction duration.

Script 3 — Top waits (basic triage view)

/* Top waits (since last restart) - starting point */
SELECT TOP (20)
  wait_type,
  wait_time_ms,
  signal_wait_time_ms,
  waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;

How to interpret: Treat as a clue. For real incident analysis, focus on waits during the incident window,
not only since restart.

Script 4 — Top cached queries by logical reads (heavy I/O)

/* Top cached queries by total logical reads */
SELECT TOP (20)
  (qs.total_logical_reads) AS total_logical_reads,
  (qs.total_logical_reads / NULLIF(qs.execution_count,0)) AS avg_logical_reads,
  qs.execution_count,
  (qs.total_worker_time / NULLIF(qs.execution_count,0)) AS avg_cpu,
  (qs.total_elapsed_time / NULLIF(qs.execution_count,0)) AS avg_elapsed,
  DB_NAME(st.dbid) AS database_name,
  LEFT(st.text, 4000) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_logical_reads DESC;

How to interpret: High logical reads indicates inefficient query patterns (scans, poor indexes, large row processing).
These queries are top candidates for tuning.

Part 6 — Query Store (The Best Tool for Regression Detection) (25–50 min)

Query Store helps you see performance changes over time. It is essential in modern SQL Server tuning because it provides:
query history, plan history, runtime stats, and the ability to detect when a query got worse.

What Query Store is best at

Identifying top resource-consuming queries (CPU/reads/duration).
Finding plan changes and correlating them with slowdowns.
Comparing performance “before vs after” deployments.
Providing historical evidence to prevent guessing.

Key teaching point: If you tune without Query Store (or equivalent history), you will repeat problems.
History is what gives you control.

Part 7 — Alerting Without Noise (Designing Useful Alerts) (20–40 min)

Alerts should be actionable. If every alert is “CPU is high,” you will ignore the system.
A good monitoring strategy uses thresholds and context.

Examples of actionable alerts

Blocking longer than X minutes + increasing blocked sessions.
Log file growth is abnormal or log backups are failing.
Disk read latency exceeds a threshold for N minutes.
Top query duration increased 5x compared to baseline.
TempDB running low on space or high contention indicators.

Key teaching point: The best alert is the one that tells you “what to check next.”

Student Lab (60–90 min) — “Build a Monitoring Snapshot Pack”

Lab Outcome

Students will build a simple monitoring snapshot script pack that captures enough information to diagnose a typical slowdown.

Lab Tasks

Create a “snapshot” script that captures: active requests, blocking, top waits, and top queries by reads.
Run the snapshot during normal performance (baseline).
Simulate load (or run a heavy query) and run the snapshot again.
Compare: what changed? Which signals point to the bottleneck?
Write a short summary: “What is slow and why?” using the evidence.

Student Submission Template (Copy/Paste)

Baseline snapshot time:
Incident snapshot time:
Top waits baseline:
Top waits incident:
Top active request(s):
Blocking present? yes/no + details
Top queries by reads/CPU:
Conclusion: likely bottleneck + what you would check next

Common Beginner Mistakes (Monitoring)

Monitoring only CPU and ignoring blocking and I/O.
No baseline (no definition of normal behavior).
Collecting too much data with no purpose (noise overload).
Relying on Profiler for continuous monitoring (high overhead risk in production).
Alerting on symptoms without context (“CPU is high” with no drill-down path).

Class Discussion Prompt

Prompt:
“If you could only collect 5 metrics from SQL Server for long-term monitoring, what would they be and why?
How would those 5 metrics help you diagnose most incidents?”

Homework Assignment

Create a “Monitoring Plan” for a small production SQL Server (1–2 pages).
List: what you will collect, collection frequency, retention period, and alert thresholds.
Explain how you will avoid noise and ensure alerts are actionable.

Quick Knowledge Check (12 questions)

What is the difference between triage monitoring and long-term monitoring?
Why is “CPU is high” not a root cause by itself?
Name 5 key performance metrics categories a DBA should monitor.
What are DMVs used for?
What problem does Query Store solve?
When should you use Extended Events?
Why is Profiler not recommended for continuous monitoring?
What are wait stats and why are they useful?
Why do you need a baseline?
What is alert fatigue and how do you prevent it?
What is one metric that signals blocking problems?
If queries are fast alone but slow under load, what does that suggest?

Closing (What’s next)

In the next lesson, we will cover SQL Server Storage and I/O Performance.
You will learn how SQL Server reads/writes data, how to measure disk latency correctly,
the difference between data file I/O and log I/O, and how to diagnose storage bottlenecks using evidence.

Performance Monitoring Tools and Techniques in SQL Server