As SQL Server does automatic Memory management , it means that SQL Server will ask for memory from the OS as and when it requires, and will keep it as long as it can. This is intended and a default behavior of SQL Server. The memory component (Buffer Pool) does this. So, when does SQL Server release memory back to the OS? It releases memory back to the OS when any other process will ask for memory from the OS. Despite this automatic memory management, there are 2 commands that can be used to free up memory in SQL Server.
DBCC FREEPROCCACHE :- To flush out execution plans from the procedure cache &
DBCC DROPCLEANBUFFERS :- To flush out data pages from the memory.
DBCC FREEPROCCACHE :- To flush out execution plans from the procedure cache &
DBCC DROPCLEANBUFFERS :- To flush out data pages from the memory.
Hi Arun,
ReplyDeleteWhen we reindex the DB. The Task Manager of windows 2008 R2 say that used memory is 85%(before 20%). But the used memory does'n decrease after the reidexing finish.
I run your command(DBCC DROPCLEANBUFFERS & DBCC FREEPROCCACHE) but there are no effect.
Can you help me to release RAM in this case.
Thanks,
Hai Mai.
Hi Hai Mai,
ReplyDeleteRestart SQL Server Service. It will release unused memory.
Are there a better way, Arun?
ReplyDeleteWe can restart SQL server service on PRODUCT environment.