Avatar of litmic
litmicFlag for Hong Kong asked on

Ms SQL using high memory

A Microsoft SQL server with 64GB ram running around 95% ram utility, how to determine the exact cause of the performance issue?
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Raja Jegan R

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

Restart the server can help to lower the memory usage?
ste5an

First of all: This is NOT a performance issue.

Restart the server can help to lower the memory usage?
No. It will only free the used space.

SQL Server will request memory when need until no more memory is available or the max. memory setting is hit. Once requested the memory is kept allocated for buffering data. Cause the simple consideration is: This is our current workload, it makes no sense to drop data from memory, cause reading from disk is soo slow.

So doing this, will result in so called cold buffers, thus your applications get slower.

Thus the question is: What is your actual concern?

Just set the SQL Server max. memory setting to (server memory - 4 GB). This is in most scenarios sufficient. You should only use a lower max. memory setting, when you run further services on the host server, which is in many scenarios not recommended.
Eduard Ghergu

Hi,
Do you have any triggers or data-intensive operations like Insert, Update, Delete (with Indexes)?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
litmic

Not sure, how to check it?
ste5an

Please rephrase your question, cause high memory consumption of SQL Server is not a performance issue.
Eduard Ghergu

Hi,

If you're not in control of the server, ask someone that is, if possible.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

With 64GB of RAM, the absolute max you should assign as max memory for SQL is 56GB.  You need to make sure you don't force memory to be taken back from SQL to go to Windows / other processes, because that is a very serious performance issue.  If you start memory "thrashing", performance will be truly awful.  [The official MS recommendation is not to allocate more than 54GB max memory, so even 56GB is a bit of a push.]

Keep in mind that CLR, as just one example, needs RAM outside the SQL memory space.  Windows needs RAM to manage the OS itself and the RAM.  And so on.

Also, note that this statement is false:
SQL Server by design/default will reserve all memory available in the Server

Ste5an's comment is much more accurate (emphasis added):
SQL Server will request memory when need[ed] until no more memory is available or the max. memory setting is hit.

In other words, SQL does not automatically grab memory just because it's there.  It will acquire more memory, especially aggressively for buffers, when it needs more RAM.
Raja Jegan R

Thanks Scott for correcting my statement..

litmic,

A feedback would be much appreciated from your end..