My SAN admin wants to put my transaction logs on FAST storage. Should we?

Q: My SAN admin wants to put my transaction logs on FAST storage. Should we?

A: No...

Oh, you wanted more information than that? Okay, here goes.

SQL Server transaction logs are effectively cyclical files where SQL Server writes the transaction log file from the top to the bottom which lots of sequential writes. The big benefit of FAST is that the storage array will take the blogs which are accessed the most and moves them to the fastest storage, which is typically flash drives. Flash drives are awful when dealing with sequential writes. 

Related: Should I be using table partitioning or auto tiering features of my storage array?

Another problem is that because the transaction logs are cyclical we don't really have a hot spot on the log file, we write to all parts of the file evenly so there aren't going to be any hot spots. The storage array will simply take all the blocks and move them to the fastest storage available. 

Now if you have a transaction log for a database which isn't all that busy you might think that FAST might be okay, but I'd still say that FAST isn't a good place for the transaction log. This is because when the blocks which hold the transaction log aren't being used they'll be moved to SATA storage. Then when they are needed they'll be sitting on cheap, slow SATA storage and won't perform very well.

After all, this I would say to put the transaction logs on a more traditional RAID 10 made up of fiber channel disks and let it be.

Please or Register to post comments.

What's Troubleshooting SQL Server Storage Problems?

Practical advice, insight, and help for core SQL Server considerations.

Contributors

Denny Cherry

Denny Cherry is the owner and principal consultant for Denny Cherry & Associates Consulting and has over a decade of experience working with platforms such as Microsoft SQL Server, Hyper-V,...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×