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.