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…
Best Answer
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.