February 13, 2011 at 10:08 am
we have 8 servers with less than 20% memory available. 4 of them have less than 150MB free. They are running SQL server 2008 R2 x64 Enterprise with 64GB memory, 32 of which is the max SQL can take using sp_configure.
a colleague of mine is convinced this is fine, no problem. he thinks having more free would mean money wasted. I disagree after my years experience, but cannot come up with a solid technical explanation for him. He thinks if SQL needs more memory it will get it since it has it's own buffer. We run SQL, AV, and our application on the system, plus a few DB maintenance tools we wrote.
Can someone explain to me who is right and how to explain this properly? If there are other counters you want to see the values for, let me know. We're tracking tons. Just let me know the goal I should look for.
February 14, 2011 at 12:21 am
shifty1981 (2/13/2011)
we have 8 servers with less than 20% memory available. 4 of them have less than 150MB free. They are running SQL server 2008 R2 x64 Enterprise with 64GB memory, 32 of which is the max SQL can take using sp_configure.a colleague of mine is convinced this is fine, no problem. he thinks having more free would mean money wasted. I disagree after my years experience, but cannot come up with a solid technical explanation for him. He thinks if SQL needs more memory it will get it since it has it's own buffer. We run SQL, AV, and our application on the system, plus a few DB maintenance tools we wrote.
Can someone explain to me who is right and how to explain this properly? If there are other counters you want to see the values for, let me know. We're tracking tons. Just let me know the goal I should look for.
Yes,less than 20 % indicate the memory pressure
Check the counters here
http://www.sqlservercentral.com/Forums/Topic1018559-146-2.aspx
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 14, 2011 at 5:36 am
Thanks, but I need some sort of article or technical explanation of why it's important to have roughly 20% of memory available.
February 14, 2011 at 12:59 pm
shifty1981 (2/14/2011)
Thanks, but I need some sort of article or technical explanation of why it's important to have roughly 20% of memory available.
Never heard of such a requirement.
February 14, 2011 at 1:11 pm
ben.rosato (2/14/2011)
shifty1981 (2/14/2011)
Thanks, but I need some sort of article or technical explanation of why it's important to have roughly 20% of memory available.Never heard of such a requirement.
Thanks. Have you heard of any requirement at all? I've seen things say 5MB or 100MB. But with that little free it's hard to tell if it's really hit it's needs or if Windows is just adjusting it to make sure it doesn't run out.
February 15, 2011 at 1:11 am
shifty1981 (2/14/2011)
Thanks, but I need some sort of article or technical explanation of why it's important to have roughly 20% of memory available.
shifty1981
Check the counters,what i have given? You can't take the discission only seeing the 20 % of available memory.
Monitor/catch the all necessary counters routinely.Prepare the document yourself then give the checklist to your team.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 15, 2011 at 5:05 am
Hi,
I don't know if there is enough information provided here to give you a definite answer. Is 150MB too low? Well it kind of depends. Are you seeing paging on the servers? How if the page life expectancy in SQL, etc.
You need to look at performance counters as a whole. A simple example I've seen recently is where CPU is running constantly 85-98% and hitting 100% regularly. The disk I/O waits were also high and page life expectancy was really low. Was the problem the CPU? Well no in this instance the system was under memory pressure which was leading to paging, leading to low page life expectancy leading to high I/O and high CPU numbers.
Check these out: -
SQL Server Perfmon Counters Poster
February 15, 2011 at 7:07 am
sean.massey (2/15/2011)
Hi,I don't know if there is enough information provided here to give you a definite answer. Is 150MB too low? Well it kind of depends. Are you seeing paging on the servers? How if the page life expectancy in SQL, etc.
Did you see my pervious reply ?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 15, 2011 at 10:02 am
Thanks Sean. Below are the stats during our peak usage. When you say "are you seeing paging" what do you mean? I ask because I've read articles that say look for "pages/sec" but then I've read newer articles that say this value is not as reliable as was once thought.
Checkpoint pages/sec669.770 2/14/2011
Page File Size - E67640426496.000 2/14/2011
Page File Usage6.046 2/14/2011
Page life expectancy93.765 2/14/2011
Page Reads/sec577.063 2/14/2011
Page reads/sec (SQL:BM)1892.015 2/14/2011
Page Splits/sec33.032 2/14/2011
Page Writes/sec0.000 2/14/2011
Page writes/sec (SQL:BM)898.589 2/14/2011
Pages Input/sec884.645 2/14/2011
Pages/sec884.645 2/14/2011
Virtual Memory (bytes)18233497035.035 2/14/2011
sean.massey (2/15/2011)
Hi,I don't know if there is enough information provided here to give you a definite answer. Is 150MB too low? Well it kind of depends. Are you seeing paging on the servers? How if the page life expectancy in SQL, etc.
You need to look at performance counters as a whole. A simple example I've seen recently is where CPU is running constantly 85-98% and hitting 100% regularly. The disk I/O waits were also high and page life expectancy was really low. Was the problem the CPU? Well no in this instance the system was under memory pressure which was leading to paging, leading to low page life expectancy leading to high I/O and high CPU numbers.
Check these out: -
SQL Server Perfmon Counters Poster
February 15, 2011 at 10:05 am
Thanks, but that's just a forum post telling someone what to look at/for. I don't see a basis for those recommendations. Ram is not cheap when you're near 100GB of Memory, so 20% overhead is costly unless it is really needed. You're the first person I've ever seen give that high a recommendation. I'm sure it's a safe recommendation, but I'll need some sort of article or technical explanation for that number if I'm going to be able to convince the team and CEO it's worth money.
muthukkumaran (2/15/2011)
sean.massey (2/15/2011)
Hi,I don't know if there is enough information provided here to give you a definite answer. Is 150MB too low? Well it kind of depends. Are you seeing paging on the servers? How if the page life expectancy in SQL, etc.
Did you see my pervious reply ?
February 15, 2011 at 12:39 pm
i can see low PagelifeExpectancy and high pages/sec showing memory issue..
February 15, 2011 at 11:07 pm
kp_kumar (2/15/2011)
i can see low PagelifeExpectancy and high pages/sec showing memory issue..
Start a new thread to ask the questions.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 16, 2011 at 4:37 pm
You may want to check out these DMV queries to help see where you have a bottleneck but with low page life expectency and paging I'd be thinking memory.
February 17, 2011 at 8:33 pm
sean.massey (2/16/2011)
https://sqlserverperformance.wordpress.com/tag/dmv-queries/%5B/quote%5D
Thanks sean for the tips. I'll try and see. I have no idea what the other guy asked him to start another thread.
February 18, 2011 at 3:59 am
shifty1981 (2/17/2011)
sean.massey (2/16/2011)
You may want to check out these DMV queries to help see where you have a bottleneck but with low page life expectency and paging I'd be thinking memory.https://sqlserverperformance.wordpress.com/tag/dmv-queries/%5B/quote%5D
Thanks journeyman for the tips. I'll try and see. I have no idea what the other guy asked him to start another thread.
Oooo.... the name is not journeyman.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply