patternsqlCritical
Why is SQL Server consuming more server memory?
Viewed 0 times
whyconsumingsqlmorememoryserver
Problem
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
Solution
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
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).
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).
Context
StackExchange Database Administrators Q#47431, answer score: 57
Revisions (0)
No revisions yet.