SQL Server retains memory caches to support later work. I investigate memory pressure before trying to release them.
I’ve seen people restart SQL Server to clear its memory caches. Retained memory isn’t by itself evidence of a leak. Cached data and plans can help subsequent requests.
The original commands target different caches
-- Administrator reference only. Do not run on a shared instance for this article.
DBCC FREESYSTEMCACHE ('ALL');
DBCC FREESESSIONCACHE;
DBCC FREEPROCCACHE;FREESYSTEMCACHE releases supported unused cache entries. FREESESSIONCACHE clears the distributed-query connection cache. FREEPROCCACHE evicts execution plans. These are not interchangeable commands to return all SQL Server memory to Windows.
Plan eviction adds recompilation work and affects unrelated sessions. I first identify the memory consumer and inspect the configuration. Then I measure the effect of a deliberate change.
Investigate before intervening
Check the operating system’s available memory, SQL Server memory configuration, and relevant memory consumers. Restarting or clearing every cache can erase useful evidence and temporarily change the symptom.
Reference: DBCC FREESYSTEMCACHE.
A cache command is not a memory diagnosis, it is an intervention with a particular scope.
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.
13 Comments. Leave new
can we use dbcc FREESYSTEMCACHE
dbcc FREESESSIONCACHE
dbcc FREEPROCCACHE
commands can be used in production server
Hi Pinal,
What are the parameters that can be passed to along with ‘FREESYSTEMCACHE’.??
When I simply run the query ‘DBCC FREESYSTEMCACHE’, I got the following error message
Msg 2583, Level 16, State 3, Line 1
An incorrect number of parameters was given to the DBCC statement.
Regards ,
Biju.K.S
DBCC FREESYSTEMCACHE (‘ALL’) WITH MARK_IN_USE_FOR_REMOVAL;
paste this as it is and it will clean up the buffers
— Clean all the caches with entries specific to the resource pool named “default”.
DBCC FREESYSTEMCACHE (‘ALL’,’default’)
1). May i run this query on every page???
2). This will affect the performance????
Hello Pinal:
The above commands are working in SQL Server 2008 R2 only, they are not in 2012.
Are there any commands which will work in SQL Server 2012, i searched in net but i didn’t find any.
hi Dave, I run these command at sql server 2008 (not R2)., but memory didnt release at all. Any idea?
Hi Dave,
I have executed above commands in Microsoft SQL Server Standard Edition (64-bit) 2008 R2 database, but memory didn’t release.
I create a COM object with sp_OACreate. Then I use the sp_OAMethod to call a load method of the underlying DLL. Finally I use sp_OADestroy to dispose the COM object. The procedure works perfectly fine the first round.
During the second round of execution, sp_OACreate succeeds. But sp_OAMethod returns insufficient memory error. I was under the impression that sp_OADestroy would have released the memory, but that doesn’t seem to be the reality.
I tried the following commands after the first round of execution
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE (‘ALL’)
But none of them (nor all of them together) help.
The only way out is to restart the SQL Service after each round of execution.
Is there a way around this?
Perhaps give the OS more memory and take away a little from SQL…
How to do this???? How to release the RAM memory from SQL server??
Hi Swami,
Open your MSSMS -> Open Object Explorer -> Right-Click on the Server Name -> Select “Memory” page -> Reduce Maximum Server memory (in MB) value to a desired value. You can first check how much of OS RAM you have on your Server’s Computer Properties. If your OS RAM is 16GB or bigger the difference between your OS and SQL memory should be at least 4G.
Thanks,
Sammy Machethe
Thanks Sammy for your contribution.