SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance, Full Recovery Mode vs Simple


Performance, Full Recovery Mode vs Simple

Author
Message
Rob-406527
Rob-406527
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 128
In terms of raw performance is simple recovery mode any faster than full recovery mode? Let's not consider the extra time to backup the Tlog. My understanding is that the same amount of logging will take place in either mode. Your just going to use up more space.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95597 Visits: 38968
Which one you use (Simple or Full recovery mode) depends on your restore requirements. Using simple recovery mode, you can only complete full or differential backups. Using the full recovery mode, you add transaction log backups and the ability to restore your database to a specified point in time.

We have done this in our development environment to help identify and correct errors in our production systems. I havene't had to do this in a production environment yet, which I am glad of actually.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Gethyn Ellis
Gethyn Ellis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2797 Visits: 2905
It is also worth remmebering that the mode you chose and the backup strategy you implement will affects how often checkpoints are issued. Which can affect how long SQL Server takes to recover in the event of a system failure.

Gethyn Ellisgethynellis.com
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13331 Visits: 23078
The answer to your questions is basically "No". Even in simple recovery mode, MS SQL writes to the transaction log. In simple recovery mode, it just truncates the log file every time there is a checkpoint so the transaction log file does not grow. All of the overhead of writing to the log file is still there.

It could be argued that as the transaction log grows,, the requests to the OS for more disk space takes additional processing power and drive access, but it is usually not enough to care about.
Rob-406527
Rob-406527
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 128
Michael Earl (11/27/2007)
The answer to your questions is basically "No". Even in simple recovery mode, MS SQL writes to the transaction log. In simple recovery mode, it just truncates the log file every time there is a checkpoint so the transaction log file does not grow. All of the overhead of writing to the log file is still there.

It could be argued that as the transaction log grows,, the requests to the OS for more disk space takes additional processing power and drive access, but it is usually not enough to care about.


Agreed
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29717 Visits: 19006
Rob (11/27/2007)
Michael Earl (11/27/2007)
The answer to your questions is basically "No". Even in simple recovery mode, MS SQL writes to the transaction log. In simple recovery mode, it just truncates the log file every time there is a checkpoint so the transaction log file does not grow. All of the overhead of writing to the log file is still there.

It could be argued that as the transaction log grows,, the requests to the OS for more disk space takes additional processing power and drive access, but it is usually not enough to care about.


Agreed


On the other hand, since full DOESN't truncate the minute it's finished with something, you might have some options to find a "slow" time to do the truncate (i.e. in the middle of the night after the backup). So - you might end up with less disk activity at your busy time of the day, which is then "spread" to a slower time, especially if you have your logs sized correctly (so that they don't have to grow....).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
EdVassie
EdVassie
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14132 Visits: 3901
Simple recovery does not truncate the log at every checkpoint. SQL will always, regardless of recovery mode, keep track of the oldest active transaction. The LRSN of the oldest transaction will relate to a logical log file (see BOL for what this means).
When the current oldest active transaction ends, SQL establishes a new oldest LRSN. If the new LRSN is in a different logical log file to the previous LRSN, SQL releases the previous logical log file (and any intermediate logical log files) for further processing.
Now comes the difference between Simple and Full recovery...
If you are in Simple recovery, the released logical log files are put in the re-use list.
If you are in Full or Bulk Logged recover, the released logical log files are put in a backup-pending status.
Both of the above operations take next to no time to complete, so there is no processing overhead at this point between Simple, Bulk-logged and Full recovery.
Next, if you are in Bulk-logged or Full recovery, at some point you will do a log backup. As each logical log file in pending status is backed up, it is reset to re-usable. The only extra processing overhead for using Full compared to Simple is the time taken to do the log backups. With Bulk-logged, you save some processing time compared to both Full and Simple when you are doing bulk insert operations - BOL has details of this.
The log backup will process all logical log files that are in backup pending or in-use state. If you do multiple log backups during a time when the oldest LRSN has not changed, you will back up the active logical logs multiple times.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32489 Visits: 8675
I am going to go with Matt here in that on a very busy system with off-hours slow time full recovery mode can allow optimal performance during the peak time by not having the tlog flushed automatically in Simple recovery mode. This assumes that you tlog is sized appropriately and you don't get autogrowths during peak time, btw. But everyone should already have that done on their systems anyway.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
DLathrop
DLathrop
Mr or Mrs. 500
Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 Visits: 219
"Minimally logged operations" such as BULK INSERT, BCP.EXE and CREATE INDEX operate as fully logged operations in Full Recovery Mode, but are minimally logged in Simple and Bulk-Logged Recovery Mode. So if you have a read-only reporting database that is periodically bulk loaded, these types of operations will be much faster and use much less transaction log space.

If you go this route, I would recommend having a full backup of your database available before beginning the loading process. If something goes wrong during the process, your only real option is a full restore.

David Lathrop
DBA
WA Dept of Health
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216604 Visits: 41986
Just a suggestion...

If you regularly load large amounts of data using BCP or Bulk Insert, consider making a "staging area" database. Because all data in a staging area should be considered expendable and reproducible from external sources, you should be able to set a "staging area" database to the Simple Recovery Mode without fear. Also, because the data is expendable, there is no need to do data-backups on "staging area" databases nor is there a need to do deletes. Truncates or even drops of tables would be the way to go.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search