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!) 

About Rob Sanders

IT Professional and TOGAF 9 certified Enterprise Architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

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