Post reply

Calculate IOPs for production server?

  • January 13, 2014 at 9:49 am

    #396099

    We have SQl Server in the production with dozens databases that reside on a SAN in the RAID 1 configuration with SAS 10K disks. Few databases have a huge overload on the SAN (more writes than reads). As there are some performance issues we are considering new SAN.

    Is there any way we can get average IOPs in this server? I tried to use PerMon but without success. I don't know if I can chose "Total" for Logical drives in PerMon for the counters? I read dozens of pages: use sqlio, take this counters, use that, but no straight way to calculate. I know that SAN could have the relevant info for IOPs but our old one doen't provide it.

    So please, if somone knows how can I calculate it (without use these links or use this counters with no calculation procedure in the end :)) it would be great.

    Thanks in advance.

  • January 13, 2014 at 4:54 pm

    #1679755

    So you want to measure IOPs without looking at performance counters?

    Or you want to measure max IOPs that the storage is capable of, but without using SQLIO?

    What is wrong with these approaches? Not sure if I fully understand what you are trying to achieve in that case.

    And don't just look at IOPs. Throughput and latency are also important. And slow I/O may be unrelated to storage performance; there are so many other factors to consider.

    I've written more about it here SQL Server IO Performance


    Check Your SQL Servers Quickly and Easily
    www.sqlcopilot.com

  • January 13, 2014 at 5:15 pm

    #1679759

    Let's put it simpler: I have production SQL Server and need to measure average IOPs so I can say to possible SAN vendors that I need minimmum XXX IOPs for my server.

    - What is the calculation/measurement procedure: Can I use PerfMon and if I can what are the specific counters and how to use it to make the calculation

    - Can I use any other tool for mesurement

    - Or there are many other factors that are affecting on the server performance.

    Simple: SAN vendor asks us what RAID we want and what number of IOPs we need for our SQL Server.

  • January 14, 2014 at 4:05 am

    #1679835

    For IOPS you need to look at the Physical Disk Reads/sec and Writes/sec.

    Brent Ozar wrote a post over 6 years ago about how to capture these values and use Excel to get averages, maximum, etc.

    www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning[/url]

    I know that SAN vendors and storage engineers tend to fixate on IOPs, but don't forget throughput as well. For this you can look at Physical Disk Read Bytes/sec and Write Bytes/sec.


    Check Your SQL Servers Quickly and Easily
    www.sqlcopilot.com

  • January 14, 2014 at 7:19 am

    #1679885

    I use both sqlio and crystal disk mark http://crystalmark.info/software/CrystalDiskMark/index-e.html

    I can understand needing to find a min #of iops needed, it seems like most storage sales representatives need this number to begin, then you can dig into throughput and latency with the sales team storage engineer. (also sayings EYE OPS sounds kind of cool).

    Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale

  • January 16, 2014 at 12:18 am

    #1680527

    Thanks guys for the help. I managed to find the nedeed informations.

  • February 5, 2015 at 9:58 am

    #1775769

    I have the same issue and I would be interested in learning what you finally did to get the IOPs numbers?

  • April 22, 2015 at 3:57 pm

    #1792875

    Hi ApploDB,

    Thanks guys for the help. I managed to find the nedeed informations.

    Can you pleas let me know how to calculate IOPS on my current SQL Server in production?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply