Recovery Model affect Performance?

skiddoo

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

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

bbrown

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

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

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

Check that transaction log is empty after each log backup.

Alex_Chow

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

frgusto wrote:

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

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

frgusto

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

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

hi

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