June 13, 2009 at 5:06 pm
When data is read / written to the database it is recorded in the transaction log, does this mean that if the transaction log is slow, then the database could have performance issues?
Or do database transactions not wait for the log to be written to and the transaction log is written to when possible.
Is this the reason why SIMPLE recovery mode is alot faster because not every transaction is recorded? could someone please clarify. Thanks.
June 13, 2009 at 5:41 pm
Hello,
Hopefully this article will help answer your questions:-
http://articles.techrepublic.com.com/5100-10878_11-5173108.html
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 13, 2009 at 9:45 pm
The log is always written to first, before data changes are made. So if the log is slow, meaning the I/O channel, then performance can suffer.
June 13, 2009 at 9:49 pm
Even in simple recovery mode, all your transactions are written to the log file. The only difference here is that the log is cleared everytime SQL Server fires a checkpoint wherein all committed transactions are written to the database by lazy writer. Space occupied by the committed transactions are released back to be reused.
June 14, 2009 at 12:42 am
Trashcan2007 (6/13/2009)
When data is read / written to the database it is recorded in the transaction log, does this mean that if the transaction log is slow, then the database could have performance issues?
Yes.
Or do database transactions not wait for the log to be written to and the transaction log is written to when possible.
No the transaction log is written to first and the transaction has to wait for it.
Is this the reason why SIMPLE recovery mode is alot faster because not every transaction is recorded? could someone please clarify. Thanks.
SIMPLE Recovery mode is not "a lot faster", except, sometimes, for certain bulk operations (like SELECT into). The difference between FULL and SIMPLE recovery models is that SIMPLE will deallocate its usage of the log after the transaction is complete so that it can be reused. Also, SIMPLE cannot actually Recover your database from backups, only restore it from backup.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 14, 2009 at 2:36 am
Thanks everyone for the reply, that's certainly cleared things up.
June 14, 2009 at 10:42 am
Glad we could help!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply