It is a well-known recommendation and best practice that the transaction log of any database must be on a drive different than the data files are on. This is especially useful to improve transaction log file performance, which manifests itself as a high value for the LOGBUFFER wait type. Refer Pinal’s post (blog) on the LOGBUFFER wait type here. Pinal has demonstrated this at multiple community meets (Tech Ed 2011, CTD – June 2011) and every time he performs the demo, the crowd erupts in admiration.
So, all’s well. But, the question that kept coming back to me was – WHY? Why does moving the transaction log to it’s own dedicated drive benefit the performance of the SQL Server?
To understand the WHY behind this best practice, it is imperative for us to understand the differences in the physical architecture of the transaction log and the data files.
SQL Server uses something called as a Write Ahead Log (WAL) mechanism. What this means is that even before data is persisted to the disk/data file, data is written to the transaction log. When data is written to a database, it moves from the memory (where the manipulation happened) to the transaction log. Later, when background check-pointing happens, this data is written from the log to the data file. Therefore, the data file performance does not directly affect the throughput of the database. The transaction throughput of the database ultimately depends upon the performance of the transaction log.
Since users can read or write any data from the data files, the read & write activity is essentially random in nature. Physically, the read-write heads inside of the disk are jumping around all over the place moving from one sector to another randomly – which slows down the drive, reducing the throughput.
The transaction log on the other hand, is written to serially. This is one of the reasons why instant file initialization cannot be used for transaction logs (refer Paul Randal’s post here), but that’s a different story. Because the transaction log is written to serially, and read from only during check-pointing, a log backup or a restart recovery, it is much more beneficial to place the transaction log on a drive that does not need it’s heads to move around randomly.
This is why moving the transaction log to it’s dedicated drive benefits the SQL Server performance wise.
You can read more on the physical architecture of the transaction log in Books On Line at: http://msdn.microsoft.com/en-us/library/ms179355.aspx
Now that I understand the reason why this arrangement works, I feel much more confident in implementing the same in my development, quality assurance and production environments.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
Yes Nakul,
I have also posted just learned for the same here, [http://beyondrelational.com/justlearned/posts/93/database-files-configuration-to-improve-memory-and-io-performance.aspx][1]
[1]: http://beyondrelational.com/justlearned/posts/93/database-files-configuration-to-improve-memory-and-io-performance.aspx
LikeLike
Thanks, Paresh. Pinal’s session did cover a lot of great performance best practices (as mentioned in my post as well).
However, the focus of the article is not on the best practice, but on the “Why” behind the best practice. Best practices are good, but if we don’t understand the reasons behind them, I don’t think there’s any value in following them – they might end up doing more damage than good.
LikeLike
Excellent Contribution Nakul – I indeed loved it – next time when I demonstrate this cool demo – I will mention your this blog post and link back.
Wonderful! So easy to understand the complex matter when you present it in so simple words.
LikeLike
Thank-you for the feedback, Pinal! It means a lot to me.
LikeLike
Wonderful!
LikeLike
ithe sample question is : transaction log is put to other physics drive or other Logical drive? does logical dirve has any benift ?
LikeLike
Other logical drive will not benefit because ultimately, it’s the same drive controller and the same physical disc. Hence, if the heads are busy doing random reads for a logical drive D, they won’t be able to perform serial writes on logical drive E on the same physical drive.
Hence, the log should be given the priviledge of it’s own separate physical drive.
LikeLike
it’s mean a lot to me,thx , Nakul Vachhrajani ! thx!
LikeLike
A good explanation. Although you must also consider the situation where you have multiple transaction log files for multiple databases on one drive. If they are all active , you can experience IO bottlenecking .
LikeLike
Absolutely – Thanks for pointing that out, Jack.
LikeLike
If all Transaction Logs are moved to the same physical disk (or raid array), is there still a performance improvement?
We have a SAN, currently with 1 VDISK containing both database and Transaction Logs. Would I expect to see a good improvement in performance by creating a second VDISK and moving the Transaction Logs to that?
Many thanks for an enlightening article above…
LikeLike
@grazer: I am afraid the answer is “It depends”. I personally, would prefer a separate physical drive for each database with a high volume of transactions. As part of your tuning exercise, you will need to look at the activity on your transaction log, and then decide whether or not a separate drive is mandated or not. However, generally speaking, yes, you might see some performance improvement.
LikeLike