Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does SQL Server CACHES Query Results? [duplicate]

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?

like image 527
pencilCake Avatar asked Apr 05 '13 05:04

pencilCake


People also ask

Does SQL Server cache query results?

This is one of those myths that is almost true, which makes it that much more believable. If you don't know better, you might think SQL Server has a "results cache" because the second execution of a query is often faster. SQL Server does not have a "results cache" and the second execution is not "practically free."

How does SQL Server cache work?

In SQL Server, the buffer cache is the memory that allows you to query frequently accessed data quickly. When data is written to or read from a SQL Server database, the buffer manager copies it into the buffer cache (aka the buffer pool).

Does SQL Server cache query plan?

Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn't need to create another query plan; rather it uses the cached query plan which improved database performance.

Does caching improve query performance?

Performance : Caching techniques are commonly used to improve application performance by storing relevant data as close as possible to the data consumer, thus avoiding repetitive data creation, processing, and transportation.


1 Answers

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 least recently used pages are flushed.

like image 118
Filip De Vos Avatar answered Sep 23 '22 03:09

Filip De Vos