Clear SQL Server Cache

What is SQL Server Cache?

SQL Server uses memory to cache:

• Execution Plans (Plan Cache)

• Data Pages (Buffer Pool)

• Procedure Cache (Compiled procedures/functions)

This improves performance by reducing the need to recompile queries or fetch data from disk repeatedly.

Why Clear SQL Server Cache?

• For performance testing (benchmarking with a "cold cache")

• To troubleshoot performance issues

• After large data changes

• To free up memory temporarily

Caution

Clearing cache can slow down performance temporarily since SQL Server has to reload data/plans into memory. Do not use on production servers unless necessary.

Commands to Clear SQL Server Cache

Clear the Procedure (Execution Plan) Cache

DBCC FREEPROCCACHE;

Clear the Buffer Pool (Data Cache)

DBCC DROPCLEANBUFFERS;

Clear Both Caches (Common for testing)

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

Permissions Required

• Membership in the sysadmin or serveradmin role.

Useful for:

• Database performance testing

• Resetting memory usage

• Benchmarking different query versions

---

REFERENCES

• https://www.mssqltips.com/sqlservertip/4714/different-ways-to-flush-or-clear-sql-server-cache/