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.
ALTER INDEX myindex ON mytable REBUILD WITH (DATA_COMPRESSION = PAGE)
ALTER TABLE mytable REBUILD WITH (DATA_COMPRESSION = ROW)
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 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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>