Recovery Model affect Performance?

skiddoo · 2013-02-03

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

kine · 2013-02-03

It depends. You are writing nothing about your disk setup. And I think this is one from the critical points.

bbrown · 2013-02-04

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.

davmac1 · 2013-02-05

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?

frgusto · 2013-04-11

The short answer is No, recovery model do not affect performance.

Check that transaction log is empty after each log backup.

Alex_Chow · 2013-04-11

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.

bbrown · 2013-04-14

frgusto wrote:

...Check that transaction log is empty after each log backup.

It likely won't ever be in an active system.

frgusto · 2013-04-26

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

bbrown · 2013-04-26

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.

elliswhite · 2014-05-31

hi

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

Recovery Model affect Performance?

Comments

Categories