SQL SERVER – Clear SQL Server Memory Caches

If SQL Server is running slow and operations are throwing errors due to lack of memory, it is necessary to look into memory issue. If SQL Server is restarted all the cache memory is automatically cleaned up. In production server it is not possible to restart the server. In this scenario following three commands can be very useful.

When executed following three commands will free up memory for SQL Server by cleaning up its cache.

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

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

About these ads

21 thoughts on “SQL SERVER – Clear SQL Server Memory Caches

  1. You didn’t mention that there could be a performance hit associated with clearing the system cache and procedure cache, as all requests after issuing DBCC FREESYSTEMCACHE will cause a plan recompilation as well as FREEPROCCACHE.
    So people should be aware that executing these commands could cause a temporary decrease in performance.

    If things are running slow and errors are occurring due to lack of memory on a production server – these commands are only short term band-aids and the real root of the problem needs to be found, analyzed, and fixed in order to have a well performing Production server.

  2. Be VERY VERY careful when you run these commands in production environment – and make sure you know what you are doing.

  3. Pingback: SQL SERVER – Refresh Database Using T-SQL Journey to SQL Authority with Pinal Dave

  4. is by executing these statements (written below) whatever our data is in sql will be removed if not then wht’s the query to remove all the date in once exection?
    DBCC FREESYSTEMCACHE
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE

  5. Hi Sarika,

    At first I would like to make clear that there is difference in clearing of “Plan Cache” and “Data Buffer”. Plan Cache only contains the compiled and execution plans of sql statements, sp, function, etc. While all table data is stored in Data Buffer.
    The following commands clean only the plan cache:
    DBCC FREESYSTEMCACHE(All | pool_name)
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE
    DBCC FLUSHPROCINDB( db_id )

    To clear the data buffer use the following command:

    DBCC DROPCLEANBUFFERS

    For more information visit to : http://blog.sqlauthority.com/2007/03/23/sql-server-stored-procedure-clean-cache-and-clean-buffer/

    Regards,
    Pinal Dave

  6. Hi Pinal,

    One of the interviewer asked me about the database refresh.as per my knowledge i thought of backup and restore.

    is iam correct.

    As you said earlier article we can refresh the database by below dbcc commands,is iam right?

    The following commands clean only the plan cache:
    DBCC FREESYSTEMCACHE(All | pool_name)
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE
    DBCC FLUSHPROCINDB( db_id )

    To clear the data buffer use the following command:

    DBCC DROPCLEANBUFFERS

  7. Hello Sir,

    I have an issue with a server memory. SQL server is allocated 32 Gb out of 40 GB memory and sql server is using all the memory which is allocated to it.

    The memory usage is gradually increasing to the max value that is allocated to SQL Server.
    I have tried all ways but still no luck:

    1. Killed all the open transactions.
    2. Cleared the system cache.
    3. Cleared session cache
    4. Cleared procedure cache.
    5. Checked for blocked transactions and killed them.
    6. Ran DBCC opentran and found no open transactions.

    The active connections to the server are as follows:

    DatabaseName NoOfConnections LoginName

    ReportServer 3 NT AUTHORITY\NETWORK SERVICE
    master 21 sa
    OLTP_QA_TEST 4 sa
    KRApplication 1 sa
    KYApplication 4 sa
    DWH_QA_FINAL 1 sa
    OLTP_QA_FINAL 2 sa

    The Buffer info is as follows:

    bpool_commit_target bpool_committed

    I restrted the SQL server service and then memory went down but gradually it started increasing to theax value within one hour..

    Can you please help me out in resolving this issue please.

    • Hey Suman,
      only solution is..
      just go to memory settings and set max value 1024MB and click apply
      thn again go to memory settings set 32GB.. now see the sql server memory utilization.. u got it.. rit..

  8. yes. i have the same problem. SQL server memory keep on increases. i have 20 GB of RAM. at in 1 Hr SQL memory reaches 20 GB. server goes dead slow. still no clue.

    • Hi Friend,

      I have the same issue earlier, we found that SQL server wont release the unused memory once it complete the transactions or backups and this is a know issue. And in our case we found that we have application services run through out the clock and sending the data in xml’s to vendor server which is out of box so here they are occupying my RAM memory and never coming down, these services hit the DB to query in the open orders so obviously its SQL memory caching issue. so we tunned the services in background now the system is stand and calm.

      So please check the any such services or applications which are connecting or using DB to query in continually.

      And one point i need to add here, if you are using Windows server 2008 R2 have issue with the cache memory release please find the below

      http://blogs.msdn.com/b/ntdebugging/archive/2007/11/27/too-much-cache.aspx

      Patch released;

      http://www.microsoft.com/downloads/en/details.aspx?FamilyID=E24ADE0A-5EFE-43C8-B9C3-5D0ECB2F39AF&displaylang=en

      And there is also a SP1 released for the same

      Good luck.

  9. Hi friends,

    I have sql 2008 R264 bit and 20 GB RAM but 19.7% ram is used by sqlservr.exe so any body know how to freez SQL 2008 R2 64 bit. Don’t tel me use AWE to allocate memory because it is not support on 64 bit

  10. Hi
    I have an issue with a server memory. SQL server is allocated 31 Gb out of 32 GB memory and sql server is using all the memory which is allocated to it.

    The memory usage is gradually increasing to the max value that is allocated to SQL Server.
    Any suggestions?

  11. Hello sir
    I am using Sql Server 2008 and want to write a procedure which can update the table as i fire it.
    Suppose there is 100 data so at the time of updation the 100 data will be sequenced by datetime(this is one of the column data type) and when the another 5 data has been inserted and after then i fire that procedure it only sequenced the rest of 5 data no need to touch the data of 100 what i should have to do??

  12. Dear All,

    We are using a DB view in our project. This is the view which will be triggered many times in different reports. Initially data is coming fast but gradually the performance is getting down.

    Can some one help us to improve the performance,

    Thanks in advance.

    Eswar

  13. Hi There,

    Basically you won’t be able to release physical memory of the server by running DBCC FREESESSIONCACHE,DBCC FREEPROCCACHE
    In order to release the memory try to run following two queries:

    1.

    EXEC sys.sp_configure N’show advanced options’, N’1′ RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure N’max server memory (MB)’, N’8000′
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure N’show advanced options’, N’0′ RECONFIGURE WITH OVERRIDE
    GO

    Then run following:

    2.

    EXEC sys.sp_configure N’show advanced options’, N’1′ RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure N’max server memory (MB)’, N’2000′
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure N’show advanced options’, N’0′ RECONFIGURE WITH OVERRIDE
    GO

  14. hi pinal dave
    one production server is there in that server total ram size is 32 gb.on that server one database occupy 31gb.what abt remaining databases. how to solve the performance issue

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