litmic
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
First of all: This is NOT a performance issue.
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.
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)?
Do you have any triggers or data-intensive operations like Insert, Update, Delete (with Indexes)?
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.
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:
Ste5an's comment is much more accurate (emphasis added):
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.
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..
litmic,
A feedback would be much appreciated from your end..
ASKER