Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've heard MS SQL Server takes up as much RAM as it can to cache results. Well, it's not leaving enough bargaining room for our little server's RAM.

How do I change the settings to limit the amount of RAM it can use?

MS SQL Server running on Windows Server 2008.

share|improve this question
    
"Bargaining room"? Most likely, you should just leave things alone. The people who designed these things knew what they were doing, and unless you know something they don't, their decisions are probably best. Remember, any free memory is forever wasted. It's not like if you use half your memory today you can use twice your memory tomorrow. –  David Schwartz May 9 '12 at 22:22
1  
@DavidSchwartz: I think you'll find that a lot of MS SQL DBAs will limit the amount of RAM that the DBE can use. It often has a performance impact on anything else running on the system. –  wfaulk May 9 '12 at 22:46
1  
@wfault: "By default, SQL Server can change its memory requirements dynamically based on available system resources." Many people like to mess with things just for the sake of messing with them, but what they're actually doing is inhibiting the server's ability to tune itself to changing load. If it ain't broke, you can't fix it. This is most commonly done by people who like to see lots of free memory to make themselves feel good, but actually, any memory that's free is also unused -- that is, wasted. –  David Schwartz May 9 '12 at 22:49
2  
@DavidSchwartz: I understand where you're coming from, and in any other situation I would agree with you, but MS SQL doesn't seem to actually optimize this very well, IME. –  wfaulk May 10 '12 at 0:21
    
It actually was broke, as it would grind the server to a halt... –  FlavorScape Mar 26 '13 at 17:07
show 1 more comment

1 Answer 1

up vote 9 down vote accepted

From How to configure memory options using SQL Server Management Studio:

Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server.

  1. In Object Explorer, right-click a server and select Properties.
  2. Click the Memory node.
  3. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.

You can also do it in T-SQL using the following commands (example):

exec sp_configure 'max server memory', 1024
reconfigure
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.