Link to home
Start Free TrialLog in
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?
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of litmic

ASKER

Restart the server can help to lower the memory usage?
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.
Hi,
Do you have any triggers or data-intensive operations like Insert, Update, Delete (with Indexes)?
Avatar of litmic

ASKER

Not sure, how to check it?
Please rephrase your question, cause high memory consumption of SQL Server is not a performance issue.
Hi,

If you're not in control of the server, ask someone that is, if possible.
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.
Thanks Scott for correcting my statement..

litmic,

A feedback would be much appreciated from your end..