SQL Server Performance

SAN & I/O considerations for SQL Server databases

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by Fais, Jan 25, 2008.

  1. Fais New Member

    As i discussed here earlier that we are going to move in clustering environment soon. For that ,we wanted to buy SAN. But, before doing that, i have some questions, if anyone can provide some answers, that would be great.
    ( A ) what is the recommended I/O rate that we will need for our SQL databases? --> How to find this figure.
    ( B ) What is the recommended number of disk drives that we should place in our SAN to achieve this I/O rate ( For 2 Terra byte data storage)
    ( C ) what is the rate of the I/O that the subsystem needs to be driven at to achieve the recommended I/O rate ( How should i know this, so based on this , we can tell the vendor to provide the disk subsystem minimum of this value)
    I know these question are not straighforward, but if you can give any number ( not neccessarily , Exact answers) (or) any way to find this( Direct way (or) Indirect way). that will be helpful . Because i did not find these answers anywhere .
    Thanks again all for your help.
  2. Luis Martin Moderator

    Check:
    http://www.sql-server-performance.com/articles/per/system_storage_configuration_p1.aspx
  3. Fais New Member

    Ok.
    So based on the above configuration, i should do this. Please correct me here.
    1) First run perfmon on existing server. Gather the stats for the folloing counters.
    (a) Disk Reads/sec == X
    (b) Disk Writes/sec == Y ---> Number of I/Os per second (IOPs) being issued against a particular disk or volume
    (c) Average Disk Bytes/Read ==P
    (d) Average Disk Bytes/Write ==Q --> Which tells us the average size of the I/Os SQL Server is issuing
    • Then based on (a) & (b) , i can tell the hardware vendor that this (X&Y) is the current estimation of I/Os per second issued on the current disk. So, now your new SAN storage should surpass these limits.
    • Based on (c) & (d) , i can tell hardware vendor that, this is the estimated average size of I/Os SQL Server is issuing. So, your new SAN storage should well pass these limits.
    • And then run sqlio.exe on current production ( or ) DR which has same setup. so as to get the value of current IOPS on present server. So , then based on this value, i can tell hardware vendor that, your new SAN storage should be well ahead of this value.
    In btw , what is the basic recommended I/O rate needed for SQL Server databases.
    Looking forward for your advices here.
    Thanks

Share This Page