none
Performance SQL 2005 RRS feed

  • Question

  • Hello
    If you should choose recovery model on a database just to have max performance, what would you choose. I know of the other issues regarding Simple or Full model (disk, restore to point and.......). But now I'm just thinking of the performance issue. What is the fastest in a database? It's an accounting sw with lot's og reads and writes.
    Friday, December 11, 2009 6:09 PM

Answers

  • Hi,

    The simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. Only for the performance view, simple recovery may be a better solution. However, the simple recovery model risks significant work-loss exposure if the database is damaged. Data is recoverable only to the most recent backup of the lost data.

    To determine the optimal recovery model for a particular database, you should consider both the recovery goals and requirements for the database and whether you can manage log backups. For more information about Choosing the Recovery Model for a Database, please refer to http://technet.microsoft.com/en-us/library/ms175987.aspx.

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, December 15, 2009 10:00 AM
    Moderator

All replies

  • The backup plan should not affect performance at all unless you are running on a pizza box server.  The only time you would see resources really being taken by Full recovery mode is during your full and diffs which will take IO more than the log backups and length of resource time on cpu (minor other utilizations).  however, even in simple recovery you need full backups to the concept you'll get more performance out of taking the log backups out of the mix really doesn't apply.

    Bottom line isn't a performance issue but a recoverability issue.  Adding log backups for a better recovery will not hurt you unless you have no disk other than the disk you have your data residing and a very under powered server.  The only thing I can really think of outside of that is watching overlapping backups but that is planning, not in or out of a process


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Friday, December 11, 2009 6:21 PM
    Moderator
  • No it's not a pizza box server :-)

    But there was another dba who told me that Simple recovery inreases writing to disk as transaction log will do commitment to the database all the time.
    Therefor he ment that Full was better than Simple from a performance view. You say he is wrong?
    Friday, December 11, 2009 7:46 PM
  • Hi,

    The simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. Only for the performance view, simple recovery may be a better solution. However, the simple recovery model risks significant work-loss exposure if the database is damaged. Data is recoverable only to the most recent backup of the lost data.

    To determine the optimal recovery model for a particular database, you should consider both the recovery goals and requirements for the database and whether you can manage log backups. For more information about Choosing the Recovery Model for a Database, please refer to http://technet.microsoft.com/en-us/library/ms175987.aspx.

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, December 15, 2009 10:00 AM
    Moderator