Performance Myths: The query result cache

Guest Author : Andy Mallon (@AMtwo)

I was recently doing a training session when a developer commented that it was OK to run an expensive query twice because on the second execution, SQL Server would use the "results cache" and be "practically free". It's not the first time I've heard someone refer to a "results cache" in SQL Server. 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."
SQL Server does have a "buffer cache" and the second execution is "faster, but not free."

The SQL Server buffer cache holds data pages in memory, in the exact form that they reside on disk. The second execution will not have to perform physical I/O operations to satisfy the query, because it can use the buffer cache. However, it does have to perform all other operations. Think of it like this: the second execution still executes the entire execution plan, including all the expensive operations. It is faster, but not "practically free."

Let's create a simple example to demonstrate that the second execution can still be slow and expensive.

First, I'll create a table and put some data in it. Imagine this is some big table from a real life system you work on.

CREATE TABLE dbo.SomeBigTable (
    ID INT IDENTITY(1,1),
    PretendRowData NCHAR(4000),
    CONSTRAINT PK_SomeBigTable PRIMARY KEY CLUSTERED (ID)
);

INSERT INTO dbo.SomeBigTable (PretendRowData)
SELECT REPLICATE(N'x',4000)
FROM sys.columns c1, sys.columns c2;

On my test database, this creates a 7.6 GB table. That'll be plenty big to test at scale! Let's start simple. To level the playing field, I'll clear out the cache:

-- Start with a clean cache
DBCC DROPCLEANBUFFERS

Then, I'll run a query three times that pulls back a single row. The first execution will be slow, then the next two will be fast right?

-- Run the query thrice; The second & third executions are free, right?
SELECT PretendRowData
FROM dbo.SomeBigTable
WHERE ID = 1000000;
GO 3

Whoa, all three were super fast. If I run these three executions in Plan Explorer, I see that the first execution consistently shows a duration of 1-2 milliseconds, and the next two executions show a duration is blank. Plan Explorer shows blank duration when the execution time is sub-millisecond. That looks like the myth is true after all! The results cache exists! Those executions were completely free!

Let's try again with a more expensive query. I'm going to force a table scan by applying a function to the ID column. This query gives the exact same result set as the first one, it just uses a different execution plan to get there. Again, we'll clear the cache first, then run the query three times.

-- Start with a clean cache, again
DBCC DROPCLEANBUFFERS
GO
-- Run the query thrice; The second & third executions are free, right?
SELECT PretendRowData
FROM dbo.SomeBigTable
WHERE CONVERT(varchar(10),ID) = 1000000;
GO 3

This query is quite a bit slower. Plan Explorer shows that the execution time on the first execution was 11 seconds, and the next two take around 9 seconds. Faster, but not free.

The query result is the same PretendRowData on all six executions of both flavors of this query. If there were a "results cache" then at least four of the executions should have come from that cache and been "practically free". The second flavor of the query performs a table scan, so it has to read all 7.6GB of data in order to return the single-row result. In fact, Plan Explorer shows that the significant CPU & (logical) I/O cost is equal for all three executions. The query is faster, because the table is in memory in the buffer cache, but it's hardly free.

If you have expensive queries where you want to use cached query results, there are some great technologies that are purpose built just for that. Two popular memory caching products are Redis and Memcached. These memory-optimized caches scale horizontally, making them well-suited for helping to offload expensive, frequent, read-only queries from your production database.

About the Author

Andy Mallon is a SentryOne Product Manager and Microsoft Data Platform MVP. Andy's background is as a DBA supporting high-volume, highly-available OLTP environments with demanding performance needs. Over his career he has managed databases in the healthcare, finance, e-commerce, and non-profit sectors. Andy is the founder of BostonSQL, past organizer of SQLSaturday Boston, and blogs at am2.co.