The second execution can look wonderfully fast because the first execution already fetched the required pages. A fair cold cache comparison records that difference instead of presenting the best-looking run as the whole story.
Define the Cold Cache You Are Testing
SQL Server's buffer pool holds database pages, while its plan cache holds compiled execution plans. A query can have cached pages without an existing compiled plan. It can also reuse a plan while fetching pages that are absent from the buffer pool.
Keep those conditions separate in the test description. This article compares data-cache conditions while avoiding an unnecessary plan-cache reset. Record compilation separately when first-execution compilation is part of the question.
I label each measurement with its cache condition before comparing durations. I also keep the first execution instead of quietly discarding it. The fastest sample deserves a label, rather than a promotion to universal truth.
Production workloads rarely fit a completely empty or completely full cache description. Some frequently used pages remain resident while other pages need reads. Concurrent requests and memory pressure change that mixture throughout the day.
Use a Dedicated Test Instance
Never run DBCC DROPCLEANBUFFERS in production. On SQL Server, this command removes clean buffers across the instance, rather than only your query's pages. It can force unrelated workloads to fetch their data again.
A test database on a shared production instance is therefore insufficient isolation. Use a dedicated nonproduction SQL Server instance with no unrelated workload. Confirm the instance identity and obtain the required test permissions before proceeding.
The sample assumes an existing isolated database named CacheTestDb on that dedicated instance. It creates a workload table with predictable input data. Verify the database and object names are reserved for this experiment.
USE CacheTestDb;
GO
DROP TABLE IF EXISTS dbo.CacheSales;
CREATE TABLE dbo.CacheSales
(
SaleId int NOT NULL PRIMARY KEY,
Amount int NOT NULL,
Padding char(200) NOT NULL
);
WITH Digits AS
(
SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
), Numbers AS
(
SELECT a.n + 10*b.n + 100*c.n + 1000*d.n + 10000*e.n + 1 AS n
FROM Digits AS a CROSS JOIN Digits AS b CROSS JOIN Digits AS c
CROSS JOIN Digits AS d CROSS JOIN Digits AS e
)
INSERT dbo.CacheSales(SaleId, Amount, Padding)
SELECT n, n % 100, REPLICATE('x', 200) FROM Numbers;The generated row count and payload width are test inputs, not measured read counts. Use a representative restored dataset for decisions about an actual query. A synthetic table explains the method without representing every production storage pattern.
Run the Query Before Clearing Buffers
Execute the target query once to establish its compiled form and basic correctness. Record its plan and results independently of the timed comparison. Keep the text and values identical during the measured executions.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SUM(CONVERT(bigint, Amount)) AS TotalAmount
FROM dbo.CacheSales
WHERE SaleId <= 80000
OPTION (MAXDOP 1);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;MAXDOP one limits one source of variation in this demonstration. It is not a recommendation to force production reporting queries to run serially. Preserve the actual supported execution configuration when investigating a real application query.
The setup insert has already affected cache contents. An immediate read after inserting data is therefore not a valid demonstration of uncached pages. My first run after the insert reported zero physical reads. Establish the intended condition explicitly before describing the next run as a data-cache comparison.
Keep plan capture overhead consistent between runs. Collect a separate explanatory actual plan when needed, or enable the same collection for both measurements. Changing instrumentation and cache state together weakens the comparison.
Checkpoint Before Dropping Clean Buffers
CHECKPOINT writes dirty pages for the current database, making those buffers eligible for clean-buffer removal. It does not flush every database's dirty pages. The following guarded block belongs only on the dedicated test instance.
DECLARE @ConfirmedDedicatedTestInstance bit = 0;
-- Change to 1 only after verifying this is a dedicated nonproduction instance.
IF @ConfirmedDedicatedTestInstance <> 1
THROW 50001, 'Dedicated test-instance confirmation is required.', 1;
IF DB_NAME() <> N'CacheTestDb'
THROW 50002, 'Connect to the isolated cache test database.', 1;
SELECT CONVERT(nvarchar(128), SERVERPROPERTY('ServerName')) AS TestInstance;
CHECKPOINT;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;The confirmation flag prevents accidental execution of an unchanged sample. The database-name check cannot prove that the instance is isolated. Verify that requirement operationally before setting the flag.
Required permissions vary by SQL Server version. SQL Server 2022 and later document ALTER SERVER STATE for the buffer-clearing command. Request appropriate access for the test without turning this maintenance command into a production diagnostic habit.
Dropping SQL Server buffers does not establish an empty storage-controller or operating-system cache. A physical database read can still benefit from caching below SQL Server. Describe the experiment as a buffer-pool test rather than a guarantee of raw-device performance.
Compare the Cold Cache Run With the Repeated Run
Run the same query immediately after the approved buffer clearing. Then execute it again without another reset. Capture both outputs from STATISTICS IO and STATISTICS TIME with their sequence and timestamps.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- First execution after the dedicated test-instance buffer reset.
SELECT SUM(CONVERT(bigint, Amount)) AS TotalAmount
FROM dbo.CacheSales
WHERE SaleId <= 80000
OPTION (MAXDOP 1);
-- Repeated execution without another buffer reset.
SELECT SUM(CONVERT(bigint, Amount)) AS TotalAmount
FROM dbo.CacheSales
WHERE SaleId <= 80000
OPTION (MAXDOP 1);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Logical reads count page accesses through the data cache. Physical reads report pages fetched from storage, while read-ahead records its separate prefetch activity. Inspect those categories together rather than looking only at the physical-read field.
The first run can populate pages that the next run reuses. Logical reads still occur during a warm execution because the query still accesses pages. Zero physical reads do not mean the query performed zero work.
Elapsed time also includes waiting, scheduling, and other runtime effects. CPU time describes processor work rather than complete user latency. Preserve both alongside the read information instead of ranking queries by one favorable number.
Report Representative Behavior Alongside the Experiment
Repeat a small number of controlled pairs when results vary materially. Keep parameter values, data, indexes, plan, and concurrent workload conditions documented. Do not keep resetting until one attractive duration appears.
A query's cold cache behavior helps explain first-touch cost and post-restart experience. Its warm behavior helps explain repeated access to resident data. Neither measurement alone describes every request under ordinary production concurrency.
Does the application's real workload repeatedly touch the same pages, or continually reach a much larger working set? Compare that access pattern with the controlled experiment. An index that reduces logical work can help across more conditions than a timing improvement based only on residency.
Record test scope, cache boundary, query plan, reads, CPU, elapsed time, and sample count. Preserve raw measured messages without replacing them with invented example timings. The test is useful when another person can reproduce its conditions and understand its limits.
Never carry a cold cache buffer reset into production to verify a tuning change. Observe representative production executions and compare interval evidence instead. Keep deliberate disruption inside the isolated experiment where its effects are understood.
Related reading on this blog: SET STATISTICS IO ON: SQL in Sixty Seconds #128 and Data Pages In Memory Buffer Pool: sys.dm_os_buffer_descriptors.
A fast repeated execution is not a complete benchmark, it is one measurement under a particular cache condition.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
