Sqlserver is consuming 87.5 % of my server Ram. This recently causes alot of performance bottleneck such as slowness.I Research on this issue. One common solution i could find on the internet is to set the Maximum limit for Sqlserver. This was done and much improvement is gained.I want to know why if the Maximum Memory value is not set why sqlserver keep consuming the resources
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
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).
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.
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:
You can modify the recommendation taking into account your server usage.
This works instantly without the need for SQL restart:
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:
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!
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.
protected by Community♦ Oct 6 '15 at 16:18
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site.
Would you like to answer one of these unanswered questions instead?