A quick one today; During development, I often find myself having to clear up SQL servers procedure cache before re-running a query. Sometimes I have  a hard time recollecting the exact order of words. Is it ‘FREEPROCCACHE’ or ‘PROCCACHEFREE’. So this post is for me to refer to, in future.

DBCC FREEPROCCACHE

Helps clear plan cache. Each query when its executed, Sql Server creates a plan and stores it in its memory; So next time you run the same query, it reuses the plan instead of recreating. So, clearing proc cache forces plan generation for every query. So, do not use it on production databases (causes performance hit, as SQL has to recompile every single query/procedure before executing).

DBCC DROPCLEANBUFFERS

Sql Server, upon executing a query and returning the results to the user, it stored the data in its buffer pool for a little while. If you rerun the query, Sql Server will be able to quickly return the results from memory rather than going to disk again. This is much faster. But every few minutes, this buffer pool is automatically cleared. But that’s a different topic.

So, if you want to force clean entire buffer pool, you want to run DROPCLEANBUFFERS. First run CHECKPOINT to force all dirty pages to be written to disk and then run DROPCLEANBUFFERS to clean the buffer pool.

Hope this helps,
_Sqltimes