How high is high I/O for SQL Server 2005?

Question

I have a RAID 1 setup averaging 40 - 60 I/Os per disk and 0.5 average disk queue length. According to BOL example below, this is not a bottleneck.

Disk Reads/sec 80
Disk Writes/sec 70
Avg. Disk Queue Length 5

In that case, you are encountering (80 + (2 * 70))/2 = 110 I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border line I/O bottleneck.

Which brings me to my question, how high is a high I/O? The average disk queue length is quite well documented in that it should not exceed 2 per CPU but BOL makes no mention about I/Os per disk?

Answer 1

Quick clarification... "The average disk queue length is quite well documented in that it should not exceed 2 per CPU" should be "The average disk queue length is quite well documented in that it should not exceed 2 per spindle", but that common knowledge is not neccessarily true.

More information on this topic from the Microsoft SQL support team here: http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

As I read your question, you want to know how many IOPS (IO's per second) is high for a hard drive. The answer is "when you have more than the drive supports". a 15K Seagate Cheetah supports many more IOPS than a 5400 RPM laptop drive. In your case, you have the drives setup as RAID 1 (which adds redundancy, but reduces the available IOPS as you write the same data twice).

Courtesy of Wikipedia (http://en.wikipedia.org/wiki/IOPS), some standard numbers:

• 7200RPM SATA drives - ~90 IOPS
• 10kRPM Serial Attached SCSI drives - ~ 140 IOPS
• 15kRPM Serial Attached SCSI drives - ~180 IOPS
• Simple SLC SSD - ~400 IOPS
• Intel X-25 M (MLC; As of March 2009[update], no competition) - ~1,500 IOPS
• Intel X-25 E (SLC; As of March 2009[update], no competition) - ~5,000 IOPS (Intel's data sheet says 3,300 write, 35,000 read IOPS, while 5,000 are measured for a mix)
• ioDrive, a PCI-Express card with Flash - >80,000 IOPS
• DDRdrive X1, a May 2009 PCI Express based solid-state drive - 300,000+ (512B Random Read IOPS) and 200,000+ (512B Random Write IOPS)
• Violin Memory Appliance, 1,000,000+ Random Read IOPS

Answer 2

The Disk Reads/Writes data in itself wouldn't (to me) make sense to have a specific high I/O defined, as hardware advances would render such things obsolete in short order. The key is looking for where things are causing delays, not simply what might be harmless but frequent disk activity.

This is a pretty good article on hardware bottlenecks for SQL Server

Answer 3

Your disk manufacturer should provide info on the kind of IOPS you can expect to handle. Just as important as disk queue is sec/read and sec/write - anything more than 15-20ms sustained is probably gonna hurt.