Hi All,
We have been getting complaints about the performance of our new Warehousing System, it is running on SQL2000 SP4 on a Windows 2003 SP1 Standard Server. 2 x Intel 2.8GHz Processors, 2GB RAM, RAID with 3 Disks
I have been spending a lot of time trying to spot the problems and have tried checking the recommended counters for the various pinch points (CPU, Memory, I/O) and they all seem to be with normal tolerances during day to day activity or even when running intensive queries. Some of these problems are caused because some queries are client intensive (so time to upgrade some PCs) however the two questions I have are;
1/ We were having a large number of Page Faults so I upped the RAM, this seems to have made a "bit of an improvement" in that I believe we have had one time out error in a week instead of several a day however the Page Faults typically are high (typically 40 or 50/sec but up to several thousand with an average of around 150 to 200/sec) still and also stay that way over night. Digging a bit deeper most of these are "Soft Page Faults" with the Hard Page Faults only occuring occasionally when the server is working hard. Do I need worry about Soft Page Faults (I believe Hard Page Faults are ok at 5-10/sec which is what we have)?
2/ The most intensive queries seem to (usually) peak out at between 45% and 55% for the duration, would throwing Hardware (i.e.: more powerful CPU's) at them make a significant difference to the time taken to process the query (it is quite rare that more than one large Query is run at the same time) or is that a fairly acceptable CPU usage for a large query with no real performance gains to be made?
Start Free Trial
