Backup is slow with multiple files

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Backup is slow with multiple files

Postby rowlettron » Mon Apr 30, 2012 9:25 pm

I am testing the backup on a test database that is 300GB and lives on a SQL 2005 cluster. When I backup with just one file, the backup is very quick. When I backup with 5 files on 5 different disk resources that are independent of each other, the time for the backup more than triples in duration. Any ideas?
rowlettron
 
Posts: 3
Joined: Mon Apr 30, 2012 9:18 pm

Postby petey » Tue May 01, 2012 9:15 am

Please see http://www.sqlbackuprestore.com/speedingupbackups.htm. Throughput also depends on where your database files are located, not just where the backup files are located. Also see the 'Optimizing backup speed' topic in the SQL Backup help file, which offers some guidance on how to measure and improve your backup throughput.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Backup is slow with multiple files

Postby rowlettron » Tue May 01, 2012 1:36 pm

I tried the link but it returns page not found. Where the data file is located should not make a difference since backing up to a single file goes very fast. But to provide insight on the data file, it is located on an independent LUN that does not share any disks with the backup LUNs.
rowlettron
 
Posts: 3
Joined: Mon Apr 30, 2012 9:18 pm

Postby petey » Tue May 01, 2012 3:32 pm

Please see http://www.sqlbackuprestore.com/speedingupbackups.htm

and also the 'Optimizing backup speed' topic in the SQL Backup help file. It should help you in determining where's the bottleneck when you back up to 5 files.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Postby rowlettron » Tue May 01, 2012 3:42 pm

I looked at your page and I have already configured the database as suggested. It is only when I backup to multiple files using the tool that it slows down. I have done a native sql backup to a single file and multiple files. In both cases, throughput is great. I have backed up to a single file using the tool and throughput is great.

Disk speed - RAID 10 on a SAN. Database is broken up into 12 files with each file being on a separate independent LUN with no shared disks.
Database files - See bullet above.
Really use physically different disks - See first bullet
File fragmentation - Less than 5%
Transaction log - Stored on a separate independent LUN on the SAN
Backup files - Each one is stored on a different independent LUN.
rowlettron
 
Posts: 3
Joined: Mon Apr 30, 2012 9:18 pm

Postby petey » Tue May 01, 2012 4:07 pm

Could you please post some numbers on the speed you are seeing, for both native backups and SQL Backup backups, for backups to 1, 2, 3, 4 and 5 files? Would like to see the increase/decrease for both backup types, as you move from 1 file to 5 files.

Thanks.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests