DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS

dbtut November 19, 2019 MSSQL

What is DBCC FREEPROCCACHE in SQL Server

We can reset all plans in the plan cache with DBCC FREEPROCCACHE. Thus, each stored procedure has to be recompiled because there is no execution plan stored in the memory. In some cases (eg if you are having a problem, such as parameter sniffing), recompile the queries may solve your problem. In the article “What is Parameter Sniffing” you will find details about parameter sniffing.

With the following command, you can clear all query plans in plan cache.

Clear All Execution Plans with DBCC FREEPROCCACHE Command

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

1	DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Clear a Specific Plan From Query Plan Cache with DBCC FREEPROCCACHE Command

DBCC FREEPROCCACHE (Write the plan_handle value of your query);

1	DBCC FREEPROCCACHE (Write the plan_handle value of your query);

Find the plan_handle of a Query

SELECT plan_handle, st.text  
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st  
WHERE text LIKE N'SELECT * FROM Person.Person%';

SELECT plan_handle, st.text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'SELECT * FROM Person.Person%';

What is DBCC DROPCLEANBUFFERS in SQL Server

You can clean up unchanged data in the buffer cache with DBCC DROPCLEANBUFFERS. In SQL Server, the data is not read directly from the disk. It is first transferred from disk to memory (buffer cache) and then transmitted to users from the memory. If the data in the memory changes, SQL Server writes the data to the disk when the checkpoint is executed. Data in the memory that has not chanhed are called clean buffer. With DBCC DROPCLEANBUFFERS we clean this clean buffer.

Before you run this command, you can run CHECKPOINT to write the modified data to disk. To understand the working logic of Checkpoint and SQL Server, I recommend reading “What is Database Checkpoint“?

I recommend that you do not use this command in production environments. Because if you execute this command SQL Server must read the data and transfer from disk to memory again.

Clear Clean Buffer with DBCC DROPCLEANBUFFERS

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

1	DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux