Why we require to clear cache and buffer before to execute stored procedure in SSMS?
Sometimes it requires to clear buffer and cache to measure exact time duration of execution.
SQL Server provides two DBCC commands to clear cache and buffer.
DBCC FREEPROCCACHE :
You can use this DBCC command to clear cache of the Stored Procedure. When you use this command, it’s clear all old query plans and recompiled all your statements. It removes all the elements from the plan cache and force to SQL Server for recompile. It does not clear any execution statistics for compiled stored procedure.
You should use this command carefully because every time its create new plan for the same query so it may decrease query performance. But it is required during performance testing because you cannot measure query execution time without removing plan data from cache.
DBCC DROPCLEANBUFFERS :
Using this DBCC command you can remove all clean buffers from the buffer pool. During performance testing of your stored procedure this DBCC command is very useful to clear all buffer data from the buffer pool.
When you execute a stored procedure in SSMS, it makes sure that all data is not coming from buffer so you can measure the exact time of execution.