SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with SQL Server backups slow ( backup performance slower than before after data purge)


Help with SQL Server backups slow ( backup performance slower than before after data purge)

Author
Message
SQL Show
SQL Show
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 1078
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.
sqlsurfing
sqlsurfing
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1185
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 :-D
sqlsurfing
sqlsurfing
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1185
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 :-D
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