We help IT Professionals succeed at work.

Ms SQL using high memory

48 Views
Last Modified: 2020-05-12
A Microsoft SQL server with 64GB ram running around 95% ram utility, how to determine the exact cause of the performance issue?
Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Restart the server can help to lower the memory usage?
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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 GherguArchitect - Coder - Mentor
CERTIFIED EXPERT

Commented:
Hi,
Do you have any triggers or data-intensive operations like Insert, Update, Delete (with Indexes)?

Author

Commented:
Not sure, how to check it?
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Please rephrase your question, cause high memory consumption of SQL Server is not a performance issue.
Eduard GherguArchitect - Coder - Mentor
CERTIFIED EXPERT

Commented:
Hi,

If you're not in control of the server, ask someone that is, if possible.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Thanks Scott for correcting my statement..

litmic,

A feedback would be much appreciated from your end..
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.