This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Why do we require to clear the cache and buffer before executing a stored procedure in SQL Server?
Sometimes it needs to clear buffer and cache for measuring the exact time duration between the executions.
SQL Server provides two DBCC commands to clear the cache and buffer.
DBCC FREEPROCCACHE :
You can use above DBCC command to clear the cache of the Stored Procedure. When you use this command, it clears all old query plans and recompiles all your statements.
It removes all the elements from the plan cache and forces SQL Server for a recompile. It does not clear any execution statistics for the compiled stored procedure.
You should use this command carefully because every time it creates a new plan for the same query so it may decrease your query performance. But it is required during performance testing because you cannot measure query execution time without removing plan data from the cache.
DBCC DROPCLEANBUFFERS :
Using the above DBCC command, you can remove all clean buffers from the buffer pool. During performance testing of your stored procedure, this DBCC command is handy to clear all buffer data from the buffer pool.
It also makes sure that all data are not coming from the buffer and fetching from the physical data pages so that you can measure the real performance between the executions.