Troubleshooting Common Performance Problems

Troubleshooting Common Performance Problems in SQL Server

In this lesson, you will learn how to troubleshoot SQL Server performance incidents the way a real DBA does it in production.
You will not “guess and tweak.” You will follow a repeatable incident workflow:
define the symptom → capture evidence → identify the bottleneck → apply a targeted fix → validate and prevent regression.
By the end, you should be able to look at a slow system and quickly classify the problem into one of the main categories:
CPU, I/O, memory, blocking/concurrency, TempDB,
bad plans/parameter sniffing, or maintenance/operational issues.

Big Idea: Most performance incidents are the same problems repeating in different environments.
If you can recognize the symptom patterns and run the right diagnostic scripts, you can solve issues quickly and safely.

Class Overview (What students will learn today)

A DBA incident workflow you can reuse for every troubleshooting case.
How to identify the “big 6” bottlenecks: CPU, I/O, memory, blocking, TempDB, plan instability.
Common symptoms and what they usually mean (timeouts, slowness, spikes, deadlocks).
Practical diagnostic scripts (safe, read-only).
Fast mitigation steps vs permanent fixes.
How to validate improvement and prevent repeat incidents.

Recommended Class Duration

150–240 minutes (incident-style lesson + lab)

Prerequisites

Basic indexing and query optimization knowledge
Basic understanding of locks and blocking
Basic understanding of statistics and execution plans is helpful

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

Use a standard troubleshooting workflow to diagnose performance issues safely.
Identify whether the system is CPU-bound, I/O-bound, memory-pressured, blocked, or plan-unstable.
Find top resource-consuming queries and sessions.
Identify root blockers and long-running transactions.
Explain common wait patterns and what to check next.
Propose a short-term mitigation and long-term fix for each major problem type.

Part 1 — The DBA Incident Workflow (20–35 min)

Step 1: Define the symptom (measurable)

What is slow? Which app endpoint, report, job, or stored procedure?
When did it start? Always or only peak time?
What is the SLA or expected time?
What changed recently? (deployments, data growth, maintenance, configuration, infrastructure)

Step 2: Capture evidence (before changing anything)

Current waits, CPU/memory/disk signals
Top running requests
Blocking chain (if any)
Top queries (Query Store if enabled)

Step 3: Identify the bottleneck

You must classify the incident. Most SQL Server performance problems are one of the categories below.

Step 4: Apply targeted fix → Validate → Prevent regression

Fix only what the evidence supports. Validate with before/after metrics.
Then add monitoring and document the root cause.

Part 2 — The “First 5 Minutes” Triage Scripts (Use These First) (20–40 min)

Script A — What is running right now (top waits, blockers, heavy requests)

/* Triage: current requests and waits */
SELECT
  r.session_id,
  r.status,
  r.blocking_session_id,
  r.wait_type,
  r.wait_time,
  r.wait_resource,
  r.cpu_time,
  r.total_elapsed_time,
  r.logical_reads,
  r.reads,
  r.writes,
  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 statement_text
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 time, and non-zero blocking_session_id.

Script B — Lock waits (LCK waits) and blocked sessions

/* Triage: sessions waiting on locks */
SELECT
  r.session_id,
  r.blocking_session_id,
  r.wait_type,
  r.wait_time,
  r.total_elapsed_time,
  DB_NAME(r.database_id) AS database_name,
  LEFT(t.text, 4000) AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type LIKE 'LCK%'
ORDER BY r.wait_time DESC;

Script C — Top waits since restart (context clue, not verdict)

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

Teaching note: This is “since restart.” For incident work, always combine with current activity and timeframe.

Part 3 — Common Performance Problems (Symptoms → Evidence → Fix) (70–120 min)

Problem Type 1: High CPU (CPU-bound system)

Typical symptoms: CPU stays high, queries slow down, timeouts increase, application feels “laggy,”
and the system cannot keep up during peak.

What to check: Top CPU queries, parallelism behavior, high compile/recompile patterns,
and whether CPU is wasted on scanning too much data.

Quick evidence script — top requests by CPU

/* Top active requests by CPU */
SELECT TOP (20)
  r.session_id,
  r.cpu_time,
  r.total_elapsed_time,
  r.logical_reads,
  r.writes,
  r.wait_type,
  DB_NAME(r.database_id) AS database_name,
  LEFT(t.text, 4000) AS sql_text
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.cpu_time DESC;

Common fixes: index improvements to reduce row scans, query rewrites, fix parameter issues,
reduce excessive parallelism only with evidence, and reduce compilation overhead.

Problem Type 2: High I/O (I/O-bound system)

Typical symptoms: queries are slow with high read time, storage latency spikes, PAGEIOLATCH waits appear,
and large scans dominate.

What to check: queries with very high logical reads, missing/inefficient indexes,
and storage latency at the file level.

Quick evidence script — database file I/O latency

/* File-level I/O latency (ms) */
SELECT
  DB_NAME(vfs.database_id) AS database_name,
  mf.physical_name,
  vfs.num_of_reads,
  vfs.num_of_writes,
  CASE WHEN vfs.num_of_reads = 0 THEN 0
       ELSE vfs.io_stall_read_ms / vfs.num_of_reads END AS avg_read_latency_ms,
  CASE WHEN vfs.num_of_writes = 0 THEN 0
       ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
  ON vfs.database_id = mf.database_id
 AND vfs.file_id = mf.file_id
ORDER BY avg_read_latency_ms DESC;

Common fixes: add/adjust indexes to reduce reads, rewrite queries, reduce returned rows,
and fix storage performance or file placement if storage is the bottleneck.

Problem Type 3: Memory Pressure

Typical symptoms: frequent physical reads, unstable query performance, low effective cache,
high RESOURCE_SEMAPHORE waits (memory grants), and sudden slowdowns with large sorts/hashes.

What to check: whether max server memory is configured properly, memory grants,
and whether queries are requesting huge memory for sorts/joins due to poor estimates.

Quick evidence script — memory grant waits

/* Active requests waiting on memory grants */
SELECT
  r.session_id,
  r.status,
  r.wait_type,
  r.wait_time,
  r.granted_query_memory,
  r.cpu_time,
  r.total_elapsed_time,
  DB_NAME(r.database_id) AS database_name,
  LEFT(t.text, 4000) AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'RESOURCE_SEMAPHORE'
ORDER BY r.wait_time DESC;

Common fixes: set max server memory correctly, tune queries to reduce memory grants,
update statistics to improve estimates, and reduce excessive sorts/hashes (often by indexing or rewriting).

Problem Type 4: Blocking and Concurrency

Typical symptoms: many sessions waiting, LCK waits, timeouts, slowdowns only under concurrency.

What to check: root blocker session, open transaction count, and why the blocker holds locks so long.

Quick evidence script — find the root blocker

/* Blocked sessions and their blockers */
SELECT
  r.session_id,
  r.blocking_session_id,
  r.wait_type,
  r.wait_time,
  r.wait_resource,
  r.total_elapsed_time,
  DB_NAME(r.database_id) AS database_name,
  LEFT(t.text, 4000) AS sql_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;

Common fixes: shorten transactions, add indexes to reduce lock footprint,
batch large updates, and consider versioning isolation (RCSI) where appropriate.

Problem Type 5: TempDB Contention and TempDB I/O

Typical symptoms: sudden system-wide slowdown, heavy sorts/spills, version store growth,
temp object pressure, and high TempDB write activity.

What to check: queries spilling to TempDB (sort/hash spills), version store usage (RCSI/SNAPSHOT),
and TempDB file configuration and storage performance.

Common fixes: reduce spills (indexes, memory grants, stats), tune queries,
ensure TempDB is properly configured and on fast storage.

Problem Type 6: Bad Plans and Plan Regressions (Parameter Sniffing / Plan Changes)

Typical symptoms: the same query is fast sometimes and slow other times, performance changes after a deployment,
or a plan changed unexpectedly.

What to check: plan stability, parameter values, statistics changes, and Query Store (if enabled).

Common fixes: update stats, rewrite query for stable plans, use Query Store plan forcing where appropriate,
and address parameter sniffing with correct design (not random hints).

Part 4 — Mitigation vs Permanent Fix (Critical DBA Skill) (20–35 min)

Mitigation (short-term, restore service)

Stop or reschedule a heavy job that is blocking users.
Kill a runaway session only if you understand the risk and have business approval.
Temporarily throttle a workload (batch size, schedule change, app-level rate limiting).
Stabilize by forcing a known-good plan (Query Store) when appropriate.

Permanent fix (root cause, prevent recurrence)

Index and query redesign
Transaction design fixes (shorter transactions, batching)
Statistics maintenance strategy
Configuration change based on evidence (memory/parallelism/TempDB)
Monitoring and alerting improvements

Key teaching point: A professional DBA can restore service fast (mitigation) and still do the real work (permanent fix).

Student Lab (60–120 min) — “Diagnose the Incident”

Lab Outcome

Students will diagnose an incident scenario, identify the bottleneck category, present evidence, and propose mitigation + permanent fixes.

Lab Tasks

Choose (or be assigned) one scenario: High CPU, High I/O, Blocking, Memory grants, TempDB pressure, Plan regression.
Run the triage scripts and capture screenshots or output.
Identify the bottleneck category and provide 3 pieces of evidence.
Write one mitigation action and one permanent fix plan.
Define how you would validate success and prevent regression.

Student Submission Template (Copy/Paste)

Scenario chosen:
Measured symptom: (latency/throughput/errors)
Bottleneck category: CPU / I-O / memory / blocking / TempDB / plan regression
Evidence (3 points):
Mitigation action:
Permanent fix plan:
Validation plan:
Regression prevention:

Common Troubleshooting Mistakes

Changing settings before capturing evidence.
Ignoring blocking and focusing only on CPU.
Fixing the blocked queries instead of the root blocker.
Assuming “slow storage” without measuring latency.
Assuming “memory is low” without checking memory grants and workload context.
Applying random query hints that cause future instability.
Not validating under concurrency and real workload.

Class Discussion Prompt

Prompt:
“If your manager says: ‘The database is slow,’ what are the first 5 questions you ask before touching SQL Server?
Then, what are the first 3 scripts you run to capture evidence?”

Homework Assignment

Create a one-page “Incident Response Card” for SQL Server performance issues.
Include: triage scripts, evidence checklist, bottleneck categories, mitigation options, and escalation triggers.
Bonus: add a section for “what not to do” during an outage (common dangerous actions).

Quick Knowledge Check (12 questions)

What is the correct order of the incident workflow?
Why must you capture evidence before changes?
Name the “big 6” bottleneck categories.
What does blocking_session_id tell you?
What does high avg_read_latency_ms usually suggest?
What does RESOURCE_SEMAPHORE usually indicate?
Why can a query be fast sometimes and slow other times?
What is the difference between mitigation and permanent fix?
Why are wait stats “clues” rather than verdicts?
Why must validation include concurrency?
Give one example of a safe mitigation action.
What is one way to prevent future regressions?

Closing (What’s next)

In the next lesson, we will cover Case Studies and Real-World Scenarios.
We will walk through realistic production incidents end-to-end: what happened, how we diagnosed it,
what we changed, how we validated it, and how we prevented it from happening again.

Troubleshooting Common Performance Problems