August 4, 2005 at 12:51 pm
We have SQL Server 2000 SP3. Is there any performance difference using a Simple instead of a Full Recovery Model when a lot of updates are taking place? We are not using T-Logs for recovery purposes so I set out databases to Simple Recovery. Our users perform a lot of updates once a month and I am wondering if the switch to a Simple Recovery Model would negative affect their update processing.
Thanks in advance for you assistance, Kevin
August 4, 2005 at 12:59 pm
I do not believe it will effect the performance where a user would see it.
Recovery model will only pertain to how much information the t-Log holds, and helps you with recovery in emergency situations.
Simple recovery should only be used if the data is fairly static or slowly changing, or if the database size remains small.
Otherwise if your db is large, or changes alot, it will take you alot longer to create backups, and restore databases. Full Recovery gives you much more flexability it a recovery situation.
August 8, 2005 at 1:28 pm
The performance impact is negligable. I believe that on an update, whether your recovery is simple or full, the following actions occur:
1) the data page in memory is updated (and marked 'dirty').
2) transaction log is updated (the transaction is completed).
3) eventually the 'dirty' data page(s) in memory is/are written to disk by the 'lazy writer'.
4a) if recovery is 'simple', upon a 'checkpoint' (a system wide process) and 'completed' transactinos are removed from that database transaction log.
4b) if recovery is 'full', your schedule transaction log backup Job removes all 'completed' transactions and copies them to an OS file.
So, for 'simple' recovery, you add usage to a system wide ersource (checkpoint) but sperad out your log deletions over many executions.
For 'full' recovery you're performing the same actions just less frequently. The difference is that you are not using a system wide resource, but a database specific resource (Job/Scheduled Task). You also incur some additional disk space usage as well.
So the bottom line is that the difference is really, really small in the long run from what I've experiences.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 8, 2005 at 1:29 pm
Please excuse the 'phat phingers' (typos) in the post ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply