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

Help with SQL Server backups slow ( backup performance slower than before after data purge) Expand / Collapse
Author
Message
Posted Wednesday, May 08, 2013 4:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
What is the auto growth of your lDF file?

I am about to blame the number of VLF files in your database. Your Purge might be adding lots of small sized vlf files which in turn , may cause the backup process run slow. Backup process obviously "reads" ldf file, large numbr of small size vlf files will delay.

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

Next time you do purge, do Pre size your ldf file accordingly.

Post #1450482
Posted Wednesday, May 08, 2013 9:29 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 106, Visits: 771
You can also test the if the bottleneck is coming from the backup disk or your data disk.
Backup to nul to test backup read speed, if it doesn't change (or is even quicker) then you know you have an issue with the backup disk you are writing to.
It's a simple test to close some doors.

BACKUP DATABASE [MyDB] TO DISK = 'nul' WITH COPY_ONLY

Copy_only to not break your log backup chain.

This is usually quicker than a regular backup since the bottleneck is often the write speed.

You can also play around with the number of stripe, BUFFERCOUNT and MAXTRANSFERSIZE (Books Online).


Thanks for suggestion, that's a good one, I'd forgotten about backing up to 'nul' - will try that out this week!


--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
Post #1450872
Posted Wednesday, May 08, 2013 9:50 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 106, Visits: 771
SQL Show (5/8/2013)
What is the auto growth of your lDF file


I am about to blame the number of VLF files in your database. Your Purge might be adding lots of small sized vlf files which in turn , may cause the backup process run slow. Backup process obviously "reads" ldf file, large numbr of small size vlf files will delay.

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

Next time you do purge, do Pre size your ldf file accordingly.



Autogrow set to 1GB increments. (That will get addressed in future)

Interesting to point out number of VLFs could be the issue, but prior to all the multiple purges the number of VLFs was around 200s though (backup times back then were under 65mins),

After purge it's around 200s also, so not much change, in number of VLFs, I completely ruled it out....But now I'd like to give that a try just to see, because any ideas are worth a shot. Thanks for suggestion, worth a try - wouldn't have considered without your suggestion


--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
Post #1450875
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse