Your monitoring shows the database's buffer cache hit ratio steady at 99.5%, yet query latency has doubled. Explain how a high hit ratio can coexist with a serious performance problem, and what you would measure instead.
answer
- ratio = hits/(hits+misses) over accesses, not over work
- bad plan -> more hits -> better ratio, worse system
- 0.5% of a huge number is still huge
- cumulative-since-startup counters go flat and lie
- logical reads per execution is the real number
basics
~20 sHit ratio is a ratio of page accesses, not of work. A query doing a hundred times more page accesses than needed can keep the ratio high while burning CPU and time. Measure absolute logical reads per query, physical reads, and latency - not the ratio.
solid answer
~50 sThe hit ratio is `hits / (hits + misses)` over **page accesses**, so it measures where pages came from, not how many were needed. A bad plan that switches from an index lookup to a nested-loop scan may touch 10,000 cached pages per row instead of 4 - every one a hit. The ratio goes *up*, the work explodes. This is the well-known pathology: the ratio rises when things get worse. It is also insensitive to the shape of misses. Half a percent of a huge number of accesses can still be tens of thousands of physical reads per second, and a ratio averaged over an hour hides a two-minute cliff. What to look at instead: absolute **logical reads (buffer gets) per execution** for the top statements, absolute physical read counts and I/O wait, latency percentiles, and eviction/write-back rates. Diagnose from the statements downwards: which query changed, how many pages it touches now versus before, and why the plan changed.
go deeper
Know what the ratio is and that a high value does not by itself mean the database is healthy.
Explain that the ratio counts page accesses, so a plan that touches far more cached pages raises it while making things slower.
Drive a diagnosis: latency percentiles, logical reads per execution against a baseline, absolute physical reads, wait breakdown; and know when a falling ratio is genuine evidence of an undersized pool.
Talk about metric design - ratios versus absolute work, why a target that rewards the wrong behaviour is worse than no target, and what the SLO-facing dashboard should show instead.
## What the ratio actually measures Buffer cache hit ratio = page accesses served from the pool divided by total page accesses. Both numerator and denominator are *page accesses the engine chose to make*. Nothing in the formula knows whether those accesses were necessary. That single fact is the source of every misuse of the metric. ## Failure mode 1: the ratio improves as the system degrades Suppose a query used an index and read 4 pages per execution, 3 of them cached: ratio contribution 0.75. A statistics change flips the plan to a nested loop that scans a small-but-cached table repeatedly, and the same query now performs 40,000 page accesses, 39,900 of them hits: contribution 0.9975. The system is now dramatically slower - CPU-bound in the buffer manager, latching and unlatching millions of frames per second - and the dashboard shows an *improved* hit ratio. Any metric that moves the wrong way under a real regression cannot be a health indicator. This is why experienced DBAs say the hit ratio is a workload descriptor, not a target. Tuning *toward* a hit-ratio number actively rewards plans that touch more cached pages. ## Failure mode 2: the missing denominator 99.5% sounds close to perfect, but 0.5% of what? At 2 million page accesses per second, 0.5% is 10,000 physical reads per second - which may be at or past the device's capability. The percentage discards the magnitude, and the magnitude is what the storage device experiences. Always pair a ratio with the absolute counters behind it. ## Failure mode 3: averaging hides the incident A one-hour average of 99.5% is compatible with a three-minute window at 60% - exactly the window in which the incident happened. Ratios must be evaluated over short intervals aligned with the latency spike, computed as a delta between snapshots rather than from since-startup cumulative counters (which asymptotically freeze and are useless for diagnosis on a long-running instance). ## Failure mode 4: hits are not free A logical read still costs: hash the page identity, acquire the buffer mapping, pin, latch, scan the page's line pointers, evaluate the predicate, unlatch, unpin. On a many-core server, hot pages produce **latch contention** - many cores fighting over the same frame or the same buffer-mapping partition. The symptom is CPU burn and waits on internal synchronization with a 100% hit ratio and zero physical I/O. Read-mostly workloads on a very hot index root are the classic case. ## Failure mode 5: the problem isn't the buffer pool Latency can double for many reasons the pool is blind to: lock or row-level contention, plan regressions, a runaway connection count, write-ahead log flush stalls at commit, checkpoint-driven write bursts, a degraded storage device with the same IOPS but higher latency, or replication lag pressure. If the hit ratio hasn't changed, the pool is probably not the story - which is itself useful information. ## What to measure instead Work downwards, from user-visible pain to mechanism: 1. **Latency percentiles per statement**, and which statements changed. p99, not mean. 2. **Logical reads (buffer gets) per execution** for the top statements, compared against a known-good baseline. A jump here with stable row counts means a plan regression - the single most common cause of 'high hit ratio, terrible latency'. 3. **Absolute physical reads and write-backs per second**, plus device-level latency (await/service time). Distinguish 'many I/Os' from 'slow I/Os'. 4. **Wait/event breakdown**: is time spent on I/O, on locks, on buffer latches, on log flushes, on CPU? 5. **Eviction and dirty-page activity**: a rising eviction rate says the working set no longer fits, which is a genuine buffer-pool signal. 6. **Rows examined versus rows returned** - the plan-level analogue of logical reads, and the number that actually tells you whether the query is doing sensible work. ## Where the ratio is still useful As a *trend* on a stable workload, a falling hit ratio combined with rising physical reads and rising eviction rate is real evidence that the working set has outgrown the pool - for example after data growth or a schema change. Used that way, as one signal among several with the absolute counters visible beside it, it is fine. Used as a target to maximize, it is actively misleading. ## The interview answer in one line Hit ratio measures where pages came from, not how many pages the query should have touched; diagnose from statement-level logical reads and latency, and treat the ratio only as a trend on an unchanged workload.
- Give a concrete mechanism by which a query rewrite makes the hit ratio go up while latency gets worse.Replace an index range scan with a nested loop that re-scans a small cached lookup table once per outer row. The inner table stays fully resident, so nearly every one of the now-millions of page accesses is a hit, pushing the ratio toward 100%. Total page accesses per execution rise by orders of magnitude, so CPU time, latching and elapsed time all rise even though physical I/O may fall to zero.
- You see 100% hit ratio, no physical I/O, high CPU, and heavy waits on buffer latches. What is happening?Many cores are contending for the same hot frames or the same buffer-mapping partitions - typically an index root or a very small, very hot table. Every access is a hit but each one must acquire and release synchronization on shared structures, so throughput is limited by contention rather than storage. Fixes point at reducing accesses per operation, partitioning the hot object, or caching at the application layer.
saying these in an interview costs you the question
- Treating hit ratio as a tuning target to maximize
- Concluding from a high ratio that the buffer pool is correctly sized
- Reading a since-startup cumulative ratio instead of a delta over the incident window
- Assuming a logical read is free because it involves no disk I/O
- Ignoring the absolute number of physical reads hidden behind the percentage