Brand new server showing signs of disk I/O

[mebenz]

We have the following server with a single database on SQL 2000 SP4:

Server: HP ML370 G5
CPU : 2x Quad Core Intel Xeon Processor E5345 (2.33 GHz, 80 Watts, 1333 FSB)
RAM :4GB memory,
NIC : Dual NC373i Multifunction Gigabit
STORAGE :Eight 72GB 15K hard drive - 2 RAID 1 for OS, 2 RAID 1 for logs and rest in RAID 5 for data
OS :W2K3 Server Enterprise


We ran tests in our application for 1 hour and the following are the results. The memory and disk queue lengths seem high for a single application and new, very fast server.

Average Min Max

CPU % Processor Time 3.829 0.000 18.984

Memory pages/sec 1.812 0.000 133.608

I/O Avg. Disk Queue Length
Total Drives Together 5.632 0.000 163.212
C (Windows and SQL) 0.014 0.000 0.239
D (SQL Logs) 0.036 0.000 0.638
E (SQL Data) 5.581 0.000 162.566

Buffer cache hit ratio 99.824 97.231 99.884


We have McAfee running on this server. Could it possibly be adding overhead to the disk as I believe they configured it with defaults. Are there recommended settings for virus software on a sql server? We are running enterprise 8.51.

 

[Ovatvvon]

RAID 5 is not good for Database servers. Microsoft Recommends RAID 1/0. That's all we implement in our environment with over 250 SQL Servers (over 7,000 databases, ranging from a few MB in size, up to several Terabytes).

I'd steer clear of RAID 5, since it has the extra writes for the parity. RAID 1 also does not give you any added performance. If this were a RAID 1/0 setup, I'd say you may be fine, as you could adequately handle an Average Disk Queue length up to a value of 8 on a 4-disk RAID 1/0 (2 per spindle in the RAID-Set). Your Max value for E:\ is higher than it should be, that's for sure. Even adding 2 more drives to E:\ and converting to a RAID 1/0 might solve your problems. I dropped a database from over 6,000 queue length to below 3,000 by adding 2 physical drives to my already present RAID 1/0. (I would've added more, but we were out of space in the external chassis', and had to order more. So it was a temp fix.)

RAID 5, I'm not sure what to tell you, since reads will be great, but writes will be worse.





-Ovatvvon :-Q

 

[mebenz]

We setup the data in a RAID 5 and logs in RAID 1 as that is what our core application manufacturers (Microsoft Dynamics and others) have in place as the requirements for the databse server, so we sort of had to comply with that.

THis server only has one database on it which is only 2Gb in size (grew within 2Gb in a year and a half), so there shouldn't be substantial writes.

 

[Ovatvvon]

I'm not aware of antivirus adding an enormous amount of disk I/O, unless it's doing heavy full-disk scans at the same time. You'll have to check out your scheduling. Although, our environment is large enough that I mostly only handle the database aspects, not the O.S. (and anti-virus software).

The disk is obviously being taxed over whatever timeframe you took the PerfMon counters. Large amounts of data can be one factor of heavy disk i/o, but even in smaller databases, you can get a lot if there's a lot of data being pulled and purged (i.e. just high activity). RAID 1/0 will always give you better performance; it just takes more disk spindles than RAID 5. A minimum requirement of RAID 5 would not stop you from going to RAID 1/0. If you ran that by your Microsoft Reps, I'm fairly certain (if they know anything from the database side of things - whomever you're talking with) that they will fully support RAID 1/0.

If you know the approx. time the disks are running heavy, you can take SQL Profiler and check out the activity at that time and see if the database is causing it or not. Otherwise, check out your system processes to see what's running on the system at that time, and consuming resources.


-Ovatvvon :-Q

 

[Ovatvvon]

mebenz,

someething else I was thinking about. Have you looked at query performance? Is it possible any table scans are going on due to improper indexing? That could create a lot of disk activity as well.

Just a couple things to check out. Good luck.


-Ovatvvon :-Q