Sql-server – Scalability of concurrent log restore for different databases on SQL Server

restoresql serversql server 2014sql-server-2016wait-types

  1. Are there any locks or resource blocking/contention (beyond IO capacity) between two sessions, which restore logs for two different databases with norecovery? Same about restore logs with recovery?

    a. My answer by now is “not much”, asking for validation.

  2. Are there any locks or resource blocking/contention (beyond IO capacity) between two sessions, which restore two different databases with recovery from norecovery state?

    a. My answer by now is “not much”, asking for validation.

  3. What portions of concurrent log restore process (read backup/write to data/write to log, other) contribute into each of the major identified wait types BACKUPIO, IO_COMPLETION, LCK_M_S, BACKUPTHREAD, PREEMPTIVE_OS_FLUSHFILEBUFFERS, PAGEIOLATCH_EX

    a. Most puzzling is LCK_M_S

I thought the question was very clear, but clarifying. The scenario is planning of log shipping of 2000-6000 DBs per server. There are two concurrency scenario – first is regular scheduled application of logs and second is DR scenario when all DBs need to be brought online using RESTORE DATABASE WITH RECOVERY.
Third question is a capture of actual top waits during test run of concurrent log restore. I do know what each wait mean, I don't know per SQL internals what portion of restore process is reflects and as such how can be optimized.

Best Answer

For 1 & 2 - You need to take into account VLFs as well. Too many VLFs will slow down the restore of Transaction log.

Also, all the restores are logged in msdb--> restorefile, restorefilegroup and restorehistory tables, so many concurrent sessions will have to write to the same table and if those tables are not maintained (purged) properly, there will be contention/blocking.

As a side note, I have seen blocking especially when you logship 50+ databases with the same restore frequency.

For 3, I will just refer to the most authoritative wait stats repository.

Related Question