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)
pinaldave
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.
Be VERY VERY careful when you run these commands in production environment – and make sure you know what you are doing.
Hi,
So when should we use these commands or when we should not? Will you explain me?
Thanks
Shailesh
[...] Read my previous article about SQL SERVER – Clear SQL Server Memory Caches. [...]