Click here to monitor SSC
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 Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24206 Visits: 37967
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)
GRE (Gethyn Ellis)
GRE (Gethyn Ellis)
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1066 Visits: 2840
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 Ellis

gethynellis.com
Michael Earl-395764
Michael Earl-395764
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2707 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 Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 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)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18082
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3146 Visits: 3816
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. 1 Dec 2016: now over 39,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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5963 Visits: 8312
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 at GMail
DLathrop
DLathrop
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45113 Visits: 39916
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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