I have a 1.1TB database with 700GB of data on a SQL Server 2008 R2 SP1 instance. Most data is row compressed and in 2 partitioned tables. A full backup is taken daily to 4 files on a NAS as part of the daily maintenance routine (which also includes index fragmentation, updating statistics and an integrity check directly before the full backup and in that order). The backup is also compressed.
On a testserver I want to do a piecemeal restore, starting with the primary filegroup (the partitioned tables are in other filegroups). The primary filegroup file is 9GB and has about 3GB of data. The transaction logfile is 16GB. I know, with a restore, the transaction logfile is being zeroed which could take some time. On the other hand, the empty space in the primary data file is not being zeroed during a restore operation when Instant File Initialization is enabled. This is the case on my testserver.
The problem is that the piecemeal restore takes a very long time, about 6 hours. I investigated and tried a few things to speed up the restore. Here they are:
- Check if Instant File Initialization is enabled, it is.
- Checking the performance of the whole chain (NAS, network, SAN), by restoring another backup with approximately the same size (3GB) from the same NAS to the same testserver over the same network. The restore was done in 2-4 minutes.
- Checking the number of virtual logfiles, which is 41 and thus ok.
- Take a partial backup and restore that one partially. This restore took 2-4 minutes. My conclusion: creating the mdf and ldf files is not the problem.
- Altered the daily maintenance routine by adding a transactionlog backup after the integrity check and before the full backup, hoping that the transactions from the transactionlog caused by the earlier operations, are left out from the full backup. I’m not sure this makes sense, but to my understanding when SQL Server takes a backup, the transactions in the transaction log are also included for the redo/undo fase when restoring a the backup.
Also, I opened the Performance Monitor to see disk activity during the restore operation. There is only disk activity during the beginning of the restore operation. Furthermore, when I execute sp_WhoIsActive during the restore operation, I see that the restore session is suspended (wait_info: BACKUPTHREAD) while network traffic stays at maximum. The CPU's are also processing 'something'. I’m beginning to suspect the restore operation is scanning every page in the full backup files somehow.
Is there someone who knows if this is true or, if not, knows the true reason why the piecemeal restore is so slow?