February 8, 2002 at 5:00 pm
Setting: SQL 7 Server (the only app running on the machine), PIII 733Mhz, 654 MB total RAM
PerfMon readings:
Processor queue length = 2.4 (average)
Available RAM = 22MB (for many weeks)
Drive C Paging file usage = 97%
Drive D paging file usage = 6%
Memory: Pages/sec = 0
Page faults/sec = 121
SqlCache Memory = 864KB
From the processor queue length I'd say we have "processor" congestion. (Does this mean we need a faster CPU or dual CPUs?) How can I track down the source of the congestion?(We are a relatively small company and I don't believe that we get that many hits on our web server.)
It seems there is plenty of paging file room on D but what is the impact on performance of the Drive C page file being almost full?
Furthermore, we have another machine, with a dual 550 Mhz, 916MB RAM with processor queue lengths on average = .01!
Boy am I lost. Any help much appreciated.
February 8, 2002 at 5:05 pm
Now with paging file filling up on one drive you get drive contention. The data swapping back an forth can actually be your biggest issue. Think of it this way the CPU will get used more as it is trying to get to all the data and work with, but when RAM is not enough it will swap off to the hard drive anything it ithinks it can get away with and some that it can't. I would suggest looking at the CPU ustilization and I believe it will be high all the time. Can I get a bit on the full specs of the machine, I can first offer you suggestion as is, but ultimately a more RAM will be needed since obviously swapping is occuring.
February 8, 2002 at 5:30 pm
Thanks you. I don't understand why the page file being so full is bad (after all we do have a page file on drive D). Also, generally the CPU utilization is about 1% (very low! Now you see why I'm confused).
Further specs: the computer is a Proliant 1850-R with a RAID 5 array. It runs NT 4, SP6, sql 7 (don't think SQL has a SP applied).
Bill
February 8, 2002 at 5:43 pm
Try this, go and change the settings of the swap file setting C to 0 and D to the 2 current added together. Could but I doubt be a corruption in the swap file. Also consider running checkdisk in tool on the c drive properties dialog. (May be a fix in a service pack or even a patch for the compaq itself).
February 8, 2002 at 6:00 pm
Interesting. My solution is always to add memory! The less you hit the disk for anything the better. Other than the cpu queue, are you noticing any problems, performance issues, etc?
Andy
February 8, 2002 at 9:30 pm
Something doesn't mesh. Your processor queue length is high. Your page faults/sec is high, but your processor utilization is at 1%? Usually when there is excessive paging, the processor is pegged and pegged hard.
From the description you have one hardware RAID 5 and 2 logical drives, correct? If so, where the files are located is pretty much irrelevant, it's the physical platters that make the difference.
Looking at everything, it does look there is a memory bottleneck, especially with the page faults/sec. That's about the cheapest and quickest way to try and improve performance. Under NT 4, a switch from a single processor to a multi-processor system is only supported with a reinstallation of the OS (to include an upgrade, I believe). It *can* be done, but Microsoft recommends strongly against it. A friend of mine hit this brick wall when wanting to make one of his Dell servers dual processor.
A couple of other pertinent questions. Is this server dedicated to SQL Server and SQL Server only? Also, what is the approximate size of the DBs on the system?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 9, 2002 at 10:04 am
My point was to see if the C swap file is corrupt. Not redistribute it. I have seen this can be the case and I feel it is always best to start with the simplest thing first. If you make the change and performance improves then split the file back out after running check disk against the c and d drives. Then baseline your system for a week if it holds at those values and your database doesn't change a lot or have a lot of queries against it then if these values change along the way you know you are suffering performance hits from somewhere other than the RAM. Also if you have a lot of indexes and this databse isn't extremely large (meaning you can put another copy of the array with no issue). My next step would be to DBCC DBREINDEX against all indexes, DBCC UPDATEUSAGE to fix iternal reporting problems that can build up and sp_updatestats (I may have that wrong but you will find it spoke about in other threads). Once all that is done then due to things that will happen to the log file I would perform a full backup and use the process in scripts for forcing the log file to shrink (as SQL7 does not do this without help). Then check your system again and if looks normal baseline it for a week. More times than a few it is a matter of improper maintainence of the drives and databases that cause issues. Start there and if none of this helps go straight for the RAM as that will be the next possible bottleneck (I would suggest to anyone that you get the fastest available RAM compatiable with your system and that you fill each slot with the maximum allowed with a minimum total to 1GB (RAM is not all that bad on cost, and the reason for maximum piece that will go in a slot is to have room for future needs).
February 9, 2002 at 5:33 pm
Thanks to everyone for their great insights.
Today I was digging around found more facts:
Total Processor Time = 2%
Size of paging file on C = 10-25MB (no wonder 97% utilization!). If I remember right, NT wouldn't let us set the pagefile size to "0". It needed a miniumu size of 2MB or some such.
Size of paging file on D: = 641-690 MB.
But now, instead of 22MB RAM available the server is down to 4MB and the Processor Queue Lengfth is about 3.8! Something is corrupt, or a memory leak, or both. I'm rebooting this server daily. Next stop wil be to add RAM.
Incidentally, this server is used only for SQL 7. It has 1 RAID-5 drive with 2 logical drives. I have not noted any problems with the server except for the disturbing Perfom readings.(But I'll keep looking and I may find trouble.)
February 9, 2002 at 5:43 pm
Since it is a Proliant, you may want to head over to the support site:
http://activeanswers.compaq.com
There are is a performance analyzer tool that runs on a separate system and uses the Compaq Insight Management Agents. Perhaps this will help locate the true issue.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply