none
SQL server disk I/O

    Question

  • Hi,

    We have a  server having 24 cores(Intel Xeon) and 192 GB of ram.

    the server hosts SQL server 2008 R2.

    and we are hosting our datawarehouse over there. our etl process causes 6-89Mb/s of database I/O on the server. but the technical team claims that at any point there shouldn't be an database i/o of more that 1.5MB/s.

    Can someone please suggest me the ideal database I/O for this configuration


    Arjun Kishore
    Tuesday, January 24, 2012 6:50 PM

Answers

  • The amount of I/O will be as much as your ETL needs it to be so I wouldn't worry too much about Mb/s as a metric or trying to get it down for the sake of it.  ETL loads will naturally be more intensive than normal OLTP loads if that's what your guys are comparing it with - although there's always ways and means to make your ETL more efficient should you need to.

    If you're experiencing performance issues that you think are disk-related then the counters you should probably pay most attention to are for how much latency you're getting on your disks - "Avg. disk sec /Read" and  "Avg. disk sec /Write".  Just do a search for "SQL server disk latency" and you'll find plenty of articles / blogs by MVPs with guidelines for what values you should be aiming for on these.

    • Marked as answer by ArjunPK Monday, February 06, 2012 12:09 PM
    Tuesday, January 24, 2012 9:35 PM
  • test your Storage IO first and get the breaking point of it to make sure that you're aware of your MAX iops.

    use SQLIO for this http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163

    and here's a vieo describing how to use it

    http://www.youtube.com/watch?v=aF2_rmyrbLU&feature=results_video&playnext=1&list=PL75ADCB807250739F

     


    Wagdy Ishac www.sqldair.com
    • Marked as answer by ArjunPK Monday, February 06, 2012 12:09 PM
    Wednesday, February 01, 2012 8:40 AM

All replies

  • The amount of I/O will be as much as your ETL needs it to be so I wouldn't worry too much about Mb/s as a metric or trying to get it down for the sake of it.  ETL loads will naturally be more intensive than normal OLTP loads if that's what your guys are comparing it with - although there's always ways and means to make your ETL more efficient should you need to.

    If you're experiencing performance issues that you think are disk-related then the counters you should probably pay most attention to are for how much latency you're getting on your disks - "Avg. disk sec /Read" and  "Avg. disk sec /Write".  Just do a search for "SQL server disk latency" and you'll find plenty of articles / blogs by MVPs with guidelines for what values you should be aiming for on these.

    • Marked as answer by ArjunPK Monday, February 06, 2012 12:09 PM
    Tuesday, January 24, 2012 9:35 PM
  • test your Storage IO first and get the breaking point of it to make sure that you're aware of your MAX iops.

    use SQLIO for this http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163

    and here's a vieo describing how to use it

    http://www.youtube.com/watch?v=aF2_rmyrbLU&feature=results_video&playnext=1&list=PL75ADCB807250739F

     


    Wagdy Ishac www.sqldair.com
    • Marked as answer by ArjunPK Monday, February 06, 2012 12:09 PM
    Wednesday, February 01, 2012 8:40 AM