Welcome back. This lesson is where many students finally understand why performance problems can happen
even when “the server has plenty of RAM.” In SQL Server, memory is not just a number—it is a set of mechanisms:
caching, memory clerks, query execution memory grants, and pressure signals.
Two of the most important memory concepts for a DBA are:
the Buffer Pool (data cache) and Memory Grants (execution workspace).
These explain why queries suddenly get slow, why tempdb gets hammered, and why “spills” occur.
Lesson description: You will learn what the SQL Server buffer pool is, how it caches pages,
how read/write behavior changes when pages are in memory vs on disk, and why the buffer pool is central to I/O performance.
You will also learn what memory grants are, which operators request them (sorts, hashes), what happens when SQL Server grants
too little or too much, and how “spills” occur (writing to tempdb because memory was insufficient).
You will practice reading memory evidence using DMVs and understand what to look for during real incidents.
The buffer pool is the primary region of SQL Server memory used to cache database pages (8 KB pages)
after they are read from disk. When SQL Server needs a page:
This is why the same query can be slower the first time (physical reads) and faster later (logical reads).
Many performance problems that look like “SQL is slow” are actually “SQL is doing too much I/O because the needed pages
aren’t in cache or the workload is touching too many pages.”
SQL Server caches many types of pages, including:
The buffer pool is not infinite. Under memory pressure, SQL Server must evict pages (remove them from cache) and later re-read them.
Frequent evictions increase physical reads and slow down workloads.
When SQL Server modifies a page in memory, that page becomes a dirty page
(changed in memory but not yet written back to the data file).
SQL Server later flushes dirty pages to disk during checkpoints or other background write activity.
This is normal and efficient.
Critical link to the transaction log: Data pages can be written later, but log records must be written at commit time
(Write-Ahead Logging). That is why commits depend heavily on log performance even if data files write lazily.
The buffer pool is about caching pages. A memory grant is different:
it is a reservation of memory that SQL Server gives a query at runtime to perform certain operations efficiently.
Memory grants are typically requested for operators such as:
These operations need working space. If they can’t get enough memory, they may use tempdb as overflow.
That overflow is called a spill.
A spill happens when a query operator needs memory (for sort/hash) but does not have enough,
so SQL Server writes intermediate results to tempdb and reads them back.
Spills hurt because they add:
Real-world symptom: “This report query is sometimes fast and sometimes slow.”
Spills are a common reason—especially when memory grants vary due to concurrency.
SQL Server estimates how much memory a query needs based on row count estimates and operator costs.
If estimates are wrong, memory grants can be wrong.
Common reasons estimates are wrong:
DBA mindset: A spill is usually not “a tempdb problem.” It is often a query design + estimate problem.
DBA takeaway: You want memory grants to be “right-sized.”
Too small causes spills. Too large causes memory contention and slowdowns for everyone.
The goal is not to memorize every DMV column. The goal is to learn how to pull evidence when someone says:
“SQL Server is slow,” “tempdb is hot,” or “queries are waiting.”
SELECT
sqlserver_start_time
FROM sys.dm_os_sys_info;
Why it matters: cache is “cold” after restart. Many post-reboot performance complaints are simply cold cache.
SELECT
total_physical_memory_kb / 1024 AS total_physical_memory_mb,
available_physical_memory_kb / 1024 AS available_physical_memory_mb,
total_page_file_kb / 1024 AS total_page_file_mb,
available_page_file_kb / 1024 AS available_page_file_mb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
SELECT
physical_memory_in_use_kb / 1024 AS sql_physical_memory_in_use_mb,
large_page_allocations_kb / 1024 AS large_page_allocations_mb,
locked_page_allocations_kb / 1024 AS locked_page_allocations_mb,
total_virtual_address_space_kb / 1024 AS total_virtual_address_space_mb,
virtual_address_space_committed_kb / 1024 AS vas_committed_mb,
memory_utilization_percentage,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
Interpretation hints:
If process_physical_memory_low = 1, SQL Server feels memory pressure.
If the OS available memory is very low, Windows is under pressure too.
When users say “queries are stuck” or “it’s slow under load,” DBAs often check:
active requests, their waits, and whether memory grants are involved.
SELECT
r.session_id,
r.status,
r.command,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.writes,
r.logical_reads
FROM sys.dm_exec_requests AS r
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;
What to watch: Wait types like RESOURCE_SEMAPHORE often indicate memory grant pressure.
This DMV shows current memory grants for queries.
SELECT
mg.session_id,
mg.request_time,
mg.grant_time,
mg.requested_memory_kb / 1024 AS requested_memory_mb,
mg.granted_memory_kb / 1024 AS granted_memory_mb,
mg.used_memory_kb / 1024 AS used_memory_mb,
mg.max_used_memory_kb / 1024 AS max_used_memory_mb,
mg.wait_time_ms,
mg.is_next_candidate,
mg.dop
FROM sys.dm_exec_query_memory_grants AS mg
ORDER BY mg.requested_memory_kb DESC;
How to interpret:
wait_time_ms, memory grants are constrained.In real incidents, spills show up through a combination of:
RESOURCE_SEMAPHORE) under concurrencyDBA path to solution (high level):
sys.dm_os_sys_memory) and save output to:C:\DBA\Logs\section6-lesson5-os-memory.txtsys.dm_os_process_memory) and save output to:C:\DBA\Logs\section6-lesson5-sql-process-memory.txtsys.dm_exec_query_memory_grants).C:\DBA\Logs\section6-lesson5-memory-grants.txtNext, you will learn exactly what tempdb is used for, why it becomes a hotspot,
and how DBAs configure and troubleshoot it in the real world.
This will connect directly to spills, sorts/hashes, and concurrency behavior.
Not a member yet? Register now
Are you a member? Login now