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?
Add a comment
|
2 Answers
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:
The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs... The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory;
answered Jan 25, 2012 at 1:10
-
we're going to conduct a stress testing on the application server which connects to sql server instance that is running in a seperate box. Commented Jan 25, 2012 at 1:26
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.
answered Jan 25, 2012 at 1:25
