SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

T-SQL Tuesday – I/O Fallacies

It’s time for the March T-SQL Tuesday and this month Mike Walsh picked the topic. It’s I/O and you can read about his idea on the Straight Path Solutions blog.

I am not really a hardware guy. I like it, and it can be interesting, and in the past I spent a lot of time learning about SCSI, PCI v EISA, RAID, etc. and it’s impact on SQL Server. However about the time that SANs became popular I found that I couldn’t keep up with all the technologies that were coming out and it wasn’t a part of my job. The companies for which I worked typically had someone assigned to deal with hardware, and often just storage.

So I let them figure out what hardware we needed, and what implementation. My only requirements were that I have xxx sized drives (or arrays/LUNs) for my SQL Server to separate out data from logs or backups. I would even let them know that we wanted to support yyy reads/sec of zzz size, or similar specs so that they could design the subsystem.

One day we got a new SAN.

I needed new storage for a SQL Server and soon the storage guy came to talk to me. He said that the vendor basically set up the entire SAN as one large array of disks and he would just carve out xx GBs for each of my drives. I wasn’t pleased with that and requested a meeting with the vendor. I got 2 sales guys and a sales engineer to talk to me, none of whom had ever really spent time using the product and were repeating what their engineers had (supposedly) said.

Needless to say I disagreed with them, but I let them do things their way and we ended up with performance problems at times on the SQL Servers connected to the SAN. Not all of them, but a few that were very busy at times had delays getting I/O satisfied if other servers also needed lots of I/O.

I left shortly thereafter, so I’m not sure what happened with those servers. However I think the lesson here is that a SAN doesn’t magically create better storage. A SAN can often handle higher I/O loads overall than DASD (Direct Attached Storage Devices), but it’s a balancing act for the SAN. Like an Ethernet network, often the SAN is counting on a distribution of I/O loads. Not all servers needing lots of I/O at the same time.

Or if they are, that the write cache can handle things, or the servers will retry their requests (or wait). For file server type operations this can work great. But for SQL Server it can be an issue. And write caching transactions that SQL Server expects to be on disk and permanent, or Durable in ACID terms, can lead to corruption.

Be careful of thinking a SAN can remove all I/O worries from your storage design. Good fundamental disk practices are still required.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Gethyn Ellis on 9 March 2010

I have to agree Steve, SAN's are definitely not a magic wand for IO problems, they can be very useful and there are lots of benefits but you still need to follow best practice and a vendors 'one size fits all' is not necessarily the way to go.

Posted by Jason Brimhall on 9 March 2010

Thanks Steve.  If SANs are a magic wand, then maybe a broken magic wand would be accurate.  They might work magic - they might not.  They still need to be configured correctly and the database still needs to be tuned.

Posted by Steve Jones on 9 March 2010

Thanks for the comments. I think so many people early on in the SAN world thought a SAN would provide so much IO that they could put anything on it. I think we realize now that it was a case of hardware overwhelming the issue temporarily. Good tuning is still needed on your disk system.

Leave a Comment

Please register or log in to leave a comment.