How do I get SQL Server to release its memory?
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...
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.
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.)