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:
however if storage location is same( In the same drive and folder), no extra IO involved and hence not much impact on performance.
Best Answer
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:outputs