I have a SQL Server 2008 R2 instance running on a production system. I'm not a DBA; I'm more a developer, but my client is currently asking me: How important is it to release memory from SQL Server back to the server? Would it impact the performance of subsequent queries to the instance if we released memory back to the OS?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
How exactly would you 'release' the memory back?
As a general rule SQL Server will steal all the memory from the OS and then use it for it's own purpose. See Dynamic Memory Management:
Why do you think you need to release memory back to the server? In most cases you should be allocating as much memory as you can to SQL Server, while leaving a decent buffer for the operating system and any other programs that may require memory on the server. In an ideal scenario, other programs are kept to a minimum or eliminated altogether. SQL Server will consume the memory it needs gradually, up to the max server memory setting (though prior to SQL Server 2012 this only affects buffer pool allocations), and will not give memory back unless the OS demands it. Having SQL Server play tug of war with the OS over memory is not going to be a good scenario.