SQL DB restore how to configure parallel threads

[johannesk]

Hi,

I was restoring a database that has 4 data files and 1 log file. The restore process writes 3 of the datafiles and the log file in parallel. Then when one of the 4 files have been written down, the restore of the 4th datafile starts.

Thus it's doing 4 streams. Can I configure it somewhere so the restore process does all the 5 files at once?

Regards,
Jóhannes

[matteu]

Hello,

Does these files are all on the same virtual disk ?
If not, it's mabe because you reach proxy task maximum number.

[johannesk]

The log is on different virtual disk from the other 4 datafiles. The max task number for the repository is not reached. It's configured to be 9 and nothing else going on.

If I remember correctly, Veeam used to do only one file at a time some years ago, and added this as performance enhancement to do multiple files at once.

The only place I could think of to configure it was the task on the repository or perhaps CPU's on the target. The target server has 8 sockets and is not busy.

Regards,
Jóhannes

[matteu]

You have the task number on the repository but you also have proxy task number.
I will try to see if I can find something to help you I will answer

[johannesk]

yes, the task number on the proxy is according to the CPU's (48 cores) and is configured to be able to have all the repository extents busy.

It would be really good to know for sure what is the determening factor, and if it can be configured. I totally understand that maximum tasks on the extent would always be a limiting factor.

[HannesK]

Hello,
just to clarify: what kind of restore are we talking about? Veeam Explorer for SQL? Manual file-level restore? Disk restore?

Best regards,
Hannes

[johannesk]

@Hannes SQL restore from Veeam Explorer for SQL on VBR 11a 11.0.1.1261

[HannesK]

Hello,
okay... then it's configurable via C:\ProgramData\Veeam\Backup\SQLExplorer\Config.xml (create if it does not exist)

Code: Select all

<Veeam>
<Core>
                <Log Level="Extended"/>
                <Copy FilesInParallelCount="4"/>
</Core>
<SQLExplorer>
                <Restore TrySaveDbOwner="true" />
</SQLExplorer>
</Veeam>

Best regards,
Hannes

[matteu]

Interesting
Can we find this information somewhere in the documentation or not ?

Thanks

[HannesK]

"not"

The default settings should work fine. Adding more parallelism does not necessarily make it faster. If things (usually registry keys) are becoming very popular, we usually change the user interface and then document the "new" feature in the documentation. Adding that kind of settings to the documentation would make it harder to read it and find information (the documentation is very large already...)

I would be interested, if Johannes could answer how much faster restores became by increasing the parallel files processing.

Best regards,
Hannes

[johannesk]

Hi Hannes,

I did the same restore again for that SQL server DB.

It's 4x130GB datafiles and 1x logfile 100GB.

From the proxy it's reading one backup file to send those 5 DB files to the SQL server. It seemed the reading speed on the proxy was about the same as before. So by increasing the number of streams just divides the reading speed of the proxy to more files to be written to the SQL server.

When it ran 4 streams it took 73 minutes. Now with 5 streams, it took 59 minutes.

I going to keep it at the default. But it's really good to know about this option. I'm glad I asked.

Regards,
Jóhannes

[HannesK]

Hello,
hmm, how can the reading speed be the same if the overall restore speed became 20% faster (59 vs 73 min)?

Best regards,
Hannes

[johannesk]

I did not note the exact reading speed from the backup file, but noticed it was dancing around 190 - 200MB/s most of the time in both cases. Probably it was in the higher end of it for longer in the 5 thread restore compared to when it did the 4 thread restore.

Yes 20% faster is a notible increase.