SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL , BOL

About these ads

22 thoughts on “SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer

  1. Good info! I wonder though if it is possible to specify for an index/table to not cache? or alternatively remove from cache a certain object?
    We have a log table which does not get queried at all, but it appears that primary key index of this table takes 56% of Buffer Cache.

    Thanks.

  2. I have a stored procedure that calls another stored procedure in turn. The inner procedure returns an integer value using a SELECT SCOPE_IDENTITY().

    The outer procedure also returns an integer value using SELECT SCOPE_IDENTITY().

    Obviously the client is going to receive multiple result sets. Is there a way to “clear” the earlier resultsets and send only the last result set to the client ?

  3. hi

    We developed system using .net frame work 2 with Sql server 2005. one location using 10 to 20 user we are not facing any kind of problem.

    ONE OF MY CLIENT USE 100+ users

    WINDOWS 2008 SERVER – 64 BIT + SQL SERVER 2005 64 BIt with 8 gb ram

    but when 100+ user connecting to our server first 30 mins running system smooth and after that system getting slow, and approx after one hour client systems getting error :

    TCP Provider, error: 0 – The specified network name is no longer available

    again we have to restart the sql server or windows, then again its working fine..

    please suggest me how to over come this and what could be the reason.

    ONE MORE :

    when 100 user connected to server, if any user using bulk transfers, memory goes up, after that it wont come down, just i want to know, there is any posibilities clearing the memory without disturbing my users work ?

    Your immediate reply would be highly appreciate

  4. Can somebody explain in detail the need of executing
    the below commands.
    DBCC FREEPROCCACHE,DBCC DROPCLEANBUFFERS
    I am still not very clear.

    Thanks
    Prashant

  5. SRI – just a thought, but have you checked on the licensing for that SQL server? Maybe you’re getting slowed performance with 90-100 users due to load… Then getting the TCP provider error when user 101 attempts to connect if the SQL 2005 install is licensed by CAL rather than PROC. Not confident of this post, but a thought that might help…

  6. If we are clearing procedure cache do we need to recompile all the procedures again or the sql server will automatically compile when a particular procedure runs ?

  7. wincek, you can use DBCC FLUSHPROCINDB(db_id), this will remove all elements from the plan cache on this DB only and not the whole Instance. It’s usually not recommended to run DBCC FREEPROCCACHE or DBCC FLUSHPROCINDB in a PRO Environment.

  8. Please excuse an Oracle DBA invading this terrain but like most sites today, we are mixed platform and I have to know my way round SQLServer as a beta skill set (and am a regular and grateful reader of Pinal Dave’s very helpful website).

    On SQLServer (SQLServer 2008 R2 enterprise), we have found that when a procedure is changed and recompiled, the old logic appears to run for some time (maybe 30 mins?) leaving the programmer doubting his sanity and tearing his hair out!!. This doesnt happen on Oracle but as a DBA I somethimes have to flush the library cache for other reasons. This lead me to research the same technique in SQLServer to force starting with a “clean sheet” when compiling SPs. Executing DBCC FREEPROCCACHE was exactly what was needed to deal with this problem. We will have to run this in a PROD environment when sp’s are upgraded for reasons above – executing the old SP could be majorly inconvenient to disastrous. On Oracle, a performace glitch and a few ropey stats may be expected until the cache refills. There is a beneficial side effect of freeing trapped space in the proc cache when this is done – I guess its the same in SQLServer?. In Oracle the proc is loaded to the cache when called but can be pinned so is permanently resident. Pinned procs are reloaded by the RDBMS and app procs when called, after a cache flush.

  9. I have one SP which comes with tens of thousands of records and takes nearly 20 minutes to execute. Whenever this SP is executed , memory usage of the server is drastically increasing. The server hardware is not the issue in this case (I guess). Each time I have to Restart the SQL service to clean up cached data in memory.

    I want SQL Server not to cache the o/p records of the SP as they are in tens of thousands and degrades the perfomance for other SPs.

    I can not use DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS for this case as its production server.

    Can you provide some guidance on how to Clean caching data of the specific SP?

  10. I have one SP which comes with tens of thousands of records and takes nearly 20 minutes to execute. Whenever this SP is executed , memory usage of the server is drastically increasing. The server hardware is not the issue in this case (I guess). Each time I have to Restart the SQL service to clean up cached data in memory.

    I want SQL Server not to cache the o/p records of the SP as they are in tens of thousands and degrades the perfomance for other SPs.

    I can not use DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS for this case as its production server.

    Can you provide some guidance on how to Clean caching data of the specific SP?

  11. Hi Pinal,
    Your blogs have been very useful for me. I have an Oracle background and have had to do some serious SQL Server tuning off late. We have a situation where a couple of procedures get executed frequently in SQL Server. After a lot of tuning by setting the statistics io on, timing on etc and improving the problem queries, we are getting expected results in acceptable time for the first few executions. Then the procedures start slowing down. We do a DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE and it is fast again. Would this because the buffers get full? How can we inspect what is there in the buffers/how full is it etc?
    Thanks,
    Jayadevan

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s