SQL Serer 2016 Memory 99%

memory-99.JPG  memory-99.JPGHi ,

This is SQL Server 2016 on Windows Server 2012 R2. The total memory in this server 128 GB
this is standalone

This is allways showing Memory 99%. No acitivy is running on SQL Server. Pleae find the attachment.
VijayAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Thank you, Jackie, but I couldn't see in that document that says that OS will never face memory leaking problems when not defining max server memory. That's the issues I've found before.
The following is the rule I'm following and never got issues with that:
Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for the OS and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to SQL Server.
0
 
Jackie ManCommented:
It is normal.

A SQL server will try to use up all physical memory unless you change the settings.

"If this is a SQL box, you may need to change the default memory settings.  By default, MS sets SQL's max memory to 2147483647 MB, which is their equivalent of infinity."

Source: https://community.spiceworks.com/topic/228351-99-ram-in-use-total-of-processes-doesn-t-add-up-though
2
 
VijayAuthor Commented:
The current setting , These are the current settings, you want me to set Default?

Capture1.JPG
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Jackie ManCommented:
Print the whole screen for the screen capture above.
0
 
VijayAuthor Commented:
Please find the screenshot.Capture2.JPG
0
 
Jackie ManCommented:
"With 64 GB of physical memory I would configure the Max Server Memory setting with around 56 GB, so that the OS can consume and work with the remaining 8 GB."

Source: http://www.sqlpassion.at/archive/2015/02/23/the-3-configuration-options-that-you-always-have-to-change-in-sql-server/

So, your Max Server Memory shall be 117965 (in MB) (i.e. 115.2GB - 10% of all physical memory) whereas the Min Server Memory shall be 0 (in MB).

Restart server to take effect.
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
When you set Min and Max server memories for the same size, it won't let SQL Server engine to dynamically release memory.
You didn't say how much RAM the machine has but being a 64bit system I would let between 8GB to 16GB for the Operating System and will set the Max server memory with the remaining memory.
If you don't have any other SQL Server instance or application installed in that machine then you can set the Min server memory to 0 (zero). Otherwise set it to value that allows the SQL Server instance to work properly without performance issues.
1
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
As Vitor stated, setting min and max memory for SQL Server to ~125GB  is not going to work well.

If there is no reason to do different (that is: you know more about how much memory should be used from start, and how much should be left for OS and other applications & services), you keep the default of minimum 0 and maximum "infinity". MSSQL will then dynamically manage memory consumption as it sees need to do. As long as processes have not to compete for memory all the time, that is the best way to set up MSSQL.
1
 
VijayAuthor Commented:
The Victor,

This machine has total 128 GB of RAM.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Vijay, is the machine dedicated to SQL Server? And has only a single instance?
If affirmative, then try this setting:
Min server memory: 64GB
Max server memory: 112GB
If you see that there are performance issues, increase the Max server memory to 120GB.
0
 
VijayAuthor Commented:
Yes. victor. This is dedicated to SQL Server. No other applications are running.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then follow my recommendation above.
0
 
VijayAuthor Commented:
What is the difference between leaving default memory options Like (Min 0 and MAX 2147483647) and setting min and max custom?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
See my previous comment ...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you leave the default it means that if needed, SQL Server can take all available memory for itself, even memory that might be needed by the OS.
A way to control SQL Server's memory usage is by limiting the memory that a SQL Server instance can use.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Vitor, that is not correct. MSSQL is able to communicate with the OS about memory needs, and leaves enough spare to allow the OS and other apps to use temporary memory. Messing with the settings without advanced knowledge often is counterproductive.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Qlemo, do you have any official document that states that?
My comments were made based on my own experience. I've already saw many bad things for not limiting the max server memory and for dedicated SQL Server machines.
0
 
Jackie ManCommented:
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You can only prove failure, not success ;-).
If there are issues, you can start to apppy changes. There might be conditions the automatic memory allocation process works less than optimal - because of bugs, or a more complex setup.
But if it is just about the numbers, it is better to not mess with the settings.

Inthis particular case min and max memory were set to the max, or even more than that. We do not inow if that allocation has any use. If you do not set limits, you can monitor memory consumption more easily than with other means. If the memory is used up to 50% only over a reasonable period of time, setting the min to 90% or so doesn't improve anything.
0
 
Jackie ManCommented:
"When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If less memory is free, SQL Server releases memory to the OS. If more memory is free, SQL Server may allocate more memory. SQL Server adds memory only when its workload requires more memory; a server at rest does not increase the size of its virtual address space."

Change to Fixed memory if memory management is out of order. Use default setting on initial setup.
0
 
Jackie ManCommented:
@Vitor

I post it as you ask for the link.

I seldom look up a book or a link when I do troubleshooting. Just use common sense.

Your concept makes sense to me but I am just a beginner in every topic.
1
 
VijayAuthor Commented:
Good information.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.