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
1 | DBCC FREEPROCCACHE WITH NO_INFOMSGS; |
Clear a Specific Plan From Query Plan Cache with DBCC FREEPROCCACHE Command
1 | DBCC FREEPROCCACHE (Write the plan_handle value of your query); |
Find the plan_handle of a Query
1 2 3 4 | 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
1 | DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; |