Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 304
SQL Server 303
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

Performance Best practice - Transaction log must on a different drive. But WHY?

Sep 1 2011 12:00AM by Nakul Vachhrajani   

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.

Tags: 


Nakul Vachhrajani
4 · 36% · 11606
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

12  Comments  

  • 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.

    commented on Sep 1 2011 4:19AM
    Nakul Vachhrajani
    4 · 36% · 11606
  • 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.

    commented on Sep 1 2011 10:40AM
    Pinal Dave
    150 · 1% · 326
  • Thank-you for the feedback, Pinal! It means a lot to me.

    commented on Sep 1 2011 11:54PM
    Nakul Vachhrajani
    4 · 36% · 11606
  • Wonderful!

    commented on Sep 13 2011 12:08AM
    goodasong
    1077 · 0% · 24
  • ithe sample question is : transaction log is put to other physics drive or other Logical drive? does logical dirve has any benift ?

    commented on Sep 13 2011 12:12AM
    goodasong
    1077 · 0% · 24
  • 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.

    commented on Sep 13 2011 10:35AM
    Nakul Vachhrajani
    4 · 36% · 11606
  • it's mean a lot to me,thx , Nakul Vachhrajani ! thx!

    commented on Sep 13 2011 6:51PM
    goodasong
    1077 · 0% · 24
  • 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 .

    commented on Sep 14 2011 12:33AM
    Jack Vamvas
    5 · 26% · 8528
  • Absolutely - Thanks for pointing that out, Jack.

    commented on Sep 14 2011 1:54AM
    Nakul Vachhrajani
    4 · 36% · 11606
  • 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...

    commented on Oct 17 2011 3:25AM
    grazer
    3047 · 0% · 2
  • @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.

    commented on Oct 19 2011 9:40PM
    Nakul Vachhrajani
    4 · 36% · 11606

Your Comment


Sign Up or Login to post a comment.

"Performance Best practice - Transaction log must on a different drive. But WHY?" rated 5 out of 5 by 4 readers
Performance Best practice - Transaction log must on a different drive. But WHY? , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]