Roadmap › SQL Server Internals — How the Database Engine Really Works › Module: Query Processing
Lessons in this course (11)
7. Memory — buffer pool, plan cache and memory grants
Intermediate★ Must knowLesson 7 of 11
| Memory area | Holds |
|---|---|
| Buffer pool | 8 KB data/index pages read from disk (clean and dirty pages) — usually the largest consumer |
| Plan cache | Compiled execution plans |
| Query workspace (memory grants) | Sorts, hash joins/aggregates during execution |
| Lock memory, connections, CLR, columnstore object pool, In-Memory OLTP | Other components |
- Logical read = page read from the buffer pool; physical read = page had to come from disk first. Tuning aims to reduce logical reads (less data touched).
- The lazy writer frees memory by writing dirty pages and evicting least-recently-used pages when memory is needed; Page Life Expectancy indicates how long pages stay cached.
- SQL Server takes as much memory as allowed — always set max server memory to leave RAM for the OS and other processes.
- Memory grants are reserved before a query runs based on estimates; under-estimates cause spills to tempdb, over-estimates waste memory and cause
RESOURCE_SEMAPHOREwaits (memory grant feedback in 2019+ adjusts this automatically).