June 16, 2009 at 9:44 pm
Hi all, I have a big challenge on our server. we run SqL 2000 on windows 2003 server (enterprise)., during peak periods, we realized that the system drags a lot. I currently increased the memory after realizing that the page life expectancy drops far below 300 a lot, infact sometimes it is as low as 52. buffer cache hit ratio is averagely between 93-98% on performance monitor.
we increased the physical memory from 4 gig to 8 gig last monday, used the /PAE IN boot.ini, enabled AWE, then configured the 'max server memory to 6144 and yet the system now hangs sometimes or get far slower than when the memory was 4gig. I noticed that after increasing the memory, maximum server memory (MB) reads 6144. when the memory was 4gig, it read 2147483647. the free hard disk space on the boot drive is about 1Gig....What can I do to enhance the performance, does it mean Sql is not seeing the full 6gig we have reserved for it out of the 8 gig on the server?
June 16, 2009 at 11:45 pm
Using AWE will be slower than using "natively-addressed" memory, especially if you are churning those AWE "pages".
Have you included the /3GB switch in your boot.ini? That will give SQL Server 3GB of "natively-addressed" memory (ie. the memory below the 4GB 32-bit address limit), so will help reduce any AWE churn. That should (not that proviso) get you back at least to where you were, if not better.
However, it sounds like you're building a really good business case to take to Management to get an upgrade to SQL2k5/8 x64 on a new multi-core server.
You may also want to try tracking down the statements that are causing the greatest resource utilisation and optimising them, but you've got less options in that regard than you do under SQL2k5/8: another upgrade selling point. It depends a lot on what's wrong and how the application is written as to how easy or difficult any optimisation can be. Missing indices are pretty easy but if it's bad SQL or poor schema design then it might be very difficult/expensive to effect the changes in the application.
Times are tough, but they're tougher when your systems can't keep up with the workload that's thrown at them, especially if they're used by external customers.
June 17, 2009 at 1:54 am
Are you sure that lack of memory is the issue?
In my experience, the performance problems that you describe "usually" come down to one of a few things
CPU - check this with perfmon, although I've yet to see a case where CPU was the issue with a SQL Server installation.
I/O - check your disk read/write speeds and throughput with perfmon. If these are bad, you need to look at the I/O hardware, as no amount of memory or query tuning will solve the problem. If they seem OK, run profiler to see how much I/O your queries are doing. If some of these are excessive, you may need to do some index tuning or rewriting of queries.
Blocking - if the above are OK, check that your queries aren't continually blocking each other.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply