SQL Server Performance Forum – Threads Archive

How to Calculate IO Operations Per Disk

This is a followup to an earlier post from September. When comparing RAID 5 to RAID 10 I am using the following formulas: RAID 5 # of Read Requests + (4 x # of Write Requests) / # of disks RAID 10 # of Read Requests + (2 x # of Write Requests) / # of disks Do I determine the number of read and write requests as follows? Reads = Disk Reads/sec * Transactions/sec Writes = Disk Writes/sec * Transactions/sec I want to make sure I am using the correct Perfmon counters. TPS = SQL Server<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />atabases Transactions/Sec (for all databases) Reads/sec = PhysicalDisk Disk Reads/Sec Writes/sec = PhysicalDisk Disk Writes/Sec I see the following numbers from these counters TPS = 7,500 Reads/Sec = 2.3 Writes/Sec = 3 Reads = 7,500 * 2.3 = 17,250 Writes = 7,500 * 3.1 = 23,250 RAID 5 I/O Operations per Disk = 17,250 + 4(23,250) / 6 = 18,375 RAID 10 I/O Operations per Disk = 17,250 + 2(23,250) / 6 = 10,625 Am I doing something wrong? Do I need to divide the TPS by the number of processors? Dave

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

the formula is for physical disk reads and writes as read from perfmon assuming you have a hardware RAID controller. RAID 5 # of (PhysicalDisk Disk Reads/Sec) + (4 x # of PhysicalDisk Disk Writes/Sec) / # of disks RAID 10 # of (PhysicalDisk Disk Reads/Sec)+ (2 x # of PhysicalDisk Disk Writes/Sec) / # of disks 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. 7,500 SQL Server<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />atabase Transactions/sec sounds way to high to me. do most of your requests involve transactions? SELECT queries should register as a transaction. usually, i just monitor SQL Server<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />QL Statistics – Batch Requests/sec which gives all requests issued to the server, transaction or no

Luis, 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. Dave

Joe, 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

Joe, 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

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

Luis, 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. http://databasejournal.com/features/mssql/article.php/1460091 Thanks, Dave

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

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

May take info. from thishttp://www.databasejournal.com/features/mssql/article.php/1460091 article. (oops, I should’ve read your post Dave… it seems same info. you got) And may take help from this Linkhttp://www.winnetmag.com/Articles/Index.cfm?ArticleID=3682&pg=3 now. Frank, to be frank its not my fault … blame my browser and net settings at office..[8D] _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

please refrain from making duplicate posts [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

mysteriously corrected [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

On my toes if any discrepancy… from here. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

<blockquote 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] <hr height="1" noshade id="quote"></blockquote id="quote"> of course, and Microsoft in general [<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

‘ />)] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

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

]]>