Clear Cache and Buffer of Stored Procedure in SQL Server

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:

DBCC FREEPROCCACHE DROPCLEANBUFFERS Cache and Buffer

1 comment for “Clear Cache and Buffer of Stored Procedure in SQL Server

  1. eko mt
    September 2, 2015 at 10:15 am

    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. :))

Leave your thoughts...