on February 17, 2011
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.
What I Hadn’t Realized
I don’t think I ever specifically realized that dirty pages could be immediately re-used for query results– but it makes perfect sense. I had only thought about clean pages, which were read in for one query, to be available for re-use.
I felt a little silly when I realized this. Shouldn’t I have known this? But after thinking about it I realized: there’s little gaps like this in most everyone’s knowledge. Sometimes it takes a little bit of extra experience to notice the gap and fill it in. It happens to us all.
After rerunning the commands and including a CHECKPOINT with DBCC DROPCLEANBUFFERS, I see the expected output– a physical read.
REGISTER