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 forMicrosoft SQL Server 2008, 2005, 2000 and 7.0. Used for testing and troubleshooting SQL Server I/O configurations on x86, x64 and IA64 systemsSQLIO.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!)
