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 12»»

Performance issues in FULL recovery model? Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 7:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 53, Visits: 290
Hi Experts,

Just wanted to know whether there would any kind of Performance issues if I change the recovery model from Simple to Full.

Regards,
Post #1566367
Posted Wednesday, April 30, 2014 7:21 AM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 40,438, Visits: 36,895
Recovery model has nothing to do with performance. It's about recoverability.

If you need to be able to restore a DB to point-in-time, you need to be able to restore to point-of-failure, then you need full recovery and log backups. If restoring to the last full backup in the case of a disaster is acceptable, then simple recovery is probably fine.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1566385
Posted Wednesday, April 30, 2014 7:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 53, Visits: 290
Thanks just wanted to confirm as the end users usually complain for slowness when the DB is in FULL mode.

Post #1566390
Posted Wednesday, April 30, 2014 7:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 40,438, Visits: 36,895
Users will complain, that's the steady-state of the universe.

Identify the actual cause of the performance problem and work on fixing that. Then the users might stop complaining for a nanosecond or two.

This may be worth a read:
https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1566396
Posted Wednesday, April 30, 2014 7:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 509, Visits: 1,885
[CaptainObvious]Don't forget to back up your transaction log if you do[/CaptainObvious] .
Post #1566406
Posted Wednesday, April 30, 2014 8:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 14,034, Visits: 28,406
There is a tiny impact on performance because you're using resources to backup the logs. But it's really tiny. If you're already at 99% resource usage, adding this might be an issue. But the answer is not to skip the ability to do a point in time recovery, the answer is to address why you're using 99% of your resources.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566432
Posted Wednesday, April 30, 2014 9:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:38 PM
Points: 3,939, Visits: 8,935
Grant Fritchey (4/30/2014)
There is a tiny impact on performance because you're using resources to backup the logs. But it's really tiny. If you're already at 99% resource usage, adding this might be an issue. But the answer is not to skip the ability to do a point in time recovery, the answer is to address why you're using 99% of your resources.

Unless you're not doing log backups, the logs are constantly growing and no one cares about PIT recovery. Sounds nuts, but I've seen it.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1566454
Posted Wednesday, April 30, 2014 10:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 14,034, Visits: 28,406
Luis Cazares (4/30/2014)

Unless you're not doing log backups, the logs are constantly growing and no one cares about PIT recovery. Sounds nuts, but I've seen it.


Until all performance stops because the drive is full. Oh yeah, I've seen it too.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566491
Posted Wednesday, April 30, 2014 11:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:04 PM
Points: 30, Visits: 477
Is there any bulk-load operation(s) occurring in the database?
Post #1566534
Posted Wednesday, April 30, 2014 4:27 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 30, 2014 11:23 AM
Points: 115, Visits: 400
well, in some cases it could have some performance impact depending on the workload, disk subsystem,...assuming you've also set the log backup jobs on your databases, but this doesn't mean you should set the recovery model to simple to improve performance , in rare cases that could be an option though.

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008
Post #1566622
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse