Does SQL Server CACHES Query Results?

Question

When I run a query does the SQL Server caches the results?

Because: When I run the below query:

SELECT id
FROM Foo
WHERE Foo.Name LIKE '%bar%'

The query runs for 40 seconds on the 1st time.

But on the second run it takes only a few seconds.

Is this because the execution plan is somehow cached or actually the data is cached so that I can retrieve it much faster on the 2nd run?

Answer 1

SQL Server does not cache the query results, but it caches the data pages it reads in memory. The data from these pages is then used to produce the query result.

You can easily see if the data was read from memory or from disk by setting

SET STATISTICS IO ON

Which returns the following information on execution of the query

Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The difference between logical and physical reads is the data read from memory.

SQL Server will also claim Memory for caching until the maximum (configured, or physical maximum) is reached and then the oldest pages are flushed.