DBCC DROPCLEANBUFFERS Impact on Memory: See a Cold Cache in Action

DBCC DROPCLEANBUFFERS removes the clean pages from memory, so the next query has to read from disk again. It is a tool for a test server. Counting the pages before and after shows the effect.

What the Command Does

SQL Server keeps data pages in memory. A clean page matches its copy on disk. A dirty page holds a change that is not yet written. The command removes the clean pages from memory. It leaves the dirty pages alone, because their changes are not in the data file yet.

It works on the whole instance, not on one database. It needs the ALTER SERVER STATE permission, which members of the sysadmin role have. Every database on the server reads its data from disk again afterward. That makes it a command for a test server. Nothing is lost when you run it. The pages come back, one query at a time. On a shared test server, tell your colleagues first. Their queries slow down until their pages return.

The post CHECKPOINT Impact on Memory: Watch Dirty Pages Go to Zero explains how dirty pages become clean pages. The same demo idea is used here.

Build the Demo

The first script creates a database named CleanBuffersDemo with a table of 12,000 rows. The line with TARGET_RECOVERY_TIME = 0 SECONDS stops the background flush for this database. The dirty pages then stay long enough to see. A small view counts the pages of the current database by state.

IF DB_ID(N'CleanBuffersDemo') IS NULL CREATE DATABASE CleanBuffersDemo;
GO
ALTER DATABASE CleanBuffersDemo SET TARGET_RECOVERY_TIME = 0 SECONDS;
GO
USE CleanBuffersDemo;
GO
DROP TABLE IF EXISTS dbo.Pantry;
CREATE TABLE dbo.Pantry (ItemID int IDENTITY(1,1) PRIMARY KEY, Shelf int NOT NULL, Item char(100) NOT NULL);
INSERT INTO dbo.Pantry (Shelf, Item)
SELECT n % 20, 'jar'
FROM (SELECT TOP (12000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS t;
GO
CREATE OR ALTER VIEW dbo.BufferPages
AS
SELECT CASE is_modified WHEN 1 THEN 'Dirty' ELSE 'Clean' END AS PageState, COUNT(*) AS Pages
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
GROUP BY is_modified;

Right after the load, the table has both kinds of page in memory.

SELECT PageState, Pages FROM dbo.BufferPages ORDER BY PageState DESC;

Run the Command

Now remove the clean pages and count again. Run this on a test server only. Your numbers will differ a little, but the pattern holds.

DBCC DROPCLEANBUFFERS;

SELECT PageState, Pages FROM dbo.BufferPages ORDER BY PageState DESC;

The 229 clean pages are gone. The 295 dirty pages are still in memory. This is why a CHECKPOINT comes first. It writes the dirty pages to disk and turns them into clean pages, so the command can remove them. Run both in this order, and the database is empty in memory.

CHECKPOINT;

DBCC DROPCLEANBUFFERS;

SELECT COUNT(*) AS PagesInMemory FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID();

Read From Disk, Then From Memory

Now the effect on a query. SET STATISTICS IO reports where each page came from. Run the same count twice. The first run starts from an empty cache.

SET STATISTICS IO ON;

SELECT COUNT(*) AS ItemsOnShelves FROM dbo.Pantry;

SELECT COUNT(*) AS ItemsOnShelves FROM dbo.Pantry;

SET STATISTICS IO OFF;

Both runs read 176 pages from the buffer pool, so the logical reads match. The cold run also went to disk. It fetched one page directly, and read-ahead brought in 181 pages in large requests before the scan needed them. The warm run found every page in memory, and the disk stayed quiet. The physical and read-ahead counts change from run to run, but the 176 logical reads do not. Logical reads show how much work a query asks for. Physical reads show how much the disk did.

Count the pages in memory again, and the number is back to about 200. The query pulled in the 176 pages of the table and some system pages with them.

When a Cold Cache Is Useful

Use DBCC DROPCLEANBUFFERS when you want to measure the worst case. The first reads after a restart or a failover run against a cold cache. A query that takes two seconds warm can take far longer cold. Test your slowest report both ways, and you know what a user sees on Monday morning after a restart.

It also helps when you compare two versions of a query. Warm runs hide the cost of reading pages. A cold run shows it.

Know what the command leaves alone. DBCC DROPCLEANBUFFERS clears data pages only. It does not clear the plan cache, so queries keep their plans. It does not clear caches outside SQL Server. The file system cache and the cache of a storage array stay as they are. A cold run here is cold for SQL Server, and the storage can still answer from its own memory.

You could argue that a cold test is unrealistic, because a production server stays warm. That is true. A cold test is a worst case, not a typical day. Keep both numbers.

What to Remember

DBCC DROPCLEANBUFFERS removes clean pages from the whole instance. Run CHECKPOINT first, so dirty pages become clean. Run the pair on a test server only, because every database reads from disk again afterward. After the command, the first query reads from disk and the second finds the pages in memory.

When you finish with the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'CleanBuffersDemo') IS NOT NULL
BEGIN
    ALTER DATABASE CleanBuffersDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CleanBuffersDemo;
END;

A cold cache is not a slow server, it is a server that has not read yet.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.