How to Manage Cache Behavior in SQL Server

Galaxy Glossary

How do I clear or bypass cache in SQL Server?

Manage how SQL Server stores and evicts data pages and execution plans to improve performance or troubleshoot issues.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why change SQL Server cache behavior?

Adjusting cache lets you flush problematic execution plans, measure true I/O costs, or prevent parameter sniffing issues. It is a quick, non-destructive way to reset performance baselines.

How do I clear the entire plan cache?

Run DBCC FREEPROCCACHE; to remove all cached execution plans. Use it only in staging or during off-hours because it forces recompilation of every query.

Can I drop a single plan instead?

Yes.Retrieve the plan_handle from sys.dm_exec_cached_plans and pass it: DBCC FREEPROCCACHE (plan_handle); This targets one bad plan without disrupting others.

How do I clear the buffer cache?

Execute DBCC DROPCLEANBUFFERS; after CHECKPOINT; to flush data pages from memory. Ideal for benchmarking disk performance.

How can I stop a query from being cached?

Add the query hint OPTION (RECOMPILE).SQL Server compiles, runs, and discards the plan immediately, preventing parameter sniffing.

What metadata helps diagnose cache issues?

Dynamic management views such as sys.dm_exec_query_stats, sys.dm_exec_cached_plans, and sys.dm_exec_query_plan expose plan reuse counts, memory size, and SQL text.

Best practices to manage cache safely

Prefer targeted plan clears, schedule global clears during low traffic, monitor recompilations with sys.dm_exec_query_stats, and always test in lower environments first.

.

Why How to Manage Cache Behavior in SQL Server is important

How to Manage Cache Behavior in SQL Server Example Usage


-- Remove only the plan for a slow customer order report
DECLARE @plan VARBINARY(64);
SELECT @plan = cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.text LIKE N'%FROM Orders o JOIN Customers c ON c.id = o.customer_id%';

DBCC FREEPROCCACHE (@plan);

-- Rerun the report to force a fresh plan
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total_amount) AS lifetime_value
FROM Customers c
LEFT JOIN Orders o ON o.customer_id = c.id
GROUP BY c.name;

How to Manage Cache Behavior in SQL Server Syntax


-- Clear entire plan cache
DBCC FREEPROCCACHE;

-- Clear a specific plan
DECLARE @handle VARBINARY(64);
SELECT TOP 1 @handle = cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_sql_text(cp.plan_handle) AS st ON 1=1
WHERE st.text LIKE N'%SELECT%Orders%';
DBCC FREEPROCCACHE (@handle);

-- Clear buffer cache (data pages)
CHECKPOINT;
DBCC DROPCLEANBUFFERS;

-- Prevent caching for a single query
SELECT product_id, SUM(quantity) AS total_sold
FROM OrderItems
GROUP BY product_id
OPTION (RECOMPILE);

Common Mistakes

Running DBCC FREEPROCCACHE in production peak hours. It forces every subsequent query to recompile, spiking CPU usage. Fix: schedule during maintenance or target a single plan handle.
Flushing buffer cache without CHECKPOINT. Dirty pages remain, so DROPCLEANBUFFERS has little effect. Fix: issue CHECKPOINT first, then DBCC DROPCLEANBUFFERS.

Frequently Asked Questions (FAQs)

Does clearing cache delete data?

No. It only removes in-memory copies; data remains on disk.

How can I see which plans use most memory?

Query sys.dm_exec_cached_plans ordered by size_in_bytes to identify heavy plans.

Is OPTION(RECOMPILE) safe for frequent queries?

Use sparingly; constant recompilation increases CPU. Consider plan guides or parameterization instead.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie