Placing Transaction Log on Separate Volume [solid state]?

Question

Transaction logs are often isolated on a separate volume. The rationale for this practice, as I understand it, is that the transaction log's data is written sequentially -- and hard drives can execute write operations with far greater speed sequentially as opposed to randomly. This is due to the little needle inside the drive which has to move a much shorter distance when writing sequential blocks of data, as opposed to random writes.

(Sorry for the naive interpretation. Just trying to make sense of what I've read.)

With this in mind… It occurs to me that solid state drives don't have little needles and platters and stuff moving around inside of them. If my database and transaction log are both located on a single RAID 5 of eight solid state drives, is there really any upside to moving the transaction log onto its own separate volume? If the supposed efficiency boost is based upon the premise of sequential writes reducing the distance that needles move and platters spin, and a solid state drive has none of these moving parts, what do I gain by isolating the log?

Answer 1

Short answer, use a single array, there is unlikely to be any performance gain from separating logs from data across 8 SSD drives. See SQL on SSDs: Hot and Crazy Love for a more detailed (and entertaining) commentary on SSDs. Pay particular attention to the notes on correlated failures of SSDs.

Separating logs from data on SSDs is more an RPO (recovery point objective) than performance issue. The notion being that you could reduce your RPO by separating logs from data such that in the event of the data array failing, your log array should/could remain accessible. The cautious would consider a different make/model of drive in each of the two arrays to mitigate the correlated failure issue if RPO was critical.

The comments regarding bus bandwidth are irrelevant. If you need to shift that much IO, you've got bigger issues to worry about.

Answer 2

There is a group that agrees that on an HDD it would be beneficial to separate, they still claim that for SSD drives this is no longer needed.

So for them I want to ask "if there is no issue of contention then why a RAID 10? There is no need for stripping anymore! So mirrowing alone would be enough, and of course there is no need for 8 drives, 2x the database size should suffice!".

However the reality is that if something needs a RAID 10 it is the log file!

This is not just because of the issue of sequential vs random (see resources below), but it is actually very crucial once you understand how SSD drives work.

To make a long story short (for a longer description see http://arstechnica.com/information-technology/2012/06/inside-the-ssd-revolution-how-solid-state-disks-really-work/), an SSD drive is very effecient in reads, and in writing out zeros, however to write out ones it is not so efficient as it has to erase the entire section to write even a single one!

While this is not an issue for general writes, as they are anyway buffered in memory, and written out in page boundaries, it is a major issue for the log file, as the log file is bypassing any cache and instead SQL server blocks until the logs are written to disc!, which means that for each write there will probably be a full section erase.

So to optimize it, I would suggest to dedicate every extra disc (besides 2x the database size, no need for stripping!) for the log file, this way it will be able to process as many as possible in a shorter time frame.

OLDER ANSWER

The answer is yes, for three reasons.

1. Random vs Sequential - While it is clear that SSD increased performance dramatically for random writes, still the issue of random vs sequential remains, as can be seen from the following whitepapaers and links:

• http://research.microsoft.com/pubs/63596/USENIX-08-SSD.pdf
• http://static.usenix.org/event/fast12/tech/full_papers/Min.pdf
• https://stackoverflow.com/questions/2100584/difference-between-sequential-write-and-random-write
• http://www.neowin.net/forum/topic/992800-why-sequential-vs-random-io-speeds-differ-for-ssd/

2. Reliability - There is a strong chance that all SSD drives will fail simultaneously, in which case RAID is no protection, however since an SSD drive used solely for sequential has a different life span this might be your lifesaver

3. Write Contention - The reason for putting logs on its own spindle is not just because of random vs sequential but also because of write contention, as one can see from the fact that it is also recommended to have tempdb on a separate volume which indicates that the issue here is also about write contention.

And this should apply even more for the log file, as writes to the log blocks transactions from being considered commited until it is written to the disk surface.

In fact for the logs you might use regular HDD drives as apr Dell's white paper at http://www.dell.com/downloads/global/products/pvaul/en/ssd_vs_hdd_price_and_performance_study.pdf

EDIT

Putting tempdb on its own array for spinning discs is recommended by Microsoft, see

• MSDN - http://technet.microsoft.com/library/Cc966545 (section "Best practices and recommendations")
• MSDN - http://msdn.microsoft.com/en-us/library/ms175527(SQL.105).aspx
• Sunil Agarwal (SQL Server Storage Engine Team Member) - Link
• Paul Randal (By linking to the above link as a useful link) - http://www.sqlskills.com/BLOGS/PAUL/category/tempdb.aspx
• StackOverflow - https://stackoverflow.com/questions/360088/sql-server-tempdb-optimization-tips-for-a-new-server
• ServerFault - https://serverfault.com/questions/19866/recommended-disk-partition-setup-for-a-sql-server
• "Microsoft Sql Server Management And Administration (SAMS)" (page 33)

and numerous others and it is the general accepted notion in Sql Server, while no one expressed a problem with splitting the array.

Further more the SQL Server team has created the concepts of Filegroup and Partioining partitioning, with the sole intention to be able to move them on a separate array.

And in fact the MSDN at http://msdn.microsoft.com/en-us/library/ms187087(v=sql.105) recommends that there might be a performance benefit from separating the nonclustered index on its own array, (though this shouldn't be taken as a general advice for every situation, only for specific workloads, see more info at http://weblogs.sqlteam.com/dang/archive/2008/08/01/Are-you-a-DBA-Monkey.aspx).

As such it is just a logical extension to say the reason for separation on spinning discs is not just tied to the issue of sequential vs random reads, but to general write contention, something that applies to SSD's as well.

While it might be that some people disagree with that advice and consider that there is no benefit of putting tempdb and its own volume (as Jack Douglas), and you might even claim that there is no benefit from separating the log files (as Mark Storey-Smith), and instead claim that splitting the array is much more worse, still don't forget that this is a new approach going against the general accepted approach suggested by Microsoft and the community, and so far no one has provided links to any benchmark tests to support it.

So my word to all downvoters is, I find it very unethical to downvote a post just because it has a different opinion than yours, especially when 1) your opinion is going against the general accepted theory 2) and it is against the vendors (Microsoft) own documentation 3) and you haven't provided any proof just an opinion.

But in this case it is even more ridiculous, since my post is nothing more than the logical extension to this theory, so one that considers this post to be bed advice needs of course to go back to all posts that advice this theory and downvote them.

Say someone decides that RAID is old school theory and downvotes all posts recommending it, how does this makes sense?