MS SQL restore throughput

hello ​@M. Milewski 

The current throughput looks very good. Could you please share the Write/Read performance as well?

If you’re familiar with the Disk Performance Tool, that would be great. Otherwise, you can share the logs using the scrub option to ensure no sensitive information is exposed.
 

Best regards,
Mohammed Ramadan
Commvault PS Engineer

Hi ​@Muhammed Ramadan 

Here is the Disk Performance Tool report for one of the DB disks with the default settings:

Creating folder M:\...\DISK_READ_TEST
Creating files..
Writing files..
Reading files..
Deleting files..
DiskPerf Version        : 2.2
Path Used               : M:\...
Performance type        : Create new
Read-Write type         : RANDOM
Block Size              : 65536
Block Count             : 16384
File Count              : 6
Thread Count            : 6
Total Bytes Read        : 6442450944
Total Bytes Written     : 6442450944
Total Bytes Deleted     : 6442450944
----
Time Taken to Create(S)     : 3.29
Time Taken to Write&flush(S): 2.62
Time Taken to Read(S)       : 5.56
Time Taken to Delete(S)     : 0.11
----
Per thread Throughput Create(GB/H)     : 1095.55
Per thread Throughput Write(GB/H)      : 1375.70
Per thread Throughput Read(GB/H)       : 647.12
Per thread Throughput Delete(GB/H)     : 32494.64
----
Throughput Create(GB/H)     : 6573.28
Throughput Write(GB/H)      : 8254.22
Throughput Read(GB/H)       : 3882.75
Throughput Delete(GB/H)     : 194967.84

Report Completed

​@Muhammed Ramadan 

I have posted the results of the performance tool but is held up by a moderators to review.

Let me try it again

DiskPerf Version        : 2.2
Path Used               : 
Performance type        : Create new
Read-Write type         : RANDOM
Block Size              : 65536
Block Count             : 16384
File Count              : 6
Thread Count            : 6
Total Bytes Read        : 6442450944
Total Bytes Written     : 6442450944
Total Bytes Deleted     : 6442450944
----
Time Taken to Create(S)     : 3.29
Time Taken to Write&flush(S): 2.62
Time Taken to Read(S)       : 5.56
Time Taken to Delete(S)     : 0.11
----
Per thread Throughput Create(GB/H)     : 1095.55
Per thread Throughput Write(GB/H)      : 1375.70
Per thread Throughput Read(GB/H)       : 647.12
Per thread Throughput Delete(GB/H)     : 32494.64
----
Throughput Create(GB/H)     : 6573.28
Throughput Write(GB/H)      : 8254.22
Throughput Read(GB/H)       : 3882.75
Throughput Delete(GB/H)     : 194967.84

Hi ​@M. Milewski 

How many VDI streams you’re using to backup this DB?

Thanks,

Sunil

Hello ​@M. Milewski, 

When discussing about throughput for SQL database restore to instance, from the SQL Server end, a considerable amount of time is being consumed during the initialization phase of the restore process for huge database. This directly impacts the overall restore duration and throughput.

Verify Instant File Initialization (IFI) Status

• Check with the DBA team whether Instant File Initialization (IFI) is enabled on the SQL Server instance.
• IFI significantly reduces time during operations that require zero‑initializing data files, such as:
  • Database creation
  • File growth
  • Database restores

Reference (Microsoft Docs):
https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver17

Enable IFI (If Not Already Enabled)

To enable IFI:

• Grant the “Perform volume maintenance tasks” (SE_MANAGE_VOLUME_NAME) privilege to the SQL Server service account.
• Once permissions are granted, restart the SQL Server service.

Post‑Configuration Validation

After enabling IFI:

• Restart SQL Server services.
• Perform a test restore to validate whether initialization time improves.
• Compare restore duration before and after enabling IFI for confirmation.

​@Sunil 

we use 4 data streams and 2 tlog streams

​@Vinod Kumar G 

Yes, the IFI is enabled 

​@Sunil. Thanks for suggestion. I have change the data streams to 8 and that make a big difference.

My media agents are HSX nodes (5 of them). I don’t see much impact on those nodes in terms of CPU and mem utilization. Now testing with 10 data streams and monitoring.

We didn’t change the data streams count before as CV mentioned that this may have a negative impact on the backup/restore jobs.

Will keep you guys posting.

After increasing data streams to 10 we have shortened the 8.4TB database restore time from 18 hrs to 8.5.

Thanks everyone for your contribution.

Cool… thanks for letting us know ​@M. Milewski !!