August 3, 2015 at 12:55 pm
Hey all,
I have noticed in my production system, some databases take a lot longer to backup than others. We have been trying to think of what this is (networking, SAN related, etc)..one person brought up the idea of database locks and how it needs to wait for the locks to release.
Is this a logical explanation? Is it recommended to put databases in read only during non production hours to back up bigger databases faster?
Thank you for your advice!
August 3, 2015 at 1:06 pm
Backups don't take locks and aren't affected in any way by locks held by user queries.
Backup time is a factor of read time (IO throughput on the database files) and write time (IO/network throughput on the backup file)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2015 at 3:38 pm
Can you also provide more information?
Do the backups all run at the same time?
How large are the databases? Are some 5 GB and others 100 GB?
Are there databases with large blob fields?
Are you doing backups with the same level of compression?
Are all of the databases and backup locations located in the same place? I.E. are all databases on Server1 on the D drive and you are backing them up to Server1 on the E Drive?
Are there other processes running at the same time as the backups are going on?
August 4, 2015 at 4:39 am
Capture your wait statistics during the backup process. That will tell you what is causing things to run slowly. As Gail explained, it won't be blocks, but it could be other resource contention such as CPU, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 5, 2015 at 8:19 am
Well, I think it was a networking issue. The networking team made some change and now the backups are going at an average of 80 MB/s instead of 2 MB/s....a significant improvement indeed.
If you're curious, they are getting backed up to a CIFS share on a NetApp filer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply