Memory utilization at 98%, why not 100%?

Question

I wonder why SQL Server does not use all available memory - 100% when I am quite sure there is a memory bottleneck. For example, one server I administer starts with using 91% of memory and gradually increases up to 98 but not 100. Increase trend is as follows after a restart at 23:00:

1. 58% after restart and reaches 79% throughout the night. I suppose this should be due to overnight back-ups taken.
2. 82% by 11:00 next day.
3. 90% by 12:00
4. A steady trend throughout the day until 23:00 when memory utilization increases to 93%
5. 95% next morning at 08:00
6. 97% at 17:00
7. 98% next day at 14:00 and stays there.

What might be causing that 2% not being utilized? Is it possible that Windows O/S is preventing it? If so, how can I prove that indeed Windows is keeping it to itself?

I am referring to the overall memory usage by the server - i.e the OS. Max memory configuration is already set well beyond the server's current memory. I have a monitoring tool at hand for tracking memory utilization.

Memory utilization by the server is no less nor above 98%. This is a dedicated, single-instance, two database SQL Server on which no other services, applications etc. are running. SQL Server might be using most of the allocated memory -i.e. 98%- but my question is why is it not using the rest 2%? I'm not sure if I am becoming too peevish in questioning that 2%?

Thanks to the query Determining Current Memory Allocation in Monitor memory usage I confirm Memory_usedby_Sqlserver_GB is 60 out of 64 GB total RAM. I was unable to find the column for max server memory and total RAM in the output of sys.dm_os_process_memory DMV (maybe due to SQL Server 2008 R2) but I may confirm it is a 64 GB server and max. server memory is set to 2147483647 MB.

I don't know if my server will utilize any extra memory once they are put in the slots. I am trying to prove if the server needs more memory, (if so) will make use of it and thus make a cause to push for purchase. I will be left in an awkward position if the memory is bought and memory utilization does not increase and stays in its current state - 60 GB. Seeing 60 GB of RAM being utilized after increasing the server's memory from 64 GB to 128 GB will be a waste of money.I want to make sure before the fact if the server will benefit from the new extra RAMs or be indifferent.

Answer 1

_{Community Wiki answer generated from question comments by Shanky and Shawn Melton}

---

Out of 64 GB on the server, SQL Server is using 60 GB and the rest (4 GB) is used by the OS. This is plain and simple. Since you have not set max server memory, it can also use 61 GB and can also trim down to 58 GB. This is managed automatically. I don't see any issue here.

I don't know if my server will utilize any extra memory once they are put in the slots. I am trying to prove if the server needs more memory...

See SQL Server 2008: how much RAM memory should SQL Server use in a 8GB RAM server? The moment you add more RAM and set max server memory, SQL Server will try to utilize it and probably do it. SQL Server is memory hungry it will take memory and cache the objects.

Memory will always be used if you give SQL Server access to it, but that is not going to fix an overall performance issues if the code is what the issue is (why does it want to use so much?) Throwing hardware at a problem is just a band-aid approach to fixing things. If you have 70GB worth of data, SQL Server will at some point try to pull all 70GB into memory as something tries to access it.

Answer 2

Everything requires memory including the OS just like SQL Server. The Operating System can remove memory from SQL Server, unless you happen to have locked pages in memory permissions for SQL Server. However, even then the OS will always win to keep itself running, so if it needs memory it will force SQL Server to give it up.

As well, just because SQL Server pulled up to 98% memory does not necessarily mean it is using it all. You would have to dig into memory usage of SQL Server itself to know what it is doing with it all. It holds it until the OS request it back, forcefully or in a nice way.

I will also note, you should not have max memory set "well beyond the server's current memory" capacity. SQL Server thinking it can grab more memory than there is really is can cause performance issues. Max memory is just the buffer pool limit, this does not include other areas of SQL Server processes that grab memory. So it is best to keep the max memory lower so the OS and other processes for SQL Server have some.

To dig into into performance, use Perfmon, DVMs, your monitoring tool (if it includes SQL Server monitoring).

Specifying Max memory: I base it on the OS but you also have to consider what else is running on the server. Are you running other components of SQL Server (SSIS, SSAS, SSRS)? Do you have any agents for monitoring or security purposes? An example, base value for me with Window Server 2012 R2 is leaving the OS 6GB of RAM and everything else goes to SQL Server.

See Monitor memory usage in the product documentation.