DBCC Cleaning CACHE – Memory

DBCC Cleaning CACHE – Memory

I wanted to write this blog for quite some time, here you go, as you know at sql everything is on memory, memory information retrieve quickly compare to Disk. So if things are not in memory processor has to fetch that information from disk into memory, as disk is quite slower than memory its time consuming. Ok I am not going in deep on this.

So, if we want to free up the CACHE due to some reason following are the DBCC commands…
(Never run this command on production(run with your own performance risk)

This DBCC commands are just to let everyone know what they are briefly.

  1. DBCC DROPCLEANBUFFERS Removes all clean buffers from the buffer pool … run checkpoint and run this command. clean memory (as good as sql restart).
  2. DBCC FREESYSTEMCACHE
    –Clears all the entries from system cache(
    Releases all unused cache entries from all caches).

    http://ss64.com/sql/dbcc_free.html

3.  DBCC FREEPROCCACHE – Clear Procedure cache, so that next time SP runs with re-compile.

4. DBCC FREESESSIONCACHEClear distributed queries, queries which uses OLEDB –can be connect to other RDBMS as well – Flush the distributed query connection cache.
http://msdn.microsoft.com/en-us/library/ms188721.aspx

http://msdn.microsoft.com/en-us/library/ms187781.aspx

5. DBCC FLUSHPROCINDB(<db_id>); –procedure cache for particular database similar to DBCC FREEPROCCACHE.
if Auto_Close option is set, cache of that database will free automatically.

http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/57/clearing-sql-server-data-plan-cache

http://sqlblog.com/blogs/kalen_delaney/archive/2008/10/31/clearing-a-single-plan-from-cache-in-sql-server-2008.aspx

*Never run this command on production(run with your own performance risk)

List of DBCC Commands:

http://www.extremeexperts.com/sql/articles/ListDBCCCommands.aspx

http://sqlcodebank.blogspot.com/2011/10/useful-sql-server-dbcc-commands.html

About these ads
This entry was posted in Memory, Performance Tuning, Troubleshooting and tagged , , , , . Bookmark the permalink.

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