0

I recently came to know about concept of split backup in MSSQL Server whereas I have been working on stripe backup in Sybase ASE.

Stripe backup in Sybase ASE is faster than normal backup and if we use three stripes then Sybase will use 3 cores of CPU(Depends on number of cores) as it will be split into that many threads, are performed in parallel irrespective of storage location.

I wanted to understand if same is the case with MSSQL split backup, I read that if we use different locations for split backup then the backup will be performed in parallel and will be faster:

Split Backup

however if storage location is same( In the same drive and folder), no extra IO involved and hence not much impact on performance.

1

Any speedup in a striped backup will come from using multiple IO channels. If all the files are in the same storage location it won't be any faster.

Using multiple CPU cores to process a backup probably won't help, as backup is extremely simple. And a single CPU core can saturate the storage subsystem with backup pages.

You can see this for yourself with any big database, by writing the backups to the nul device:

set statistics time on
go

print '

single backup'


backup database AdventureWorks
to disk = 'nul'
go

print '

striped backup'

backup database AdventureWorks
to disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul',
   disk = 'nul'

outputs

single backup

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Processed 651384 pages for database 'AdventureWorks', file 'AdventureWorks2017' on file 1.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks2017_log' on file 1.
BACKUP DATABASE successfully processed 651386 pages in 2.837 seconds (1793.779 MB/sec).

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 2877 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.


striped backup

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Processed 651384 pages for database 'AdventureWorks', file 'AdventureWorks2017' on file 1.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks2017_log' on file 1.
BACKUP DATABASE successfully processed 651386 pages in 4.023 seconds (1264.964 MB/sec).

 SQL Server Execution Times:
   CPU time = 499 ms,  elapsed time = 4123 ms.
  • Browne - Are you suggesting that Split backup will be slower than backup taken in one file? – Learning_DBAdmin Mar 11 at 13:59
  • It might be slower, yes, as there is a non-zero amount of extra work. – David Browne - Microsoft Mar 11 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.