Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I know SQL likes RAM, but releases it when Windows asks. I know this is how SQL is supposed to work. But I have a Windows Administrator who isn't convinced that SQL will actually return the RAM, and on this particular (virtual) server, SQL doesn't need much once Analysis Services is finished, but there's nothing else on this server that's bothering to ask SQL for much.

So I'm trying to reassure the Windows Admins that problems with the virtual environment are not "because SQL is using too much RAM", but I can't seem to persuade SQL to release it without restarting the service.

When processing cubes, the SQL service happily takes 8GB of RAM, but because there's no pressure on it, it doesn't release much of this during a normal day. The Windows guys scream, and it would be much nicer to have SQL release this.

I don't want to use the max memory setting, because I do actually want SQL to use that much RAM when processing. I just want it to go back down again afterwards.

It's potentially a duplicate of both http://serverfault.com/questions/83796/sql-server-not-releasing-memory and http://serverfault.com/questions/1279/reclaiming-memory-from-sql-server, but I'm wondering if there's a different answer. Waiting for Windows to reclaim it isn't going to persuade the Windows guys here. Restarting the service is an option, but I'm really not a fan of that idea.

I want to know how to get Windows to ask for it back...

share|improve this question
    
What the... a sysadmin is complaining about the RAM being used? I get shitty when RAM ISN'T being used, because if it's not being used it's just going to waste... –  Mark Henderson Feb 1 '10 at 5:49
    
I don't get it either, if the machine doesn't need the memory - where's the problem? This is how the operating system and its applications normally work... given the work-load having the idle SQL service keep most memory when nothing else needs it might increase response time and/or performance for the next SQL job anyway? –  Oskar Duveborn Feb 1 '10 at 8:27
    
The Windows admin feels that SQL is stressing the machine out because the process has so much RAM. I know the box isn't under stress from SQL, and can demonstrate it by restarting the SQL service, showing that it's only consuming much RAM when getting the data for the SSAS processing. So I want SQL to release the memory to show the amount of RAM that SQL is using the rest of the day. It's not about getting them to provide more RAM, just getting them to stop blaming SQL for stuff. –  Rob Farley Feb 1 '10 at 9:51
1  
Agree with Farseeker, sysadmin should be happy RAM is being used. If it's not being reclaimed, then the server doesn't need it. Maybe ask the sysadmin to look at page faults to show the system isn't stressed? –  Nick Kavadias Feb 1 '10 at 14:42
1  
If your sysadmin doesn't understand what Page Faults represent and why having memory consumed but no real Page Faults taking place is not a problem, I don't know how to help you. You can't fix stupid. One thing you can do, however, is to use sp_configure to configure the max memory down when you're not using the system, and the configure it back up when you need it. This seems pretty silly, however. sp_configure is generally something you should set once and leave alone. –  Bacon Bits Feb 8 '13 at 18:57

5 Answers 5

up vote 7 down vote accepted

I believe your only option is to restart the SQL Service, or to run an application that consumes lot's of memory to force SQL to release it.

I like @Nick Kavadias's suggestion: monitor the Memory: Page Faults/Sec performance counter to show that SQL has not put the OS's memory under presssure.

share|improve this answer
    
Thanks Mitch... Let me find that 10GB text file for Notepad. –  Rob Farley Feb 1 '10 at 5:32
    
lol!, actually that's not a bad idea! –  Mitch Wheat Feb 1 '10 at 5:33
    
maybe use a 2GB file... –  Mitch Wheat Feb 1 '10 at 5:35

One way to show them that it will do that is to have SQL Server chew up all the memory. Then copy a huge file over the network (large SQL Backup files work great for this). This will cause the system cache to fill, and Windows will begin asking SQL for its memory back. SQL will begin returning memory back to the OS until it reaches your min memory setting.

(Sorry didn't get the entire question via Twitter, more than 140 characters were needed apparently.)

share|improve this answer
    
I'm hoping for some process I can use that will ask SQL for the memory back (after the buffers have been cleaned and dropped), but without having to cripple the box to do it. –  Rob Farley Feb 1 '10 at 8:11
    
I don't think that there is a way to force the Windows memory manager to ask for the memory back, without forcing the issue through another means. You could probably write something which hits the win32 API directly to do so, but as far as I know the only way to trigger Windows to request memory back is to cause it to need more memory for another process. Check with Bob or Paul, they may have more info. –  mrdenny Feb 1 '10 at 21:44
3  
VMware's balloon driver does exactly this - it asks Windows for more and more memory in order to get SQL Server (or any other application) to give up their memory. –  Brent Ozar Feb 1 '10 at 21:48
    
Yeah - I was hoping that I wouldn't have to do something like that. –  Rob Farley Feb 2 '10 at 0:10
    
@BrentOzar, VMware's ballon driver only reclaims memory from the system cache, not actual process memory; it's designed as a way to tell the O.S. "don't use up all your available memory for trivial things", not to bring it down. –  Massimo Mar 14 '12 at 20:15

You mention 'cubes' so it's not clear whether you talking about AS engine or the relational engine. The relational engine can free memory on demand:

DBCC FREESYSTEMCACHE('<cache name>');

Where 'cache name' is taken from sys.dm_os_memory_clerks. The spec of DBCC FREESYSTEMCACHE only mentions governor pools, but many more caches can be evicted actually.

But if all the memory is used by the buffer pool, then evicting the entire buffer pool will result in tremendous performance degradation and huge IO load. Better let SQL handle this itself.

share|improve this answer
1  
Hi Remus, the relational buffer cache is large following the processing of a cube. After that, I really do want the caches cleaned, dropped and the memory returned to Windows. DBCC DROPCLEANBUFFERS and FREESYSTEMCACHE empty out the cache but don't return the memory to Windows, so it's not completely what I want. –  Rob Farley Feb 1 '10 at 9:43
2  
In theory you could fake memory pressure (eg. you could trampoline QueryMemoryResourceNotification msdn.microsoft.com/en-us/library/aa366799%28VS.85%29.aspx) but I would really really really not go there. If SQL doesn't release committed memory is because the OS doesn't need it. The OS doesn't need the memory because SQL is the only application on the host (stackoverflow.com/questions/1401834/…), right? If you run some other application along SQL, you had it coming. –  Remus Rusanu Feb 2 '10 at 1:21

With both the AS and the DS engines you can put a cap on the memory so they stay below a safe threshold; this is recommended on 64 bith machines with lots of RAM and/or multiple instances of SQL. For at least the database engine if you change the memory cap to below what the current usage it will reduce the amount of memory on the fly w/o having to restart the service, not sure about SSAS though.

share|improve this answer
    
Having the cap is ok, but I think I'm going to want to pull the max memory down after processing and then push it back out, when I'd rather clean the buffers, drop them, and ask Windows for some memory pressure. –  Rob Farley Feb 2 '10 at 22:14

The easiest I have found is to go to server memory properties, enter a smaller value, apply, wait a few minutes and adjust the config back up.

We have also found the throttling sql servers memory usage can actually improve the performance of the server as it gives windows more ram for caching, etc

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.