I am currently experiencing what I find to be an odd issue with our SQL 2000 server. To begin with, some stats/facts:
- Server is dual 3Ghz with 2 Gig of RAM. 18Gig C partition with a 135gig Raid5 data, plenty of space free. SQL SP3. Windows Server 2000 SP4.
- My experience as a DBA is somewhat limited. I of course can do the basics, know my way around EM and can usually hack together some Transact to get done what I need to with the help of Books Online, but I am not intimately familiar with all the tricks and inner workings of SQL. Our database designers are likewise limited; they can get a database up and running but I don't believe they give much thought to more advanced concerns like index tuning and such.
- That said, or SQL needs are small. 90% of the need falls into the "Application X needs an SQL backend" category. By far our busiest database is event logging of our IPS device, which clocks in at around 40-50 transactions/sec; again I'm no db expert but that seems like peanuts compared to what this kind of server hardware should be able to handle. We do have a couple of custom apps but the transactions for these could literally be measured in #/day, very low volume stuff.
What I'm seeing is periods of very high disk utilization, specifically reads. This started with me monitoring because of some issues one of our apps was having, which turned out to be unrelated to SQL performance, but during this noticing that %Disk Time was periodically getting pegged above 100%. After some research revealed this counter is close to worthless on multi-disk systems, further investigation also revealed that %Idle Time was also bottoming out during these periods, and I am seeing very high Disk Reads/sec and Disk Read Bytes/sec on the physical disk, as well as high Page Reads/sec on the SQLServer:Buffer Manager. Oddly enough, however, I see almost no Write activity during these times, and the Buffer Cache Hit Ratio of SQL Server:Buffer Manager never drops below 95%. Further, this is only happening on the C drive; utilization of the data volume is completely normal.
These periods seem to alternate between "Totally pegged" and "Flatline", alternating about every 5-7 minutes based on my observations. As near as I can tell I don't have any weird rogue processes going out of control or anything like that; I've rebooted the server and there isn't anything suspicious going on in the Event Log. At this point I'm sort of at a loss as to determining what is causing this activity; to me it seems definite that it is SQL Server somehow but I am struggling with nailing that diagnosis down further.
Any advice you all can give as to what further I should be looking at, performance counters or otherwise, or how best to approach determining what within SQL might be accounting for this activity, will be greatly appreciated.
Start Free Trial
