How to clear SQL Server Stored Procedure Cache and Data Buffer?

Question

I am working on a stored procedure optimization and the issue I am facing is, only the first time execution I am seeing the correct execution time. For every calls after that, the execution time is not correct because the stored procedure is already compiled and cached. Is there a way to clear the cache and make the stored procedure to recompile so that I can optimize it properly.

Answer 1

To Clear the Stored procedure cache use the below Query. By doing this the procedure will be recompiled rather than using it from the cache.

DBCC FREEPROCCACHE

To Clear the Data cache use the below query. This will clear all the data which was preserved in the buffer during the previous execution.

DBCC DROPCLEANBUFFERS