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. Let us learn how to clean cache.

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

I hope you find this blog post about Clean Cache and Clean Buffer helpful.

If you have any questions, you can reach out to me via Twitter.

Here are a few of my recent videos which you may find interesting.

Arrow in Execution Plan – SQL in Sixty Seconds #136

Do you know what a single arrow means in an execution plan? While we popularly call it the arrow, there is a technical name for these little arrows.

SSMS Efficiency – Replace STAR – SQL in Sixty Seconds #130

You might have heard that SELECT * is bad for query performance. Let us learn in today’s video how we can replace STAR (*) by column name.

Cycle Clipboard Ring – SQL in Sixty Seconds #129

Copy (CTRL + C) and Paste (CTRL + V) are the friends of the DBAs and Developers. In SQL Server it is totally possible to use the Cycle Clipboard Ring.

Split Screen for SSMS Efficiency – SQL in Sixty Seconds #120

I often struggle to remember the changes done to the stored procedure and in this situation, the Split Screen of SSMS really comes in very handily.

Full-Screen Execution Plan – SQL in Sixty Seconds #117

There are two primary tools – 1) Execution Plan and 2) Statistics IO. In this blog post, we will see how we can get the full-screen execution plan.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Server DBCC, SQL Server Security, SQL Stored Procedure, SQL Utility
Previous Post
SQL SERVER – Fix: Error Msg 128 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Next Post
SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

Related Posts

24 Comments. Leave new

  • rajendra dave
    March 25, 2007 1:25 am

    extremely helpful in understanding and solving the problems. Thanks for your utmost skill in this field.

    Reply
  • 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.

    Reply
  • andre.

    if you have tables which dont get queried, will it not be better if you do not have any index on those tables?

    Reply
  • How do we use the command? in the SQL or query? in the php page itself? im confused!

    Reply
  • 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 ?

    Reply
  • Sarabjeet Singh
    January 22, 2010 12:00 am

    You are Great!!!!!!!!!!!Bhaji.I am your Great Great Fan…

    Reply
  • Would you not be required to call the CHECKPOINT command before calling DROPCLEANBUFFERS?

    Reply
  • 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

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

    Thanks
    Prashant

    Reply
  • Whildher Lizarazo
    March 17, 2011 7:02 pm

    SRI:

    Can you resolve your problem with slow sql server ?

    Reply
  • GrantAndre
    May 4, 2011 1:55 am

    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…

    Reply
  • 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 ?

    Reply
  • Is there any solution to clear data from cache for specific databse ?

    Reply
  • Daniel Berber
    June 23, 2011 4:34 am

    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.

    Reply
  • Is this a database level change or instance level change?

    Reply
  • 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.

    Reply
  • 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?

    Reply
  • 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?

    Reply
  • Hi Pinal,
    When you get a chance, could you please explain the term “Cold buffer Cache” in detail.
    Thanks.

    Reply
  • 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

    Reply

Leave a Reply

Menu
Exit mobile version