Avatar of Vijay
Vijay asked on

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.
SQLWindows OSMicrosoft SQL Server

Avatar of undefined
Last Comment
Vijay

8/22/2022 - Mon
Jackie Man

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
ASKER
Vijay

The current setting , These are the current settings, you want me to set Default?

Capture1.JPG
Jackie Man

Print the whole screen for the screen capture above.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Vijay

Please find the screenshot.Capture2.JPG
Jackie Man

"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.
Vitor Montalvão

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Qlemo

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.
ASKER
Vijay

The Victor,

This machine has total 128 GB of RAM.
Vitor Montalvão

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Vijay

Yes. victor. This is dedicated to SQL Server. No other applications are running.
Vitor Montalvão

Then follow my recommendation above.
ASKER
Vijay

What is the difference between leaving default memory options Like (Min 0 and MAX 2147483647) and setting min and max custom?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Qlemo

See my previous comment ...
Vitor Montalvão

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.
Qlemo

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Vitor Montalvão

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.
Jackie Man

Qlemo

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jackie Man

"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.
ASKER CERTIFIED SOLUTION
Vitor Montalvão

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jackie Man

@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.
ASKER
Vijay

Good information.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck