During a performance tuning of a stored procedure, I had to clear the cache and buffer of the stored procedure. Clearing the cache & buffer will make the stored procedure to recompile. This recompiled stored procedure will help us to get the stored procedure’s exact execution time along with the recompilation time. This is very important for stored procedure performance testing and tuning.
Amature Way Of Clearing The Cache & Buffer
- The amature way of clearing the cache and the buffer is to drop the stored procedure and recreate it. Recreating the stored procedure will clear and buffer of only the particular stored procedure.
- Another way is to shut down the SQL Server and restarting it, restarting the SQL Server clears the cache and buffer of all the objects.
Professional Way Of Clearing The Cache & Buffer
The professional, and the ideal way to clear the cache and buffer of the stored procedure is by using the SQL Server database console comments FREEPROCCACHE and DROPCLEANBUFFERS. Using FREEPROCCACHE and DROPCLEANBUFFERS avoids the hassle of restarting the server and recreating the object.
Example
Execute the below statements to flush the cache and buffer of stored procedures in SQL Server.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Sample command execution:
So cache and buffer have their own limits on memory.. And so we can’t depend on cache cause it’s using memory not performance at all, right?
thx Beaulin Twinkle, got it. :))