SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer

Clean Cache commands affect different parts of SQL Server memory. I separate buffer-pool testing from execution-plan testing.

The buffer pool holds database pages. The plan cache holds reusable execution plans. Clearing one doesn’t clear the other. I check which cache a test requires before running a command.

-- Isolated test instance only. These commands can affect unrelated workloads.
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

CHECKPOINT writes dirty pages for the current database. DROPCLEANBUFFERS removes clean buffers and supported columnstore cached objects. It does not mean every dirty page from every database has first been written.

FREEPROCCACHE evicts plans. Later executions may compile again. That cost affects other work on a shared instance and can make a timing comparison misleading.

Keep the test conditions comparable

Run the same query and data under documented warm and cold conditions. Record logical reads, physical reads, elapsed time, and the plan. Don’t assume a faster second run proves that changing the query was responsible.

These commands don’t measure application performance by themselves. I keep the query, data and other test conditions comparable. Otherwise, a warmer cache can receive credit for an improvement the query never made.

Reference: DBCC DROPCLEANBUFFERS.

Related reading

• Twitter
• Arrow in Execution Plan – SQL in Sixty Seconds #136
• SSMS Efficiency – Replace STAR – SQL in Sixty Seconds #130
• Cycle Clipboard Ring – SQL in Sixty Seconds #129
• Split Screen for SSMS Efficiency – SQL in Sixty Seconds #120
• Full-Screen Execution Plan – SQL in Sixty Seconds #117

Cache clearing is not a tuning shortcut, it is a controlled test condition.

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