0

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.

1

For those who may have the same problem, we noticed that when we did the CHECKDB we had an error with PFS pages (https://www.sqlskills.com/blogs/paul/pfs-corruption-after-upgrading-from-sql-server-2014) and thus the solution was to correct these error with a DBCC CHECKDB(database_name, REPAIR_ALLOW_DATA_LOSS).

We also move the disk from 7K to 10K to upgrade performances.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.