Re: sql server i/o bottle neck ?

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?

It's hard to explain the I/O disparity without knowing the details of your
job. My first guess is that you might be committing each SQL Server insert
(default is autocommit) but not each Oracle insert (default is implicit
transactions on). This would require SQL Sever to perform many more
transaction log writes.

If the disks are showing no latency in the system monitor how can disk
be a bottleneck?

An I/O intensive process will generally run at the max speed of your disk
subsystem. Not all I/O is equal; sequential I/O is much more efficient than
random I/O. You can typically do about twice as many sequential I/Os per
second than random I/Os. This is one reason why it's usually best to
perform large data loads using a bulk insert method.

Note that your performance monitor is reporting 3670 writes/sec but,
according to the perf tuning technical reference, your 3 usable disks should
only sustain about 375/sec (3 x 125). It looks to me line the write caching
controller is skewing the numbers. Data must eventually get written to
disk, but since the controller writes data asynchronously, that won't become
a bottleneck until the cache is saturated.

You mention only a single RAID 01 array. Is the transaction log on the same
array as the data files?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"dunleav1" <jmd@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1158266291.118027.118880@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 ?
    ... 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)
  • Re: Disk Queue Length counters not meaningful in SQL 2000?
    ... This means SQL Server 2000 tries not to issue 200 IO per second if your IO ... average disk queue length to be more than 1. ... > engines dynamically manage disk I/O, which is why the Max Async I/O ... > complete before issuing another request. ...
    (microsoft.public.sqlserver.server)
  • Re: Short SMART check causes disk op timeouts
    ... Second, your short offline test runs at 0300, but the errors you're ... 0301 in the morning, and many of those are I/O bound. ... perform a lot of disk I/O, so it's possible that on Sunday specifically ... taking too long when internally suspending the SMART test. ...
    (freebsd-stable)