Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance, Full Recovery Mode vs Simple Expand / Collapse
Author
Message
Posted Monday, November 26, 2007 3:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 11:59 AM
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.
Post #426078
Posted Monday, November 26, 2007 3:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 20,696, Visits: 32,330
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.




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)
Post #426086
Posted Monday, November 26, 2007 4:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:04 AM
Points: 1,030, Visits: 2,795
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
Post #426108
Posted Tuesday, November 27, 2007 5:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
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.
Post #426324
Posted Tuesday, November 27, 2007 7:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 11:59 AM
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
Post #426362
Posted Tuesday, November 27, 2007 4:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:22 PM
Points: 7,074, Visits: 15,318
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?
Post #426652
Posted Wednesday, November 28, 2007 2:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 2,880, Visits: 3,232
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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #426775
Posted Wednesday, November 28, 2007 8:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:28 AM
Points: 4,362, Visits: 6,204
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
Post #426913
Posted Friday, November 30, 2007 6:35 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 7, 2014 6:15 PM
Points: 86, Visits: 216
"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
Post #428387
Posted Friday, November 30, 2007 7:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 35,259, Visits: 31,740
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #428393
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse