19

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 SQL Server not releasing Memory and 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...

6
  • 2
    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... Feb 1, 2010 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? Feb 1, 2010 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, 2010 at 9:51
  • 2
    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? Feb 1, 2010 at 14:42
  • 2
    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, 2013 at 18:57

5 Answers 5

12

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.

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

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.)

8
  • 1
    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, 2010 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, 2010 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, 2010 at 21:48
  • Yeah - I was hoping that I wouldn't have to do something like that.
    – Rob Farley
    Feb 2, 2010 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, 2012 at 20:15
2

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.

2
  • 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, 2010 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. Feb 2, 2010 at 1:21
2

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.

1
  • 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, 2010 at 22:14
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .