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 FREESESSIONCACHE –Clear 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