High memory utilization
-
Hi,
In one of our server, SQL server is consuming most of the memory(seen from task manager). Apart from setting the memory limit, is there any way we can reduce memory utilization of SQL server? I believe Setting memory limit may degrade performance as SQL server will have to manage with less memory. How to deal with the situation?
Client is chasing as memory utilization is high. Please advice.
Ryan
//All our dreams can come true, if we have the courage to pursue them// -
what's your max memory set to? How much memory in the server? what's your page life expectancy? are you having performance issues?
Just because memory utilization is high doesn't mean there is a problem. There's many factors to take into account before taking action...
[font="Times New Roman"]There's no kill switch on awesome![/font] -
SQL Server likes to cache stuff for performance, since disk I/O is usually an expensive part of an operation. Is your server under stress? Like was posted above, high memory use doesn't necessarily mean there's a problem.
The memory is there to be used and SQL loves to use memory. We have one server where the memory ran pretty consistently between 85% and 90%, but the performance was lackluster. We added more and it still runs between 85% and 90%. Performance is now awesome, but the point is that SQL loves memory and will usually consume almost everything you throw at it.
-
SQL is supposed to consume memory, that's how it's designed. If you don't want SQL to take all the memory on the server, set max server memory and that will limit what it takes.
SQL's high memory usage is a good thing, it uses memory to reduce the overhead of going to disk and compiling plans. High memory usage is a feature.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Avg. page life expectency is 41299. No max memory limit is set. Total physical memory in the server is 32GB. SQL server is consuming 29.6GB(seen from task manager).
Gail,
Do you recommend to set memory limit to a lower value in this case?
Ryan
//All our dreams can come true, if we have the courage to pursue them// -
Ryan007 (10/10/2013)
Do you recommend to set memory limit to a lower value in this case?Always. I really don't like SQL and the OS fighting over memory (which they will do if max server memory is the default 2k TB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (10/10/2013)
Ryan007 (10/10/2013)
Do you recommend to set memory limit to a lower value in this case?Always. I really don't like SQL and the OS fighting over memory (which they will do if max server memory is the default 2k TB)
I agree with Gail, and I've set ALL of my SQL Server max memory to be 2-3gb less than the total amount of memory in the box.
As mentioned above, SQL Server will gobble up memory to a point where it has the potential of contending with the OS. This is not a good situation when the OS doesn't have enough memory to do its thing.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
Thanks Gail.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply