Why SQL Server Has No Result Cache: Buffer Pool and Plan Cache

SQL Server has no result cache: a repeated query is run again, not answered from memory. The buffer pool and the plan cache make the second run cheaper. They never store your answer.

Why the second run feels faster

A colleague runs a report. It takes eight seconds. They run it again and it takes one. “SQL Server cached the result,” they say. It is a natural guess. It is also not what happened.

Two real things got reused. The buffer pool keeps data pages in memory, so the second run does not wait for the disk. The plan cache keeps the compiled plan, so the second run skips compilation. Both save time. Neither one remembers the answer.

Let me prove it on a small table. The demo uses a temp table with 5,000 rows, each worth 1. The total should be 5000.00.

DROP TABLE IF EXISTS #CacheNumbers;
CREATE TABLE #CacheNumbers (Id int PRIMARY KEY, Amount decimal(12,2));

INSERT #CacheNumbers (Id, Amount)
SELECT value, 1 FROM GENERATE_SERIES(1, 5000);

SET STATISTICS IO ON;

Run the same query twice

The command GO 2 runs the batch two times in a row. I turned on STATISTICS IO, so each run reports how many pages it read. Logical reads count pages read from memory. If SQL Server had stored the answer, the second run would show no reads at all.

SELECT SUM(Amount) AS TotalAmount FROM #CacheNumbers;
GO 2

Both runs return 5000.00. Look at the Messages tab. Each run reports the same logical reads, and none are zero. The second run went to the pages again and added up every row. The pages were already in memory, which is why it is cheap. It was still real work.

See the plan being reused

Now ask the plan cache what it knows about this query. The view sys.dm_exec_query_stats keeps a count of executions per cached plan.

SELECT SUM(qs.execution_count) AS Executions,
       SUM(qs.total_logical_reads) / SUM(qs.execution_count) AS ReadsPerRun
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%SUM(Amount) AS TotalAmount FROM #CacheNumbers%'
  AND st.text NOT LIKE N'%dm_exec_query_stats%';

Executions is 2, and the reads per run match what the messages said. The plan cache matches on the text of the query, so the same text found the same plan twice, and both times the plan read the data. So the plan cache did exactly what it promises. It saved compile time, not row reading.

Change one row and ask again

Here is the test that settles the argument. Change a single amount from 1 to 2, then run the same query text a third time.

UPDATE #CacheNumbers SET Amount = 2 WHERE Id = 1;
GO
SELECT SUM(Amount) AS TotalAmount FROM #CacheNumbers;

The total is now 5001.00. Nothing told SQL Server to forget a stored answer, because there was none to forget. The query ran again and saw the new value. If you check the plan cache once more, the same plan has served another run.

SELECT SUM(qs.execution_count) AS Executions,
       SUM(qs.total_logical_reads) / SUM(qs.execution_count) AS ReadsPerRun
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%SUM(Amount) AS TotalAmount FROM #CacheNumbers%'
  AND st.text NOT LIKE N'%dm_exec_query_stats%';

Executions is now 3. The plan is reused. The answer is always fresh.

Where caching really lives

If you want a stored answer, you build it. An application cache keeps the result under a key, and it needs an expiry rule and an invalidation rule. Someone has to decide how stale an answer may be. An indexed view stores aggregated rows in a table, which SQL Server keeps up to date, at a maintenance cost. Both are design choices. Neither is the plan cache.

One warning. Do not clear the shared caches on a working server to force a cold run. Everyone else on the instance pays for it. A demo like this one needs no such trick.

The last block turns off the statistics and drops the temp table.

SET STATISTICS IO OFF;
DROP TABLE IF EXISTS #CacheNumbers;

Next time a query gets faster on its second run, ask which work was reused.

A warm page is not a cached answer, it is a shortcut for the next execution.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.