# Dirty Pages and Statistics IO

Warning: The DROPCLEANBUFFERS command referenced in this post is appropriate for test systems only and impacts the entire SQL Instance. If you are new to SQL Server, please use this commands with care, and be careful to read the linked Books Online documentation. Happy testing!

The other day I was running some test queries and looking at the number of reads, and I noticed something funny.

I was dropping clean buffers prior to running a query, but I would sometimes see that there had been no physical reads.

No physical reads? Where was the data coming from?

I was working on a small number of rows, but it still bothered me.

The output looked like this:

]

## The Set-Up

Here’s a simple simulation of what I was doing. First, create a database and insert some values.

SET NOCOUNT ON;
GO
SET STATISTICS IO OFF;
GO
create database dirtyBuffers
GO
USE dirtyBuffers
GO
--Create a table and insert some values
create table dbo.testme (
i int identity,
j char(2000) default 'baroo'
)
GO
insert dbo.testme default values
GO 20


Then, turn on Statistics IO so we can see read information. Drop clean buffers, so data isn’t in memory. Then run a query.

SET STATISTICS IO ON;

DBCC DROPCLEANBUFFERS
GO

--Select some rows
select * from dbo.testme


It should read it from disk, right?

## What I Forgot

I was forgetting about dirty pages. In  order to get a “cold cache”, you need to first run a CHECKPOINT command to flush dirty pages to disk, then run DBCC DROPCLEANBUFFERS to remove everything from the bufferpool. This is very well documented in Books Online.

This was easy to forget because typically I test execution of queries against a restored copy of a production database, or a dataset which isn’t changing.