Menu
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?
527
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."
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).
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.
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.
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.
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
