SQL Server Performance

How to Calculate IO Operations Per Disk

Discussion in 'Performance Tuning for Hardware Configurations' started by DBADave, Nov 12, 2003.

  1. DBADave New Member

    This is a followup to an earlier post from September.<br /><br />When comparing RAID 5 to RAID 10 I am using the following formulas:<br /><br />RAID 5 # of Read Requests + (4 x # of Write Requests) / # of disks<br />RAID 10 # of Read Requests + (2 x # of Write Requests) / # of disks<br /><br />Do I determine the number of read and write requests as follows?<br /><br />Reads = Disk Reads/sec * Transactions/sec<br />Writes = Disk Writes/sec * Transactions/sec<br /><br />I want to make sure I am using the correct Perfmon counters.<br /><br />TPS = SQL Server<img src='/community/emoticons/emotion-2.gif' alt=':D' />atabases Transactions/Sec (for all databases)<br />Reads/sec = PhysicalDisk Disk Reads/Sec <br />Writes/sec = PhysicalDisk Disk Writes/Sec<br /><br />I see the following numbers from these counters<br /><br />TPS = 7,500<br />Reads/Sec = 2.3<br />Writes/Sec = 3<br /><br />Reads = 7,500 * 2.3 = 17,250<br />Writes = 7,500 * 3.1 = 23,250<br /><br />RAID 5 I/O Operations per Disk = 17,250 + 4(23,250) / 6 = 18,375<br />RAID 10 I/O Operations per Disk = 17,250 + 2(23,250) / 6 = 10,625<br /><br />Am I doing something wrong? <br />Do I need to divide the TPS by the number of processors?<br /><br />Dave<br />
  2. Luis Martin Moderator

    Setting in home, I can´t remember if all is Ok.
    But, according to formula:

    RAID 5 # of Read Requests + (4 x # of Write Requests) / # of disks
    17,250 + 4(23,250) / 6 = 18,375
    I get:32,75.

    You are dividing all by 6, but plus (+) separete terms, so you must 1rst 4(23,250) / 6 and + 17,250 = 32,75.

    RAID 10 # of Read Requests + (2 x # of Write Requests) / # of disks = 25,00.

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. joechang New Member

    the formula is for physical disk reads and writes as read from perfmon assuming you have a hardware RAID controller.<br /><br />RAID 5 # of (PhysicalDisk Disk Reads/Sec) + (4 x # of PhysicalDisk Disk Writes/Sec) / # of disks<br />RAID 10 # of (PhysicalDisk Disk Reads/Sec)+ (2 x # of PhysicalDisk Disk Writes/Sec) / # of disks<br /><br />the reason you have to apply the formula rather than just using the Physical disk reads and write from Perfmon is that the OS doesn't know about the HW raid controller, so when the OS issues a write to a RAID 10 volume, the raid controller must write to both disks involved in the mirror.<br /><br />7,500 SQL Server<img src='/community/emoticons/emotion-2.gif' alt=':D' />atabase Transactions/sec sounds way to high to me. <br />do most of your requests involve transactions?<br />SELECT queries should register as a transaction.<br />usually, i just monitor SQL Server<img src='/community/emoticons/emotion-7.gif' alt=':S' />QL Statistics - Batch Requests/sec<br />which gives all requests issued to the server, transaction or no<br />
  4. DBADave New Member


    I believe the forumula I'm using is correct, I unfortunately left out some parenthesis in my original post. Sorry for the confusion.

    RAID 5 = I/Os per Disk = (READS + (4 x WRITES)) / Number of Disks

    The division by the number of disks is performed last. I just have my doubts about the perfmon counter "Transactions/sec". The number appears larger then I suspected.


  5. DBADave New Member


    I agree about the size of TPS, but I checked it a couple of times. Any idea why this number would be so high? There are typically about 220 users connected and I'm not sure how many of those are concurrent connections. The applications are primarily written in Powerbuilder and execute stored procedures with typically a lot of tempdb activity. About 100 of those users access the applications through Citrix, but I don't see how that would impact TPS. Not sure why the number is so high. I'll check TPS again tomorrow.

    Thanks, Dave
  6. DBADave New Member


    It occured to me that last night I found a process in a "running" state for about 6 hours. I killed the process. Today TPS has not been higher then 266, which makes more sense. It's still early in the day so the average TPS should begin to rise. I suspect the process I killed was the cause of yesterday's high TPS reading.

    Do you know if the counters Reads/sec and Writes/sec are actually Reads/sec/transaction and Writes/sec/transaction? If so the formulas I am using make more sense to me.

    Thanks, Dave
  7. Luis Martin Moderator

    According Microsoft SQL Server 2000 Performance Tuning by Whalen, García, DeLuca and Thompson,
    your formula is write, but using the following numbers:

    RAID 10 I/O Physical I/O per Disk = (2,3 + 2(3,00)) / 6 = 1,3833
    RAID 5 I/O Physical I/O per Disk = (2,3 + 4(3,00) / 6 = 2,383

    They use directly Disc Read and Write /s.

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  8. DBADave New Member


    I assume this means Reads/sec and Writes/sec are not the same as Reads/sec/Transaction and Writes/Sec/Transaction, but simply physical reads and writes from disk. Would you agree?

    This makes me question the article from Database Journal which multiplies physical reads per transaction by I/O per second.


    Thanks, Dave
  9. Luis Martin Moderator

    I'm agree. The book made same analisys of # of disk but using Reads a Writes no Transactions.
    Anyway about 300 TPS sound razonable.

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  10. DBADave New Member

    I think I'm still missing something. I looked at the numbers again today and here's what I'm seeing.

    TPS = 60
    Reads/sec = 1.6
    Writes/sec = 3.3

    I believe a transaction is defined as a unit of work consisting of at least one read or write statement. If TPS = 60, that means the system performed at least 60 reads or writes in one second. However, Reads/sec indicates only 1.6 and Writes/sec indicates 3.3 so combined we are talking about 4.9 I/O/sec. Does this mean that at a minimum (60 - 4.9) 55.1 I/O operations (over 92%) consisted of reading data directly from cache?

    Is there a better forumla I should be using to compare RAID performance?

    Thanks, Dave
  11. satya Moderator

  12. FrankKalis Moderator

    please refrain from making duplicate posts [<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  13. FrankKalis Moderator

    mysteriously corrected [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  14. satya Moderator

    On my toes if any discrepancy... from here.

    Satya SKJ
  15. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Frank, to be frank its not my fault ... blame my browser and net settings at office..[8D]<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br />of course, and Microsoft in general [<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />)]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  16. DBADave New Member

    Thanks Satya. I've seen the first link, but the second link is new to me and looks good. Appreciate the help.


Share This Page