February 12, 2010 at 10:09 am
All,
I have a machine that I had to limit the max memory setting on all the instances since SQL was sucking up all the memory on the box. SQL is the only thing running on this machine. No one is complaining about performance, but I see that memory practically max's out every day and I want to be proactive. I want to determine if more memory should indeed be added. I found an article that picks out some perfmon counters to determine if a server is in need of RAM (http://www.sql-server-performance.com/tips/performance_monitor_memory_counter_p1.aspx). I've collected a 24 hr period (attached) and reviewed but I'm still a little unclear. If I look at the overall average it doesn't look bad, although the pages/sec doesn't look good. I would like to get thoughts from others as I'm the only DBA and have no one else to ask. I'm also attaching the memory consumption graph for the last day.
Windows Server 2003
Enterprise x64 Edition
16GB RAM
SQL Server 2005 Enterprise Edition
Let me know if you need additional info. I appreciate any guidance!
February 12, 2010 at 10:43 am
How much memory is on the box, what's the version / edition of SQL and Windows?
If there's nothing on the box, no reason, typically, to limit SQL Server. It caches data, so it should use up almost all the memory on the box, less what the OS keeps.
February 12, 2010 at 12:05 pm
Steve Jones - Editor (2/12/2010)
How much memory is on the box, what's the version / edition of SQL and Windows?If there's nothing on the box, no reason, typically, to limit SQL Server. It caches data, so it should use up almost all the memory on the box, less what the OS keeps.
Windows Server 2003
Enterprise x64 Edition
16GB RAM
SQL Server 2005 Enterprise Edition
We had to limit memory of each instance because they were consuming all memory and crashing the box. Since setting max memory on all instances, it has been better, the box doesn't crash but 2 or 3 instances do hit their max every day, consuming nearly all memory available on the box.
So I'm really wondering, ok it's hitting max memory, but is SQL really ok with the amount of memory, or do I need to increase it? Are the counters I have taken ones that can tell me this?
February 12, 2010 at 12:29 pm
How many instances you have on this box?
February 12, 2010 at 12:47 pm
Six instances reside on the box.
(For some reason I can't gather counters for the last instance, so you'll only see 5 in attachment)
February 12, 2010 at 1:57 pm
Apologies. I missed the instance note. I looked at the image and it appeared to be one instance only. didn't check the Excel.
A couple links to read about memory pressure:
http://blogs.msdn.com/slavao/archive/2005/02/01/364523.aspx
http://technet.microsoft.com/en-us/library/cc966540.aspx#EYIAC
You'll need to examine how each instance is performing. I would expect that each of them would ramp up to the total allocation you've given them and use that. I'm not sure how well these techniques work for multiple instances, but I'd start with them and see what you find. If you see that one instance needs memory and another doesn't, I might adjust the values you have set for max memory in each.
February 12, 2010 at 2:13 pm
sql server appears to have enough memory.
The OS might be a little starved for attention.
How much memory did you leave the OS?
Like Steve, I frequently consult Slava Oks who wrote SQLOS when it comes to memory issues.
Here's the link I usually use when calculating memory on multi-instance servers
points E and F are the sneaky ones that will steal OS memory.
you can query out both the number of worker threads and the MPA use from the SQL 2005 DMV's
Craig Outcalt
February 12, 2010 at 2:28 pm
I agree with Steve, Make sure you allocate atleast 2GB for OS and allocate remaining memory to SQL. Go from there and see how it goes.
EnjoY!
February 12, 2010 at 2:42 pm
Thank you all for your responses. I will get to reading up on the links you posted!
I reviewed the max settings on each instance and it looks like only the ones that seemed to be memory hungry did we limit (3 of them). In adding up the max's, the OS should have 3GB left, although there are 3 instances w/o a max setting so this could still shortchange the OS if those 3 take over 1GB. I will review those 3 instances on what memory they usually cosume and set the max to that number. Then I will adjust the current 3 accordingly to ensure 2GB for the OS.
I really appreciate you pointing me in the right direction!
February 12, 2010 at 2:59 pm
SQLBOT (2/12/2010)
sql server appears to have enough memory.The OS might be a little starved for attention.
I'm glad to hear that SQL seems to be ok on memory. Although I'm curious to find out your reasoning for saying that. Is it because the AVG's of the counters are ok?
For the most part the Buffer cache hit ratio was > 99% although there were times that it was down to 97% for one instance - I'm thinking this is ok though. The Page Life Expectancy's AVG was high, but there were many times where it fell below 300 - this I wasn't sure if it was ok.
The Available MB never got close to 4MB so this is good. But the pages/sec definitely got above 20 many times and therefore the avg is definitely over 20.
I'm thinking this is why you say the OS may be starved, the SQL counters AVG's are ok, but the pages/sec avg is not. However, I want to verify that with you so I make sure and understand everything correctly.
February 12, 2010 at 3:19 pm
you nailed it.
PLE of 300 means that objects are in cache for 5 minutes.
That's not too shabby. More is obviously better.
Something to consider would be to try to average out the PLE's between your instances by tweaking the max memory for each one until they all sit around the same level.
The Pages/sec counter is really tough to trust because there are so many factors and variables.
You need to consider longer averages like more than 10 minutes at 100 pages/second as a good indicator of memory pressure at the system level.
I see there's a heavy hit around 5:40 pm on 2/10
I'm thinking antivirus or something? Not sure but that's a trend to watch.
Good luck, have fun.
Craig Outcalt
February 15, 2010 at 10:38 am
Craig - I really appreciate the info! Thank you very much for the help and great explanation.
March 5, 2010 at 8:55 am
I'm just getting time to come back to this memory task. I have yet to modify any settings yet, but I know in the last post we were saying that SQL seems fine on memory, but the OS might be lacking a bit.
However, I forgot one of the most important messages I was seeing (which actually started me down this path in the first place)! The reason I was questioning whether SQL was having a memory issue is because on every instance I see the following entry daily in the log:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 77604, committed (KB): 152288, memory utilization: 50%. (of course the numbers vary per instance)
This occurs at the same time for every instance, but time of day does vary. So, now I'm back to being confused because we were saying that SQL seemed fine on memory. Can someone help set me straight!
March 5, 2010 at 9:59 am
ecause on every instance I see the following entry daily in the log:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 77604, committed (KB): 152288, memory utilization: 50%. (of course the numbers vary per instance)
I believe that message is saying that an external process is stealing memory from that particular SQL Server.
I would wager it's one of the 3 other SQL instances that have no max server memory set.
to remedy that (if it is the problem) you should set 'lock pages in memory'... setting on the instances that you don't want memory stolen from.
Unfortunately, that's a bit like stealing from Peter to give to Paul... you should give all your instances a max memory setting in a multi-instance system.
Craig Outcalt
March 8, 2010 at 7:38 am
Ok. I'll get the max memory set on all instances and see what happens from there.
Thanks!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply