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

0 votes
asked Jul 17, 2014 in SQL Server by Priya Roy (122 points)

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.

Share

1 Answer

+1 vote
answered Jul 18, 2014 by administrator (315 points)
 
Best answer

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

Your answer

Preview

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.
site design / logo / content © 2013 - 2015 pinfaq.com
...