Thought of compiling all the useful DBCC Commands for DBA related to Memory Management within SQL Server. Well, I’ve seen many DBA’s unaware of the below very useful commands staying under assumption, CHECKDB is the only DBCC Command we’ve in SQL Server.
Okay! Let’s see what do SQL Server offer to DBA’s to do a diagnosis of SQL Server Memory.
DBCC DROPCLEANBUFFERS : This Command will remove all the Data Pages from the Buffer Cache. Basically, you can issue this if you want to clear your Buffer Cache.
DBCC FREEPROCCACHE: This command will remove all the execution plans from Procedure Cache.
DBCC FREESYSTEMCACHE(‘ALL’) : This command will remove all the Unused entries from all the available caches. It includes Plan cache as well!
DBCC FREESESSIONCACHE : This command will Flush the Distributed Query Connection(Linked Servers) Cache on SQL Instance.
DBCC FLUSHPROCINDB(DB_ID) : This is a pretty useful command which clears Query Plans related to a given Database.
Note: This is an Undocumented Command.
Hope this is useful! Be very cautious and understand what you are doing before issuing these Commands blindly on your Production SQL Instances to resolve an issue, especially on an Instance which is shared by Multiple applications with many Databases.
MSSQLTREK 