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.