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. With them you can clear SQL Server memory caches without a restart.
When executed following three commands will free up memory for SQL Server by cleaning up its cache.
DBCC FREESYSTEMCACHE DBCC FREESESSIONCACHE DBCC FREEPROCCACHE
A Word of Caution Before You Run These
These commands are handy, but they are not free. When you run DBCC FREEPROCCACHE, every cached execution plan is thrown away. The next time each query runs, SQL Server has to compile it again, and on a busy production server you can see CPU go up for a while. So I treat these commands as a tool for testing and troubleshooting, not as a daily job. If a server needs them every day to stay healthy, the real problem is somewhere else, like missing indexes, badly written queries or a wrong memory setting. Also remember that clearing the caches by hand has the same effect as a restart on those caches. The server has to warm up again, and the first queries after it can be slower than usual.
SELECT cp.plan_handle, st.text FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st WHERE st.text LIKE '%YourQueryText%'; -- Then pass the plan handle you found: -- DBCC FREEPROCCACHE (0x...);
When only one query has a bad plan, there is no need to clear everything. The query above finds the plan handle of that one query. Pass the handle to DBCC FREEPROCCACHE and SQL Server removes just that single plan, while every other plan stays in memory. This is much gentler than a full reset. So before you clear SQL Server memory caches for the whole server, ask yourself one simple question: is the whole server really the problem, or only one query?
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
31 Comments. Leave new
In My Production server My RAM memory shows 73 GB is used. In Proccess sql is taking around 66 GB. Does it always takes 66 gb, how to free memory when it is not using.
I have query regarding buffer catch,after restarting server cache get cleans,It means that dirty page and clean page count gets zero value and DBCC DROPCLEANBUFFERS also does the same process.
But if dirty Pages Count is less than Clean Pages then What is the way to remove only clean pages from memory? and dirty pages should remain in buffer cache so that reading from disk can be minimize.Please suggest suitable solution.
I read about DBCC DROPCLEANBUFFERS. When run, this will remove all clean pages from memory for an entire database.I Just need to know how often this command should be run??as Pages Read/sec and pages write/sec also have high value. Please Suggest.
What exactly you are trying to achieve?
Sorry for confusion,i wanted to ask that if memory in buffercache increases per day then how often Drop clean buffer command should be run??I want to ask one more question regarding formula for memory utilization.the value of total physical available memory gets from sys.dm-os-sys-memory and buffer cache memory from sys.dm-os-memory-clerks(sum(pages-kb)).what else should be considered to get exact memory utilization so that we get exact figure what is available in sys.dm-os-memory(available-physical-memory-kb)