The Smart Guide to Building World-Class Applications
Thank you for recommending "SQL Server Pro is the leading independent community for DBAs and developers who build and manage Microsoft SQL Server database applications. ". Your recommendation has been successfully processed.
Friend 1
by Denny Cherry
Q: My SAN admin wants to put my transaction logs on FAST storage. Should we?
A: No ...
Oh you wanted more information than that? OK 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.
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 OK, 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.