SQL Server Performance

How to determine disk IO per physical disk.

Discussion in 'Performance Tuning for Hardware Configurations' started by bertcord, Feb 17, 2005.

  1. bertcord New Member

    I am attempting to determine what kind of performance gain I can expect from a new RAID array I am thinking of getting. I am attempting to use some of the formula#%92s provided in the book SQL Server 2000 Performance tuning by MS Press. Specifically I am trying to determine the IO capacity of my Array and hard-drives.

    The example from the book gives the following formulas

    Sequential IO per second = (1000/(AvgTrackToTrackSeek ms+ Rotational Latency ms))*.85 (should only run at 85%)

    Random IO per second = = (1000/(AvgRandomSeek ms+ Rotational Latency ms))*.85 (should only run at 85%)

    For the books example
    Sequential IO = (1000/(.6ms + 2ms)) * .85
    Sequential IO = 326
    Take away Limitation for the transfer rate of 40mb sec, os components, device driver
    (Ok how do you factor this in???)
    Sequential IO = 255

    Random IO = (1000/(4.2ms + 2ms)) * .85
    Random = 137
    Taking into account overhead in the controller
    Sequential IO = 125

    Current Disk Array
    RAID5 Using PERC3 ULTRA 160- 68GB FUJITSU MAM3367MC
    http://www.fel.fujitsu.com/home/v3__product.asp?pid=303

    Sequential IO = (1000/(.5ms + 2.00ms)) * .85
    Sequential IO = 340

    Random IO = (1000/(3.75ms + 2.00ms)) * .85
    Random IO= 148

    Proposed New Disk Array
    RAID5 Using PERC4 ULTRA 320 – 13 Disks 136GB Seagate ST3146807LC
    http://www.seagate.com/cda/products/discsales/enterprise/tech/0,1084,530,00.html
    Sequential IO = (1000/(.45ms + 2.99ms)) * .85
    Sequential IO = 247

    Random IO = (1000/(5.0ms + 2.99ms)) * .85
    Random = 106


    So based on these calculations on a disk-to-disk comparison the old disks can perform more IO. What factor does the controller card have? Is the controller card a bottle neck in the old array.

    For example

    old array - 1, 924 I/Os of Random IO (#drives x Random IO a second)
    new array – 1,378 of Random IO (#drives x Random IO a second)

    Can the old array process this many IO#%92s in a second? Of course If I use this array I will set it up in RAID10. But how do I determine the IO#%92s a disk can performance taking into account the controller card.


    Bert
  2. joechang New Member

    it appears you are comparing 15K Fujitsu drives against 10K Seagate drives

    in general, you want total IO capability, so number of drives adjust for speed (10K vs 15K)

    I don't trust the Dell PERC3 controller, they never published tpc benchmarks with it, using a Mylex RAID controller, before switching to the PERC4.
    Otherwise, a good RAID controller will not impact random IO performance.
    Sequential IO performance is best at 5-7 drives per SCSI channel
  3. bertcord New Member

    Thanks for replying…

    The number of drives is going to be the same. The current system has two dual channel PERC3 cards with 13 drives each.

    The new proposed machine will have 2 PERC4 cards with 13 drives. The array will be much larger as the new drives are 136GB drives compared to the old 68GB ones.

    Are you saying that the old PERC3 array configured as a RAID5 would be faster than the new PERC4 configured a as RAID5?

    Wont I get any some kind of performance increase as the new controller is ULTRA320 vs. ULTRA160?

    Thanks again
    Bert

    I am stuck with the PERC….I can only purchase hardware from Dell…
  4. joechang New Member

    I said the PERC3 has problems, don't use them.
    The older 15K drives will handle more random IOs than the new bigger 10K drives.
    Size has almost nothing to do with performance.
    U320 will have better sequential bandwidth than U160, but neither will bottleneck random IO.It is possible the newer drives will have better sequential IO.

    Random IO is usually what transaction processing needs.
    So you would probably be reducing performance.

Share This Page