|
Posted by dunleav1 on 09/14/06 20:38
I built a test job that loads data into the database. I get great
performance with Oracle and I'm trying to tune Sql Server to get the
same type of performance.
Based on system monitoring it looks like an I/O issue. So I started
investigating based on sql server perfromance tuning technical
reference.
Here are my system monitor findings:
The system monitor shows during my job:
disk writes per second 3670
disk reads per second 0
avg disk queue is .45
avg disk second read 0
avg disk second write 0
My total i/os per second is 7200. (disk writes per second * 2) + disk
reads. It's times two because I am mirroring.
I have a 6 disk raid array configured Raid 1/0 with 200M of cache
configured read/write. The average disk second read and write of 0
means no i/o latency but my i/os per disk is high compared to
Microsoft's recommendation of 125 i/o per disk. I don't see an i/o
issue based on the numbers, except for the amount of disk writes per
second. The same job ran in Oracle had 187 disk writes per second as
compared to 3600 writes per second in Sql Server. What's up with that?
Here's my calculation:
i/o per disk = [reads + (2 * writes)/number of disks
i/o per disk = [0+(2*3600)/6] = 1200
Microsoft is saying I need way more disks based on their calculation
recommendation.
Total i/o / 125 i/os per disk = disk needed
7200/125= 57.6 - 58 disks
If the disks are showing no latency in the system monitor how can disk
be a bottleneck?
Based on Microsoft's recommendation - I need 58 disks to relieve the
disk bottle.
But I have no i/o latency and the disk queue length is .45.
Ahy recommendations ?
Navigation:
[Reply to this message]
|