On my dev box I have my SQL Server configured with a high maximum amount of memory (8GB of 16GB). This is required for many development tasks.
Sometimes I want my memory back, though. I don't want to leave 8GB consumed permanently.
What's a good way to get SQL Server to suddenly release all it's memory to the operating system?
DBCC DROPCLEANBUFFERS does not do that. It merely marks clean buffers as available.
Restarting SQL Server is a problem because this causes a 1 minute database recovery to be run due to this bug/feature: Stopping the SQL Server service causes databases to not shut down cleanly (please vote for that connect item; seems like an egregious bug).
Best Answer
I routinely increase and decrease the max server memory configuration option for the SQL Server 2005-2016 instances running on my laptop (with lock pages in memory enabled). Has never caused me any problems, and memory always seems to be released quickly. I also stop, start, and restart instances as I find I need them (using SQL Server Configuration Manager, which I keep open).
As far as checkpoint is concerned, how you configure that depends on version. Manual checkpoint with a duration target has been available since 2005 (though it is undeniably more effective in later versions).
Running a manual checkpoint
<duration>per database before shutdown will help spread the I/O load and reduce recovery time on startup. Managed checkpointing will also reduce I/O needed when a memory shrink is requested because there will be fewer dirty pages to write to persistent storage. There is no particular benefit to dropping clean buffers before reducing the max memory setting that I have noticed.Not using 'lock pages in memory' may increase the rate at which SQL Server can release memory, but I rely on that to avoid paging SQL Server memory to disk. If you don't need it for that purpose, you might choose to run without it.