43

SQL Server is consuming 87.5 % of my server RAM. This recently caused a lot of performance bottlenecks such as slowness. I researched this issue. One common solution I could find on the internet is to set the maximum limit for SQL Server. This was done and much improvement is gained. I want to know why if the maximum memory value is not set why SQL Server keeps consuming the resources

Improve this question
2
  • 5
    How have you determined that SQL Server's use of RAM is the source of any bottleneck? Slowness in what? If anything, SQL Server should be more efficient if it using as much RAM as possible (or even more than you expect or want it to). And if you have other applications on the server that are suffering because SQL Server is using memory (as it's designed to do), move them to another server.
    – Aaron Bertrand
    Commented Aug 1, 2013 at 21:06
  • I have used the method here lucidica.com/help/got-sql-eating-up-ram Let me know if that helped :) Kind regards, Nikita.
    – Nikita Kosousov
    Commented Jan 26, 2015 at 12:45

5 Answers 5

Reset to default
57

SQL Server will consume as much memory as you will allow it. By default, that number would encompass 100% of your numerical memory on your machine. That's why you're seeing what you're seeing. If you give SQL Server 24 GB of memory, then SQL Server will do its best to use 24 GB of memory. Then you have SQL Server and the OS battling for resources, and it'll always result in poor performance.

When you set the max server memory configuration limit, you are limiting how much SQL Server can allocate for the buffer pool (virtually where it stores data pages and the procedure cache). There are other memory clerks within SQL Server, so for your particular version (2008 R2 and below), max server memory just controls the buffer pool. But this is always going to be the biggest memory consumer.

TechNet Reference on the Effects of min and max server memory

The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine.

As far as the question how much memory should I leave for the OS, that is a commonly debated metric. It really all depends, mostly on what other processes are running on the server. Provided it's a truly dedicated server (and that's almost never the case). I like to see at least 4 GB memory for the OS (and even more on big-hardware boxes). But most importantly, monitor how much unused memory there is. Be liberal, and give SQL Server more memory if you're noticing a lot of available (can be monitored through perfmon) and unused memory (of course, always leaving a small buffer for those corner situations).

Improve this answer
14

SQL Server is designed to use all the memory on the server by default. The reason for this is that SQL Server cache the data in the database in RAM so that it can access the data faster than it could if it needed to read the data from the disk every time a user needed it. If we needed to go to the disks every time that SQL Server needed the same data over and over the disk IO requirements would be insanely high.

Normally I recommend people leave about 4 Gigs of RAM for Windows, but that really depends on the amount of software running on the server, what components of SQL Server you are using (not all the components follow that setting), etc.

Improve this answer
9

This works instantly without the need for SQL restart:

Server Memory Server Configuration Options

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. By default, SQL Server can change its memory requirements dynamically based on available system resources.

Procedure for configuring a fixed amount of memory To set a fixed amount of memory:

  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.

Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB).

I hope this helps!

Improve this answer
3

As Eric said, you have to manage the memory in order to keep free space for other processes. For a general explanation and some guidance on how to set the maximum memory, check the post by Jonathan Kehayias:

http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

You can modify the recommendation taking into account your server usage.

Improve this answer
2

SQL Server will utilize available RAM unless you put a cap on the usage. As a rule I try to leave no less than 2GB free for the OS, i.e. if you have 64 GB physical RAM in the host, cap SQL Server @ 62 GB.

http://msdn.microsoft.com/en-us/library/ms191144(v=sql.105).aspx

Improve this answer

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