SQL Server I/O Considerations

Notes taken from the SQL Server Summit in Sydney Australia September, 2008.
I/O considerations fall into the acronym: GASP$ – Growth, Availability, Size, Performance and $(Price).
See http://www.microsoft.com/SQL/AlwaysOn for more on availability/failover.
  • Core I/O requirement is stable media (drives/disks) and write ordering (preserve the correct order of operations).
  • It’s important to benchmark I/O equipment before and after deployment of SQL Server!
  • Work with vendors to ensure the latest/appropriate drivers.
  • Understand all I/O/workload requirements, e.g backups, ETL, workload peaks.
  • Consider storage design; split data and log files onto separate drives (or LUNs) and consider the needs of the tempdb-> putting it onto a RAID 1+0 disk may improve overall system performance.
  • Remember: performance varies by vendor!
  • Consider the ramifications of file recovery – cost vs size.
  • Consider multiple filegroups per database and make the prinary FG small (for faster partial recovery).
  • Optimise by spindles and do not always rely on autogrow – use appropriate estimates on growth.
Good perfmon counters to rely on:
Disk Reads & Writes/sec          = #IOs per sec
Avg Disk sec/Read & Write       = Measures disk latency (1-5ms for log, 5-20ms for data OLTP, 25+ms DSS)
Avg Disk Bytes/Read & Write    = size of IOs being issued
Avg Disk Queue Length            =  <=2-4 per physical disk
Disk Write Bytes/sec                = Total disk throughput
Some useful tools to use for I/O benchmarking/testing: 
SQLIOSim.exe – SQLIOSim.exe simulates read, write, checkpoint, backup, sort, and read-ahead activities for
Microsoft SQL Server 2008, 2005, 2000 and 7.0.  Used for testing and troubleshooting SQL Server I/O configurations on x86, x64 and IA64 systems
SQLIO.exe – SQLIO.exe is a utility used to determine the I/O capacity of a given configuration 
  • Consider the use of data compression (SQL Server 2008) when disk space is limited or costly.
Note: Cannot compress XML indexes, LOBs.
Not recommended in conjunction with encrypted data (performance cost).
Major considerations:
  • Work with the HW vendor(s)
  • Fit-for-purpose: no "one size fits all" solution
  • Understand I/O requirements
  • Benchmark I/O before and after SQL installation
  • Monitor ongoing performance (don’t wait until something goes wrong!) 

Leave a comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.