SQL server : clearing cache has no effect on IO stats

Question

Query used:

(had copied the cache cleanup code from somewhere online)

CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO
DBCC DROPCLEANBUFFERS
GO

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

select a.a,a.b from t1 a JOIN t1 b on a.i=b.i

t1 's schema is

CREATE TABLE [dbo].[t1](
    [i] [int] NULL,
    [a] [varchar](1000) NULL,
    [b] [varchar](1000) NULL,
    [c] [varchar](1000) NULL
) ON [PRIMARY]

And t1 is populated with random data (I ranges from 0-11 and a,b,c are populated with NEWID() values)

It contains about 900 rows (934 to be exact)

The results of executing the query are:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 55 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 4 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

(115974 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 't1'. Scan count 2, logical reads 32, physical reads 0, read-ahead reads 20, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 79 ms, elapsed time = 15858 ms.

As I understand it, a clean cache should result in a significant number of physical reads, shouldn't it?

(SQL server 2012 running on a Win 8 guest VM running on Hyper V on a Win 8 host)

Martin Smith Martin Smith · Accepted Answer · 2012-11-09T11:45:51

The item you have highlighted is for a Worktable (e.g. spool) created in tempdb during the course of the query. Not a pre-existing table that needs to be read in from disc.

The line for t1 should show some physical reads and or read-ahead reads.

(Edit: Didn't see that was included originally). The below shows 20 read-ahead reads from t1

Table 't1'. Scan count 2, logical reads 32, physical reads 0, read-ahead reads 20

This means all pages were brought into cache by the read ahead mechanism.

SQL server : clearing cache has no effect on IO stats

1 Answers