Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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;

share|improve this answer
    
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. –  ronald_yoh Jan 25 '12 at 1:26
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.