DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS

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

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

Find the plan_handle of a Query

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

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *