September 15, 2009 at 7:34 pm
Tony Fountain (9/15/2009)
My question is this, how do I prove it is memory? Is seeing the page life expectancy drop below 300ish good enough or are there other specific indicators I should be concerned with?If I do need to upgrade the memory beyond 4 GB, it will require a rebuild to another version of the OS since Windows 2003 Standard only supports up to 4 GB.
It would be interesting to know a rough average and range for the page life expectancy you are seeing, together with an average for the buffer cache hit ratio.
Win 2K3 standard x64 supports 16-32GB depending on Service Pack. See http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx#physical_memory_limits_windows_server_2003. SQL Server really needs 64-bit these days...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 16, 2009 at 8:12 am
Paul White (9/15/2009)
BTW in case you missed Ian 'Capt' Scarlett's suggestions - I would encourage you to follow that good advice.
Paul
Yep, did that. I actually created separate perfmon consoles for each of the logical drives.
September 16, 2009 at 8:15 am
Paul White (9/15/2009)
It would be interesting to know a rough average and range for the page life expectancy you are seeing, together with an average for the buffer cache hit ratio.
Without getting elaborate and pulling the logs into a spreadsheet, from what I've been seeing is the range for the PLE is 0 to 600. An average is probably going to be around 100-150. It peaks with no activity and drops when there is, simply put.
Paul White (9/15/2009)
Win 2K3 standard x64 supports 16-32GB depending on Service Pack. See http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx#physical_memory_limits_windows_server_2003. SQL Server really needs 64-bit these days...
Yes, this is the OS we would upgrade to.
September 17, 2009 at 9:58 am
1) get a professional tuner to help/mentor you and your system. You have admitted to spending weeks on this. A competent tuner could have given you a slew of recommendations in a matter of minutes/hours. The stuff you are trying to do is actually pretty basic knowledge for a qualified perf expert. You could continue to go back and forth on the forum for another few weeks and only scratch the surface of what could be improved on your system.
2) what is avg disk sec/read and avg disk sec/write? Disk queue length is essentially meaningless.
3) why just one NIC to the iSCSI san?? team up a few of them and see if that doesn't help.
4) how many spindles on the san? has anyone been monitoring it's utilization?
5) you need to do IO stall analysis on your SQL Box
6) any index and query tuning been done? what are your worst performing queries? missing indexes been checked?
7) how much 'active' data on the sql server?
again, this is not even a snowflake on the tip of the iceberg of tuning possibilities!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 18, 2009 at 6:51 am
Tony Fountain (9/16/2009)
Without getting elaborate and pulling the logs into a spreadsheet, from what I've been seeing is the range for the PLE is 0 to 600. An average is probably going to be around 100-150. It peaks with no activity and drops when there is, simply put.
That is the best evidence yet that the server needs more memory.
Page life expectancy will naturally increase with no load, of course, but dropping that low indicates either a chronic lack of memory, or one or more badly tuned queries dominating the data cache. Mr S Guru makes some good points - an expert tuner could well sort things out for you in half an hour. Assuming that you don't have one to hand (or lack the budget to engage one), I would encourage you to continue learning, but also work hard on convincing the right people that 4GB is quite a small server these days.
If you are in the market for some intense and accelerated learning, there are a number of extremely good books available on this subject - some written by people you may meet on SSC.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply