Big Idea

Resource tuning is about workload control. You can’t “configure” your way out of a workload that is doing too much
work. The fastest way to stabilize SQL Server is to identify what is consuming memory/CPU, reduce unnecessary work (reads, sorts, hashes),
and ensure the instance has safe, correct settings (especially max server memory and parallelism).

Recommended Class Duration

120–180 minutes (deep concepts + evidence scripts + lab)

Prerequisites

• Basic SQL and query concepts
• Understanding of bottlenecks (CPU, memory, I/O, blocking) recommended
• Access to SQL Server (lab or local) for practice

A) Buffer Pool (Data Cache)

The buffer pool stores data pages in memory so SQL Server can read them quickly without going to disk. If your working set
(the pages you need most often) fits in the buffer pool, performance is stable and fast. If not, SQL Server must read from disk
more often (physical reads), which is slower and less predictable.

What buffer pool success looks like

• Most reads are logical reads (served from memory)
• Physical reads are low and stable during normal workload
• Peak workload remains predictable

What buffer pool pressure looks like

• Physical reads increase, latency increases, and queries become inconsistent
• Large scans or reporting workloads push useful pages out of cache
• After a large report, the whole system “feels slower” for a while

B) Query Execution Memory (Memory Grants)

Many operations need workspace memory: sort, hash join, hash aggregate,
and sometimes batch mode operations. SQL Server gives queries memory grants so they can execute efficiently. If the grant is too small,
the query can spill to TempDB (slow). If grants are too large or too many queries run at once, queries can wait for memory
(memory grant waits) and the entire system becomes slow during peak time.

What memory grant issues look like

• Large sorts/hashes take long time and may spill to TempDB
• Queries wait before starting because memory is not available
• Peak-time slowdowns: many big queries competing for workspace memory

Key teaching point: Memory grants depend on cardinality estimates. Bad estimates lead to wrong memory grants,
which leads to spills or excessive reservation.

A) Schedulers and Workers (Simple Explanation)

SQL Server assigns work to CPU schedulers. Each running request needs a worker (thread) to do work.
When CPU is busy, requests queue up waiting their turn. This is when you feel “the database is slow” even if your queries are correct.

CPU pressure symptoms (practical)

• Sustained high CPU during business hours
• Many requests running or waiting at the same time
• Large queries go parallel and consume many cores
• Performance collapses under concurrency (too many users at once)

Key teaching point: CPU problems are often workload problems. The fix is frequently:
reduce reads, optimize expensive queries, and control parallelism—not “buy more CPU first.”

Quick Symptom Mapping (Beginner-Friendly)

• CPU-bound: high CPU, many concurrent requests, heavy computations, big joins/sorts.
• Memory pressure: unstable performance, spills to TempDB, queries waiting for memory grants, cache churn.
• I/O-bound: high physical reads, storage latency, slow file reads/writes, slow log writes.
• Blocking: many sessions waiting, timeouts, CPU can be low, lead blocker exists.

Rule: Don’t tune until you identify the dominant bottleneck.

Script 1 — Server basics (CPU, memory, start time)

SELECT
  cpu_count,
  scheduler_count,
  hyperthread_ratio,
  physical_memory_kb/1024 AS physical_memory_mb,
  committed_kb/1024 AS committed_mb,
  committed_target_kb/1024 AS committed_target_mb,
  sqlserver_start_time
FROM sys.dm_os_sys_info;

How to interpret: This gives you the basic hardware and SQL Server memory target/commit levels.
It’s the starting point for any resource discussion.

Script 2 — Where SQL Server memory is being used (memory clerks)

-- Top memory consumers inside SQL Server
SELECT TOP (15)
  type AS memory_clerk,
  pages_kb/1024 AS pages_mb,
  virtual_memory_committed_kb/1024 AS vm_committed_mb,
  virtual_memory_reserved_kb/1024 AS vm_reserved_mb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

How to interpret: This shows broad memory usage categories (cache, query execution structures, etc.).
You don’t “fix” memory clerks directly—this is evidence for what’s dominating.

Script 3 — Active requests (CPU time, reads, waits, blocking)

SELECT
  r.session_id,
  s.login_name,
  s.host_name,
  s.program_name,
  r.status,
  r.cpu_time,
  r.total_elapsed_time,
  r.logical_reads,
  r.reads,
  r.writes,
  r.wait_type,
  r.wait_time,
  r.blocking_session_id,
  DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
  ON r.session_id = s.session_id
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

How to interpret: Sort by CPU to find CPU-hungry requests. Sort by elapsed time to find “long runners.”
If elapsed time is high but CPU is low, it may be waiting (I/O, locks, memory grant).

Script 4 — I/O latency by file (to separate memory vs disk issues)

SELECT
  DB_NAME(vfs.database_id) AS database_name,
  mf.type_desc,
  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_ms,
  CASE WHEN vfs.num_of_writes = 0 THEN 0 ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_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 (vfs.io_stall_read_ms + vfs.io_stall_write_ms) DESC;

How to interpret: High read latency suggests storage pressure or queries doing too many physical reads.
High log write latency suggests log bottlenecks (write-heavy workloads).

Script 5 — Top waits snapshot (clues)

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;

How to interpret: Use waits to guide your investigation.
Then confirm using active requests + I/O latency + query metrics.

A) Fixing CPU pressure

• Find top CPU queries (Query Store / DMVs) and reduce their reads and work.
• Reduce row processing (filter earlier, remove non-SARGable predicates).
• Control parallelism if parallel storms reduce concurrency (MAXDOP/cost threshold with evidence).
• Eliminate RBAR patterns (“row-by-agonizing-row”), especially scalar UDF heavy workloads.
• Fix plan regressions (Query Store history reveals “it got worse after change”).

B) Fixing memory pressure (cache + memory grants)

• Reduce large scans that push useful pages out of cache.
• Fix cardinality estimates (statistics strategy) to improve plan and memory grants.
• Reduce sorts/hashes with better indexes and query rewrites.
• Limit concurrent “big” queries (schedule reporting outside peak; workload governance).
• Review max server memory to ensure SQL has enough memory but OS remains safe.

C) The “Do Not Do This First” list

• Do not restart SQL Server as your first step.
• Do not change multiple settings at once.
• Do not rebuild all indexes as a default response.
• Do not copy/paste settings from the internet without evidence.

Scenario 1: “CPU is 95% every morning”

1. Capture active requests sorted by CPU time.
2. Identify if the same few queries dominate CPU, or if many small queries spike concurrently.
3. Check whether a scheduled job/report runs at that time.
4. Fix: tune top CPU query, reschedule job, control parallelism if storms are happening.
5. Validate with before/after CPU + query metrics.

Scenario 2: “Queries are fast sometimes, slow other times”

1. Suspect memory pressure, plan instability, or parameter sniffing.
2. Compare waits and I/O latency during fast vs slow periods.
3. Check if big scans or reporting jobs occur before slow periods (cache churn).
4. Fix: reduce scans, tune big queries, improve stats, stabilize the plan using Query Store evidence.

Scenario 3: “Queries wait before starting”

This commonly indicates memory grant competition (big sorts/hashes waiting for workspace memory).

1. Check active requests for waits and long elapsed time with low CPU.
2. Look for evidence of sorts/hashes and spills (execution plan warnings in lab).
3. Fix: reduce row counts, add supporting indexes, improve stats, and reduce concurrent heavy queries.

Student Submission Template (Copy/Paste)

• Date/time captured:
• CPU count / scheduler count:
• Physical memory (MB):
• Top 5 memory clerks:
• Top 3 active requests by CPU:
• Highest I/O latency file(s):
• Top 5 waits:
• Primary bottleneck: CPU / Memory / I/O / Blocking
• Evidence summary (6–10 sentences):
• Next step to investigate:

Prompt:
“A system slows down every time a large monthly report runs. After the report finishes, the system stays slower for 20 minutes.
What does this suggest about memory and cache behavior? What would you monitor and what would you change first?”