Recovery Model affect Performance?

skiddooskiddoo Member Posts: 19
edited 2014-05-31 in SQL Performance
Hello,

has the Recovery Model an impact on the performance of the SQL Server?

We are using SQL-Server 2008 R2 Entpr Edtn. And we have a heavy performance issue. And it seems to be that this problem occur after we switched from the simple to the full recovery model.

We use LogShipping for Backup / Failover. So the full recovery model is realy necessary. Do we need more RAM for that? (current RAM 22 GB / Creating an Posting 4000 orders per day / 80 users)

Thanks for any response.

Regards
skiddoo

Comments

  • kinekine Member Posts: 12,562
    It depends. You are writing nothing about your disk setup. And I think this is one from the critical points.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • bbrownbbrown Member Posts: 3,268
    With a properly sized transaction log, recovery model would have no impact on performance. After all, the recovery model doesn't change anythign with how SQL retrieves and updates data. Only when the transaction log is truncated.

    However, if your transaction log is undersized and needing to constantly auto-expand, this could have a noticable performance impact. This would likley be most noticable in full recovery, as the need to expand would tend to occur more often.
    There are no bugs - only undocumented features.
  • davmac1davmac1 Member Posts: 1,283
    You have given us the equivalent information as "I have a car, when I added passengers it won't go as fast."
    What other changes did you make - assume the log shipping was added at the same time.
    32 or 64 bit SQL Server?
    Windows O/S version
    Anything else running on SQL Server
    Disk configuration for programs, operating system, system databases, database files, log files, tempdb.
    Number of databases - if more than 1 active database writing to log files on same spindle, you will likely experience a slow down.
    Where is the replicated database?
  • frgustofrgusto Member Posts: 32
    The short answer is No, recovery model do not affect performance.

    Check that transaction log is empty after each log backup.
  • Alex_ChowAlex_Chow Member Posts: 5,063
    frgusto wrote:
    When properly setup, The short answer is No, recovery model do not affect performance.

    Check that transaction log is empty after each log backup.

    Fixed that for you. :D
  • bbrownbbrown Member Posts: 3,268
    frgusto wrote:
    ...Check that transaction log is empty after each log backup.


    It likely won't ever be in an active system.
    There are no bugs - only undocumented features.
  • frgustofrgusto Member Posts: 32
    Ok, thanks bbrown and Alex. Maybe I should clarify.

    A transaction log filled with data can affect performance. So make sure transaction log is backed up regularly.
    And check also, that that the transaction log is close to empty after backup is done. I have seen problems with that the log was not emptied after backup.

    If this is working as expected, then full recovery model do not affect performance.
    Regards Fredrik
  • bbrownbbrown Member Posts: 3,268
    frgusto wrote:
    Ok, thanks bbrown and Alex. Maybe I should clarify.

    A transaction log filled with data can affect performance. So make sure transaction log is backed up regularly.
    And check also, that that the transaction log is close to empty after backup is done. I have seen problems with that the log was not emptied after backup.

    If this is working as expected, then full recovery model do not affect performance.
    Regards Fredrik


    The amount of data in the transaction log has no bearing on performance. What matters is that there's enough free space to write the current transactions without needing to auto-grow the log. Now, having a large number of virtual logs can impact performance. (That's a whole other topic). The transaction log is written sequentially.

    Also it doesn't matter if the backup empties the whole file or not. Just that there's enough free space to support the new transactions. BTW - it doesn't actually clear the data from the file. it just marks the section for re-use. Much the same way a file delete works.
    There are no bugs - only undocumented features.
  • elliswhiteelliswhite Member Posts: 1
    hi

    You must read this SQL tutorial and learn more about recovery models :- http://www.sqlrecoverysoftware.net/blog/sql-database-recovery-model.html
Sign In or Register to comment.