sql server i/o bottle neck ?

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 ?

.

Relevant Pages

  • Re: sql server i/o bottle neck ?
    ... It's hard to explain the I/O disparity without knowing the details of your ... My first guess is that you might be committing each SQL Server insert ... An I/O intensive process will generally run at the max speed of your disk ... The system monitor shows during my job: ...
    (comp.databases.ms-sqlserver)
  • Re: sql server i/o bottle neck ?
    ... It's hard to explain the I/O disparity without knowing the details of your ... My first guess is that you might be committing each SQL Server insert ... An I/O intensive process will generally run at the max speed of your disk ... The system monitor shows during my job: ...
    (comp.databases.ms-sqlserver)
  • Re: sql server i/o bottle neck ?
    ... You can use a fully-logged bulk insert by changing the recovery model to ... Bulk insert is the fastest way to load data into SQL Server. ... increase write I/O and compound the negative effect of having data and log ... An I/O intensive process will generally run at the max speed of your disk ...
    (comp.databases.ms-sqlserver)
  • Disk Queue Length counters not meaningful in SQL 2000?
    ... "Note on Disk Queue Length ... I/O problems with SQL Server 2000. ... complete before issuing another request. ...
    (microsoft.public.sqlserver.server)
  • Re: Caching control
    ... |> | invalidate/unmap them in order to discard the data from memory. ... |> writing out to disk. ... | easy to discard as clean disk cache. ... stating that a specific amount of RAM can be used only for I/O ...
    (comp.os.linux.development.system)