Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You've still got busses and buffers to consider. I'd keep them separate due to the I/O pattern diffs. If your apps aren't very transactional you'd not likely know the difference, if cost is an issue. –  Eric Higgins Aug 23 '12 at 22:44
    
When you use the term "bus"... are you referring to the path the data takes from the motherboard to the RAID card? –  Chad Decker Aug 23 '12 at 23:05
2  
"is there really any upside to moving the transaction log onto its own separate volume?" almost certainly not, but there is really a speed upside to moving the whole array to RAID10 and reliability upside to over-provisioning (ie under-partitioning) the SSDs –  Jack Douglas Aug 25 '12 at 9:28
add comment

2 Answers 2

up vote 10 down vote accepted

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.

share|improve this answer
1  
@yohal No, it doesn't reinforce your answer. You're espousing the separation of IO patterns due to the huge differences in random vs sequential throughput of spinner disks. The same does not apply to SSDs. –  Mark Storey-Smith Aug 24 '12 at 10:21
2  
@yohal No, just no. Your way is 3+1 disks for checkpoint writes and 3+1 disks for log writes. The alternative is writes distributed across 8 disks, which I and anyone that's implemented and measured this scenario would take for 99% of workloads. –  Mark Storey-Smith Aug 24 '12 at 23:44
1  
@yohal Agreed, I'm certainly not getting your point. In response to your latest comments a) two disks for log would be pointless b) at no point did I suggest putting tempdb on a separate volume. Suggest you visit the heap if you wish to discuss this further. –  Mark Storey-Smith Aug 26 '12 at 23:04
2  
@yohal Both of your links refer to spinner disks, not SSDs. Regardless of the disk type, separation of data/logs/tempdb is very much old school thinking at this scale. I used to follow the same approach but am now inclined to agree with BrentOzar that at < 20 disks, 99% of the time you're better off with a single RAID 10 array for spinners. For SSDs, 99.99% of the the time a single array makes the most sense. –  Mark Storey-Smith Aug 27 '12 at 8:20
1  
@yohal - those two links give only further links, some of which are useless (requiring reg). You should provide some real arguments here. –  dezso Aug 31 '12 at 21:49
show 7 more comments

EDIT:

Thanks for all downvoters, you did an awsome job :-( but only nonsense combined with no knowledge.

Many of the commenters as well as the other responder claim that there is no reason for separating the log from the database even for reguler HDDs, and really I would not even had to answer them, however I still tried to provide resources below.

However there is another 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:

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

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?

share|improve this answer
3  
I'm going to give this a -1 because unless what you are recommending really isn't good advice for the OP in my view - a single array is much simpler to manage and is the obvious answer at this scale - splitting is only for specialized workloads in the real world, even on spinning disks never mind SSDs. –  Jack Douglas Aug 25 '12 at 9:25
1  
All sorts of nonsense is 'recommended' but that does not make it right - people still even claim it is good to split indexes and tables onto separate volumes for example. I'm open to being persuaded by 'proof' however - perhaps you have some benchmarks to share on an 8-disk SSD array? The rationale for splitting logs off is so you are not killing your sequential accesses with scattered io - not really an issue for SSD. Likewise tempdb. –  Jack Douglas Aug 27 '12 at 6:29
1  
Even in the case of spinning disks, splitting is a trade off and will worsen performance on many workloads. –  Jack Douglas Aug 27 '12 at 6:30
1  
The onus is on you to provide proof if you want me to reverse my downvote - I'm more persuaded by this kind of thinking than by MS repeating an ancient mantra personally. –  Jack Douglas Aug 31 '12 at 21:27
1  
@yohal I suspect I'm wasting keyboard clicks here but does this answer regarding tempdb mechanics lead you to question SQL Server dogma in anyway at all? –  Mark Storey-Smith Sep 5 '12 at 0:07
show 21 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.