December 11, 2019 at 1:24 pm
When creating a split backup is it required to have each of the splits go to different path, or can i have them all go to the same location?
December 11, 2019 at 2:18 pm
You mean a striped backup? No, they don't have to go to different paths. Depending on your disk configuration, though, your backup may run faster if they do.
John
December 11, 2019 at 3:30 pm
Thank you.
December 11, 2019 at 4:49 pm
Be advised that if you cannot guarantee that the individual files are guaranteed to go to separate physical spindles (if you're backing up to spinning rust), striped backups can and frequently ("usually" is what my personal observations have been) will take longer (sometimes, a LOT longer) that a single file backup.
If you have SSDs, you don't run into that problem but there's also no real advantage there, either. It could still take longer if your SSDs are setup as "NAS" because the "pipe" becomes the limiting factor there.
But, as Wernher von Braun is quoted as saying, "One good test is worth a thousand expert opinions"... especially since all of this depends a whole lot on the infrastructure of the whole shootin' match.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2019 at 4:54 pm
Be advised that if you cannot guarantee that the individual files are guaranteed to go to separate physical spindles (if you're backing up to spinning rust), striped backups can and frequently ("usually" is what my personal observations have been) will take longer (sometimes, a LOT longer) that a single file backup.
If you have SSDs, you don't run into that problem but there's also no real advantage there, either. It could still take longer if your SSDs are setup as "NAS" because the "pipe" becomes the limiting factor there.
But, as Wernher von Braun is quoted as saying, "One good test is worth a thousand expert opinions"... especially since all of this depends a whole lot on the infrastructure of the whole shootin' match.
Striped backups can go parallel, whereas single-file backups can't (as far as I know) so there may be a performance advantage there. But like you said, Jeff, you won't know until you test.
John
December 11, 2019 at 5:19 pm
The main reason i am using the strip backup if because i am transferring the bak and the main db was 12g bak and our share point was being a pain so i striped it to 3 and had no problem.
December 11, 2019 at 5:33 pm
Make sure you have all the stripes. You need them all for a restore.
December 11, 2019 at 6:20 pm
yep, learned the hard way forgot #3 of 4 lol
December 11, 2019 at 9:35 pm
from my personal testing over the years splitting onto 4 files even if they are on the same destination disk will normally yield best results than 1 or 2 files - more than 4 depends on other factors.
As people will ask.. following was done today in 2 of my servers db files on one drive, backup files on a different drive.
3 different databases (diff sizes) 5 backups done - 1 , 2, 4, 6 and 8 files respectively, 2 runs each
server with 8 cores - 300 GB ram, xTremeIO V2 storage
DB 1
backup database size = 72.30 GB, test = case 1, run = 1, bak files = 1 processed 3097857 pages in 97.884 seconds (247.251 MB/sec).
backup database size = 72.30 GB, test = case 1, run = 2, bak files = 1 processed 3097857 pages in 97.876 seconds (247.272 MB/sec).
backup database size = 72.30 GB, test = case 1, run = 1, bak files = 2 processed 3097857 pages in 44.391 seconds (545.200 MB/sec).
backup database size = 72.30 GB, test = case 1, run = 2, bak files = 2 processed 3097857 pages in 48.430 seconds (499.731 MB/sec).
backup database size = 72.30 GB, test = case 1, run = 1, bak files = 4 processed 3097857 pages in 31.764 seconds (761.931 MB/sec).
backup database size = 72.30 GB, test = case 1, run = 2, bak files = 4 processed 3097857 pages in 29.518 seconds (819.906 MB/sec).
backup database size = 72.30 GB, test = case 1, run = 1, bak files = 6 processed 3097857 pages in 22.236 seconds (1088.415 MB/sec).
backup database size = 72.30 GB, test = case 1, run = 2, bak files = 6 processed 3097857 pages in 22.443 seconds (1078.376 MB/sec).
backup database size = 72.30 GB, test = case 1, run = 1, bak files = 8 processed 3097857 pages in 33.795 seconds (716.141 MB/sec).
backup database size = 72.30 GB, test = case 1, run = 2, bak files = 8 processed 3097857 pages in 31.654 seconds (764.579 MB/sec).
DB 2
backup database size = 66.55 GB, test = case 2, run = 1, bak files = 1 processed 4247609 pages in 114.931 seconds (288.733 MB/sec).
backup database size = 66.55 GB, test = case 2, run = 2, bak files = 1 processed 4247609 pages in 95.704 seconds (346.740 MB/sec).
backup database size = 66.55 GB, test = case 2, run = 1, bak files = 2 processed 4247609 pages in 39.617 seconds (837.631 MB/sec).
backup database size = 66.55 GB, test = case 2, run = 2, bak files = 2 processed 4247609 pages in 60.939 seconds (544.551 MB/sec).
backup database size = 66.55 GB, test = case 2, run = 1, bak files = 4 processed 4247609 pages in 29.842 seconds (1112.004 MB/sec).
backup database size = 66.55 GB, test = case 2, run = 2, bak files = 4 processed 4247609 pages in 28.237 seconds (1175.211 MB/sec).
backup database size = 66.55 GB, test = case 2, run = 1, bak files = 6 processed 4247609 pages in 25.671 seconds (1292.681 MB/sec).
backup database size = 66.55 GB, test = case 2, run = 2, bak files = 6 processed 4247609 pages in 23.857 seconds (1390.972 MB/sec).
backup database size = 66.55 GB, test = case 2, run = 1, bak files = 8 processed 4247609 pages in 23.871 seconds (1390.157 MB/sec).
backup database size = 66.55 GB, test = case 2, run = 2, bak files = 8 processed 4247609 pages in 22.110 seconds (1500.879 MB/sec).
DB 3
backup database size = 43.23 GB, test = case 3, run = 1, bak files = 1 processed 5189745 pages in 105.750 seconds (383.403 MB/sec).
backup database size = 43.23 GB, test = case 3, run = 2, bak files = 1 processed 5189745 pages in 113.228 seconds (358.081 MB/sec).
backup database size = 43.23 GB, test = case 3, run = 1, bak files = 2 processed 5189745 pages in 73.210 seconds (553.816 MB/sec).
backup database size = 43.23 GB, test = case 3, run = 2, bak files = 2 processed 5189745 pages in 57.910 seconds (700.136 MB/sec).
backup database size = 43.23 GB, test = case 3, run = 1, bak files = 4 processed 5189745 pages in 32.390 seconds (1251.771 MB/sec).
backup database size = 43.23 GB, test = case 3, run = 2, bak files = 4 processed 5189745 pages in 40.996 seconds (988.995 MB/sec).
backup database size = 43.23 GB, test = case 3, run = 1, bak files = 6 processed 5189745 pages in 31.525 seconds (1286.118 MB/sec).
backup database size = 43.23 GB, test = case 3, run = 2, bak files = 6 processed 5189745 pages in 27.271 seconds (1486.739 MB/sec).
backup database size = 43.23 GB, test = case 3, run = 1, bak files = 8 processed 5189745 pages in 22.595 seconds (1794.418 MB/sec).
backup database size = 43.23 GB, test = case 3, run = 2, bak files = 8 processed 5189745 pages in 27.328 seconds (1483.638 MB/sec).
Server with 12 cores - 64GB ram - EMC Vnx storage
DB 1
backup database size = 84.65 GB, test = case 1, run = 1, bak files = 1 processed 10918553 pages in 492.992 seconds (173.027 MB/sec).
backup database size = 84.65 GB, test = case 1, run = 2, bak files = 1 processed 10918553 pages in 536.338 seconds (159.043 MB/sec).
backup database size = 84.65 GB, test = case 1, run = 1, bak files = 2 processed 10918553 pages in 401.909 seconds (212.240 MB/sec).
backup database size = 84.65 GB, test = case 1, run = 2, bak files = 2 processed 10918553 pages in 377.671 seconds (225.861 MB/sec).
backup database size = 84.65 GB, test = case 1, run = 1, bak files = 4 processed 10918553 pages in 307.046 seconds (277.812 MB/sec).
backup database size = 84.65 GB, test = case 1, run = 2, bak files = 4 processed 10918553 pages in 311.036 seconds (274.248 MB/sec).
backup database size = 84.65 GB, test = case 1, run = 1, bak files = 6 processed 10918553 pages in 299.390 seconds (284.916 MB/sec).
backup database size = 84.65 GB, test = case 1, run = 2, bak files = 6 processed 10918553 pages in 351.135 seconds (242.929 MB/sec).
backup database size = 84.65 GB, test = case 1, run = 1, bak files = 8 processed 10918553 pages in 295.205 seconds (288.955 MB/sec).
backup database size = 84.65 GB, test = case 1, run = 2, bak files = 8 processed 10918553 pages in 382.069 seconds (223.261 MB/sec).
DB 2
backup database size = 78.55 GB, test = case 2, run = 1, bak files = 1 processed 5914538 pages in 461.714 seconds (100.077 MB/sec).
backup database size = 78.55 GB, test = case 2, run = 2, bak files = 1 processed 5914538 pages in 462.626 seconds (99.880 MB/sec).
backup database size = 78.55 GB, test = case 2, run = 1, bak files = 2 processed 5914538 pages in 381.355 seconds (121.166 MB/sec).
backup database size = 78.55 GB, test = case 2, run = 2, bak files = 2 processed 5914538 pages in 313.254 seconds (147.507 MB/sec).
backup database size = 78.55 GB, test = case 2, run = 1, bak files = 4 processed 5914538 pages in 313.289 seconds (147.491 MB/sec).
backup database size = 78.55 GB, test = case 2, run = 2, bak files = 4 processed 5914538 pages in 360.539 seconds (128.161 MB/sec).
backup database size = 78.55 GB, test = case 2, run = 1, bak files = 6 processed 5914538 pages in 278.769 seconds (165.754 MB/sec).
backup database size = 78.55 GB, test = case 2, run = 2, bak files = 6 processed 5914538 pages in 259.328 seconds (178.180 MB/sec).
backup database size = 78.55 GB, test = case 2, run = 1, bak files = 8 processed 5914538 pages in 259.239 seconds (178.242 MB/sec).
backup database size = 78.55 GB, test = case 2, run = 2, bak files = 8 processed 5914538 pages in 264.809 seconds (174.493 MB/sec).
DB 3
backup database size = 38.33 GB, test = case 3, run = 1, bak files = 1 processed 1596394 pages in 107.363 seconds (116.164 MB/sec).
backup database size = 38.33 GB, test = case 3, run = 2, bak files = 1 processed 1596394 pages in 105.127 seconds (118.635 MB/sec).
backup database size = 38.33 GB, test = case 3, run = 1, bak files = 2 processed 1596394 pages in 103.137 seconds (120.924 MB/sec).
backup database size = 38.33 GB, test = case 3, run = 2, bak files = 2 processed 1596394 pages in 81.863 seconds (152.349 MB/sec).
backup database size = 38.33 GB, test = case 3, run = 1, bak files = 4 processed 1596394 pages in 80.178 seconds (155.551 MB/sec).
backup database size = 38.33 GB, test = case 3, run = 2, bak files = 4 processed 1596394 pages in 82.614 seconds (150.964 MB/sec).
backup database size = 38.33 GB, test = case 3, run = 1, bak files = 6 processed 1596394 pages in 81.441 seconds (153.139 MB/sec).
backup database size = 38.33 GB, test = case 3, run = 2, bak files = 6 processed 1596394 pages in 74.138 seconds (168.224 MB/sec).
backup database size = 38.33 GB, test = case 3, run = 1, bak files = 8 processed 1596394 pages in 90.765 seconds (137.407 MB/sec).
backup database size = 38.33 GB, test = case 3, run = 2, bak files = 8 processed 1596394 pages in 77.376 seconds (161.184 MB/sec).
December 12, 2019 at 3:11 pm
Jeff Moden wrote:Be advised that if you cannot guarantee that the individual files are guaranteed to go to separate physical spindles (if you're backing up to spinning rust), striped backups can and frequently ("usually" is what my personal observations have been) will take longer (sometimes, a LOT longer) that a single file backup.
If you have SSDs, you don't run into that problem but there's also no real advantage there, either. It could still take longer if your SSDs are setup as "NAS" because the "pipe" becomes the limiting factor there.
But, as Wernher von Braun is quoted as saying, "One good test is worth a thousand expert opinions"... especially since all of this depends a whole lot on the infrastructure of the whole shootin' match.
Striped backups can go parallel, whereas single-file backups can't (as far as I know) so there may be a performance advantage there. But like you said, Jeff, you won't know until you test.
John
Just as with queries, there is sometimes a hidden cost in going parallel and it does "Depend" on a whole lot of factors. I worked with one company about 2 years ago where they were using 4 striped files for their backups. It was a "long haul" from an offsite 3rd party provider of equipment (this wasn't a "Cloud") to on premise (and I'm not including any restore time here) and the amount of time the backup was taking was horrible. Reverting to single file cut the backup time to about 1/8th the time.
As we've just seen from frederico_fonseca, he got much different results.
So, yeah... definitely have to test. There is no panacea here.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2019 at 3:14 pm
from my personal testing over the years splitting onto 4 files even if they are on the same destination disk will normally yield best results than 1 or 2 files - more than 4 depends on other factors.
Totally awesome post! Thank you very much for taking the time to gather such information and post it!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2019 at 3:30 pm
If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 12, 2019 at 5:51 pm
If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.
Yup - I did that in a few cases where squeezing any amount of time was paramount - for most cases the effort to try and get the right combination is just not worth the hassle.
December 12, 2019 at 6:05 pm
ScottPletcher wrote:If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.
Yup - I did that in a few cases where squeezing any amount of time was paramount - for most cases the effort to try and get the right combination is just not worth the hassle.
I've seen big improvements, up to a point. Beyond 20 (or 30? been awhile, can't remember for sure) buffers didn't seem to gain anything worthwhile, no matter how large the db.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 13, 2019 at 4:15 am
If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.
Totally agreed on that. I use both.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply