none
Choosing a Recovery Model for Performance RRS feed

  • Question

  • At the risk of over-simplyfying things, here is how I understand the different recovery models:

    Full: Everything is logged, log is truncated when a backup is taken

    Bulk-logged: Everything is logged bar some bulk operations which receive minimal logging, log is truncated when a backup is taken

    Simple: Everything is logged, log is truncated constantly

    So if the above is true, is there any performance benefit of the simple recovery model over the full recovery model. Or since everything is being logged in both models is performance the same? Perhaps even the simple model performs slightly worse since it is constantly having to the truncate the log?

    Also, how are bulk operations logged in the simple recovery model, is this more similar to the full or bulk-logged model?

    Julia.

    Wednesday, April 7, 2010 10:58 AM

Answers

  • There isn't a performance difference between the recovery models, unless you account for things like the cost to grow the log file if backups aren't taken frequently in Full recovery.  Minimally logged transactions  work the same in Simple recovery as they do in Bulk-Logged.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by JuliaJulia Wednesday, April 7, 2010 11:30 AM
    Wednesday, April 7, 2010 11:02 AM
    Moderator
  • Not in SQL Server it can't.  The transaction log is used in SQL Server to guarantee the atomicity, consistency, isolation and durability of transactions, also known as the ACID properties.  To do this, SQL Server utilizes what is known as the write ahead logging protocol (WAL) which dictates that before a change is made to data in memory (non-volatile storage) it must first be logged to disk (stable storage) to ensure that in an event of a crash, the operation can be redone/undone based on the state of the transaction in the log record.  More information can be found on the following KB Article:

    http://support.microsoft.com/kb/230785

    Tempdb loses the durability part of ACID, but still implements atomicity, consisitency and isolation, so it still performs logging of operations, but since there is no need to redo transactions in tempdb since the database is recreated when the engine starts up, the log doesn't require flushing and transactions commit faster in tempdb than in user databases.  Due to the nature of how tempdb is used, a majority of the operations are not logged, but operations on user objects (temporary tables, table variables, table valued-functions, and normal user created tables) are logged.  Since durability is not required for data changes in tempdb, the after value of a change is not logged for data change operations, which results in less logging for insert/update operations, but the information would never be recovered in the event of a crash since tempdb is recreated at startup.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by JuliaJulia Wednesday, April 7, 2010 3:05 PM
    Wednesday, April 7, 2010 1:37 PM
    Moderator

All replies

  • There isn't a performance difference between the recovery models, unless you account for things like the cost to grow the log file if backups aren't taken frequently in Full recovery.  Minimally logged transactions  work the same in Simple recovery as they do in Bulk-Logged.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by JuliaJulia Wednesday, April 7, 2010 11:30 AM
    Wednesday, April 7, 2010 11:02 AM
    Moderator
  • Thanks, Jonathan - that answers my question perfectly. One last question... is there any way that logging can be switched off if a database is only used for completely volatile data, similar to tempdb? In fact how does tempdb work, are all transactions still logged in the same way as other databases?

    Julia.

    Wednesday, April 7, 2010 11:34 AM
  • Not in SQL Server it can't.  The transaction log is used in SQL Server to guarantee the atomicity, consistency, isolation and durability of transactions, also known as the ACID properties.  To do this, SQL Server utilizes what is known as the write ahead logging protocol (WAL) which dictates that before a change is made to data in memory (non-volatile storage) it must first be logged to disk (stable storage) to ensure that in an event of a crash, the operation can be redone/undone based on the state of the transaction in the log record.  More information can be found on the following KB Article:

    http://support.microsoft.com/kb/230785

    Tempdb loses the durability part of ACID, but still implements atomicity, consisitency and isolation, so it still performs logging of operations, but since there is no need to redo transactions in tempdb since the database is recreated when the engine starts up, the log doesn't require flushing and transactions commit faster in tempdb than in user databases.  Due to the nature of how tempdb is used, a majority of the operations are not logged, but operations on user objects (temporary tables, table variables, table valued-functions, and normal user created tables) are logged.  Since durability is not required for data changes in tempdb, the after value of a change is not logged for data change operations, which results in less logging for insert/update operations, but the information would never be recovered in the event of a crash since tempdb is recreated at startup.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by JuliaJulia Wednesday, April 7, 2010 3:05 PM
    Wednesday, April 7, 2010 1:37 PM
    Moderator
  • Again, a perfect answer - thanks, Jonathan.

    Wednesday, April 7, 2010 3:05 PM
  • There isn't a performance difference between the recovery models, unless you account for things like the cost to grow the log file if backups aren't taken frequently in Full recovery.  Minimally logged transactions  work the same in Simple recovery as they do in Bulk-Logged.

    I thought that bulk-logged can be faster at runtime in exchange for what can be slower backups and a loss in some point-in-time recovery.

    Also, I wonder if you can clarify: in full-recovery mode, is "select into" logged or not?  I have an SP, I changed a create #table and insert to a select into #table, and the reads went down by a bunch.  I thought "select into" was not logged, but BOL seems to say it is.  I'm confused.

    Josh

     

    Wednesday, April 7, 2010 3:32 PM
  • <<I thought that bulk-logged can be faster at runtime in exchange for what can be slower backups and a loss in some point-in-time recovery.>>

    Faster than what?

    Full recovery model? Yes, for the special cases where you can achieve minimal logging, since minimal logging means less resource utilization and that can mean shorexecution time.

    Simple recovery model? No.

    <<Also, I wonder if you can clarify: in full-recovery mode, is "select into" logged or not?>>

    Depends on the recovery mode. IT can be minimally logged in simple or bulk logged. IF you do SELECT INTO into #tmp tables, then the dest table is in tempdb which is always in simple recovery mode.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, April 7, 2010 8:02 PM
    Moderator
  • Tibor,

    I haven't looked at the recovery mode stuff for some time, but isn't the bulk-logged even faster than full recovery if you do several plain updates against the same row, it just puts some flag into the log rather than the whole data row, and then at backup time it has to go and fetch the current status from the actual database?  I never did quite grasp the details of the bulk-logged stuff (obviously).

    On the select into #tmp, yes, someone here actually pointed out that very fact - select into #tmp in tempdb is minimal logged, where insert into #tmp even in tempdb is fully logged in simple recovery mode.

    Thanks.

    Josh

     

    Wednesday, April 7, 2010 8:53 PM
  • Josh,

    Minimally logged operations can be found in the Books Online (http://msdn.microsoft.com/en-us/library/ms191244.aspx).  In addition, the UPDATE statement in the BOL (http://msdn.microsoft.com/en-us/library/ms177523.aspx) shows:

    The UPDATE statement is fully logged; however, partial updates to large value data types using the .WRITE clause are minimally logged. For more information, see "Updating Large Value Data Types" that follows.

    So there is a corner case for LOBs only when you are using .WRITE, but if you primarily use Full Recover, you are going to pay the piper somewhere because your next log backup is going to have to read all these pages from the data file, incurring random IO.  There is a trade off everywhere.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Thursday, April 8, 2010 12:11 AM
    Moderator
  • Jonathan,

    The LOB business sounds like the factoid I had stuck in my head, thanks.

    I last looked at this about three years ago, we were writing some big tables in production, producing big logs, and I was looking for some way out.  Since it was all ephemeral data, generated that way only for export and easily regenerated, I finally decided we should create a second database in simple recovery, and do the nasty work there.  Might not be any faster, but would avoid many gig of logs every day.  No LOBs were involved.

    Thanks.

    Josh

     

    Thursday, April 8, 2010 1:39 AM
  • Josh,

    <<I haven't looked at the recovery mode stuff for some time, but isn't the bulk-logged even faster than full recovery if you do several plain updates against the same row, it just puts some flag into the log rather than the whole data row, and then at backup time it has to go and fetch the current status from the actual database?  I never did quite grasp the details of the bulk-logged stuff (obviously).>>

    Actually, I think you *do* grasp the technology behind bulk_logged pretty well. What you desribe is basically how it works. The operation will go minimally logged, and SQL Server keep track of what extents were modified (so they can be included in next log backup). It is just that minimally logged operations aren't universal. It can only be performed for some special operations (SELECT INTO, bulk loading and CREATE/rebuild/DROP INDEX) - UPDATE isn't one of those operations (except for .WRITE).

    Also, we can in some circumstances achieve minimally logging for INSERT INTO as well, using trace flag 610. Check out http://msdn.microsoft.com/en-us/library/dd425070.aspx for instance.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, April 8, 2010 10:56 AM
    Moderator
  • Well, I don't feel I understand it well enough.  I'm always fuzzy on when it is marking or backing up an "extent", whatever that is exactly, and when it is some logical object or statement or row etc.

    I realize that even under simple and bulk-logging, the log is *always* used so that rollback works.  ... or is even that true, since I guess whatever is "minimally logged" is NOT going to roll back successfully.  So it is *mostly* still used so that most rollbacks still work, mostly.

    If you see what I mean (about my not understanding it well enough, ... in which I presume I am not alone!)

    Josh

     

    Thursday, April 8, 2010 5:39 PM