Every weekday at 8:15 AM the same graph wobbled. Page life expectancy fell from a comfortable four hours to under two minutes, buffer cache hit ratio dipped to eighty-something percent, and the first tickets arrived around 8:20 saying the application felt sticky. By 9:00 everything looked fine again. The standing proposal in the change queue was "add RAM." The actual fix, when I finally chased it, was a morning dashboard query scanning a 40-million-row table with no supporting index — forty gigabytes of reads flushing through the buffer pool every morning, evicting the working set of every other database on the instance. One covering index later, the 8:15 cliff disappeared and the RAM purchase got cancelled.
Buffer pool diagnosis is a genre of folklore — threshold numbers repeated from blog to blog until they harden into policy. This piece is the version I run on: what the buffer pool actually is, why the two famous counters mislead, how to see what is churning the cache, and what real memory pressure looks like versus a scan pretending to be pressure. Applies to SQL Server 2016 through 2022.
What is the buffer pool, mechanically?
It is the instance's cache of 8 KB data pages, and it is usually the largest single consumer of memory by an enormous margin. Every page read goes through it: a query needs a page, the engine checks the buffer pool, and on a miss it issues the physical read and lands the page in cache. Pages stay until something needs their slot, at which point the least recently used clean pages get evicted — dirty pages must be written first, which is what checkpoints and the lazywriter process are for. The practical consequence: the buffer pool is the reason a well-sized instance serves most reads from memory at microsecond cost, and the reason a too-small or churned one turns everything into disk latency.
Sizing starts with max server memory, which caps the pool. Set it to leave the operating system and everything else breathing — the common guidance of leaving several gigabytes plus a slice per other consumer is a starting point, not a formula, and on boxes running SSIS, SSRS, or agents with heavy workloads the "everything else" is bigger than people assume. Lock pages in memory, which lets SQL Server keep its pool out of the reach of OS memory trimming, is worth enabling on dedicated boxes; the day the OS decides to trim a database server's working set is a day of mysterious latency with clean-looking SQL counters.
Why does page life expectancy lie?
Two reasons, one historical and one architectural. The historical one is the 300-second rule: folklore says PLE under 300 means memory pressure. That number came from an era of four-gigabyte servers. PLE is measured in seconds, and its healthy value scales with pool size — a box with a 500 GB buffer pool holding pages for 300 seconds is churning through its entire cache every five minutes, which is catastrophic, while a box with 16 GB holding pages for 300 seconds is fine. Threshold-free reading is the only honest kind: trend PLE against itself, per instance, and care about sudden drops and sustained shifts, not absolute numbers.
The architectural lie is NUMA. The perf counter "SQLServer:Buffer Manager\Page life expectancy" is an average across NUMA nodes, and on multi-node boxes it hides node-level churn beautifully. The per-node counter, SQLServer:Buffer Node\Page life expectancy, is the one to watch — I have seen a box reporting a healthy average PLE of 900 while node 1 sat at 45 seconds because a scheduler-pinned workload hammered memory allocated on that node. Average counters on partitioned hardware are where false reassurance lives.
How do I see what is actually in the pool?
sys.dm_os_buffer_descriptors lists every cached page with its database, and aggregating it answers the question that matters during churn: whose pages are occupying the cache, and did that mix just change. During the 8:15 incident this query showed a reporting database holding nearly half the pool on an instance whose owner believed it was a five-percent workload:
SELECT
CASE WHEN database_id = 32767 THEN N'RESOURCE' ELSE DB_NAME(database_id) END AS db_name,
COUNT(*) * 8 / 1024 AS cached_mb,
SUM(CAST(is_modified AS bigint)) * 8 / 1024 AS dirty_mb
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY cached_mb DESC;Run it twice — once when healthy, once during the wobble — and the diff usually names the culprit database immediately. From there, sys.dm_exec_query_stats sorted by total logical reads finds the statements doing the evicting: the query that reads forty gigabytes to produce a 200-row dashboard is the query whose pages just displaced everyone else's. Note the framing: the buffer pool is not sick in this scenario, it is doing its job faithfully. The scan is the disease. This is also where the memory-grant side of the story connects — big sorts and hashes hold workspace outside the buffer pool, and the RESOURCE_SEMAPHORE queues from my memory grant notes are a different kind of memory pressure with different counters, easy to confuse from a distance.
What does real memory pressure look like?
Real pressure is sustained, not spiky, and it shows up in the counters that describe the engine fighting itself. Free list stalls — threads waiting because no free buffers were available when a read needed a slot — are the direct one. Lazywriter activity climbing means the engine is continuously scavenging for free pages rather than coasting between checkpoints. Checkpoint pages per second sustained at high values means dirty pages are being forced out fast, which couples pressure to write I/O. And buffer cache hit ratio — the other folklore counter — is useful only in its proper role: it tells you what fraction of reads came from cache, and a sagging ratio with rising physical reads confirms the pool is not holding the working set. It cannot tell you why, which is why it is a confirmation counter and never a diagnosis counter.
The differentiation that decides the fix: churn pressure comes from a workload reading more than the pool can hold — the morning scan — and the fix is almost always to shrink the read, not grow the pool. An index that turns a 40-million-row scan into a 400-row seek reduces buffer demand by five orders of magnitude, and no RAM purchase competes with that. True capacity pressure — the legitimate working set of all databases genuinely exceeds the pool, hit ratio sagging all day, lazywriter busy, no single query to blame — is the only case where more memory is the answer, and it announces itself by being continuous rather than scheduled. If your PLE cliff happens at the same time every day, you do not have a capacity problem. You have a query with a calendar.
What about the plan cache and the other clerks?
They share the same memory budget, and when they bloat, the buffer pool is what shrinks. sys.dm_os_memory_clerks breaks memory consumption down by consumer — the buffer pool clerk dominates on a healthy box, with the plan cache, the columnstore object pool, and various smaller clerks around it. When the plan cache clerk grows abnormally large, the usual suspect is ad-hoc query pollution, which has its own playbook in my plan cache pollution notes — the point here is only that "SQL Server is using lots of memory and the pool seems small" occasionally means the pool got outbid by a sibling, and the clerks view is how you find out. Memory diagnosis without the clerks view is like lock diagnosis without dm_tran_locks: you are guessing at the allocation.
Watching the buffer pool with MonPG when SQL Server support lands
The counters worth graphing are PLE per NUMA node rather than the averaged lie, buffer cache hit ratio next to physical reads per second, cached megabytes per database from dm_os_buffer_descriptors on a schedule, lazywriter and checkpoint rates, and the top logical-read queries as a standing leaderboard. The 8:15 pattern — a scheduled cliff — is exactly the shape a trend graph catches that a threshold alert never will. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, the descriptors query above snapshot twice a day into a table is enough to catch the next dashboard query that thinks it owns the cache.