Sql-server – Database restore time increased after we changed from SQL Server 2014 to SQL Server 2016

restoresql serversql-server-2016

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.

Best Answer

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.

Related Question