1

I have a 2 TB database (200 GB backup) that I am restoring every night on a secondary server of a AlwaysOn group, to erase confidential information and then backup the clean database and send it to test environment. During the restore there is no other statement running on the SQL Server, except the redo from the primary (which mostly concern reindex).

Before the SQL Server were in 2014 edition, the rate was about 180 MB/s to 200 MB/s for the restore. I have upgraded in-place to a 2016 edition (CU12) a few weeks ago, and now the rate to restore the database is below 90 MB/s.

I have limited time to do the full process of restoring and cleaning the database before teams get to work on it on the morning, and since the migration the processing time no longer makes it possible to respect the schedules.

I have checked disk graph and configuration with system teams, and we didn't found anything conclusive. Database instant file initialization is already enable on the server, and disk block are in 64K. I am restoring from one disk E:\ (where backup are stored) to another disk F:\ (where I work on the cleanup).

Has this problem ever happened to someone after a migration (rate when restoring slower than before)? I haven't found anything like that in official documentation or forums.

EDIT: Resource Governor is disabled. Max memory is 480 GB. Nothing in the error log. No significant queue on the disk during the restore.

  • Oh yes sorry, it was an in-place upgrade. So nothing has changed for the server between the two versions. The only thing changed is the compatibility level of the database which is 130 now. – user210540 Jun 11 at 14:26
  • A few things you could check which can cause restores to slow: 1) Max Mem setting for the new instance (Memory)? 2) # of vlfs the database you are restoring - too many are bad 3) How are the I/O rates (any latency) on the drive you are writing to for the restore? 4) Anything in the sql server error log during the restore? – rvsc48 Jun 11 at 16:10
  • Did the backup start taking longer to complete too? I don't know a cause of the problem, but depending of your method of restoring that backup, maybe it would be possible to use a different strategy to finish it back on schedule while looking for the cause of the performance reduction. If you're restoring the full backup from tonight, maybe restoring the full from the night before WITH NORECOVERY earlier and later restore the DIFF and LOG backups until tonight would be an option. – Ronaldo Jun 11 at 20:11
  • @rvsc48 1) Max memory is 480 GB 2) The database has 54 vlf, I didn't know that vlf can impact restore process 3) There is no queue on the disk at the restore time 4) Nothing in the error log except line for logs backup – user210540 Jun 12 at 9:11
  • @Ronaldo The backup rate is approximately the same as before. For example tonight: restore took more than 3 hours with 76 MB/s and backup took two hour with 110 MB/s – user210540 Jun 12 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.