Multi-Threaded Restores:
Using Management Studio - Maintenance Plans - it is possible to design a process that kicks-off multiple restores simultaneously. As can be seen in the below design diagram. For our use case, we saw a 50-60% reduction in overall processing times due to this design alone.
Database Instant File Initialization: [docs.microsoft.com]
Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:
- Create a database.
- Add data or log files, to an existing database.
- Increase the size of an existing file (including autogrow operations).
- Restore a database or filegroup
In SQL Server, data files can be initialized instantaneously. Instant file initialization allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.
A Restore Operation occurs by first sizing the files on disk, then zeroing out the space in the data file(if instant file initialization is NOT enabled), then zeroing the space in the log file always. Then the data is written into the data file, followed by the log file and finally crash recovery process runs to bring the Database in an online state. *Thus, to avoid the possible long delay with zeroing out the data file, enable Instant file initialization for the Server. For monitoring, use the DMV sys.dm_exec_requests and analyze the wait_type information for active sessions.
See: Database Instant File Initialization (Basically grant a security permission to the SQL Server service account)
See: How and Why to Enable Instant File Initialization
As per SQL Server Installation:
"Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service. This privlege enabes instant file initialization by avoiding zeroing of data pages. This may lead to information disclosure by allowing deleted content to be accessed."
Azure Virtual Machine Disks:
Premium Disks (SSD) - are backed by Solid State Drives and offer consistent, low-latency performance. Ideal for I/O-intensive applications and production workloads. [ESTIMATED PERFORMANCE: IOPS limit 5000 Throughput limit (MB/s) 200]
vs
Standard Disks (HDD) - are backed by magnetic drives and are preferable for applications where data is accessed infrequently.
Within our Azure Virtual Machine, with SSD's drives, we were getting Disk IO of 250MB/sec consistently during the multi-threaded restore job above.
|
|
|
|
|
|