1

  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.

Improve this question
2
  • 1
    Why? Are you having problems with restoring databases/logs simultaneously? What are you attempting to solve?
    – Hannah Vernon ♦
    Dec 9, 2015 at 15:30
  • 1 & 2 as correct with minor cautions on memory and CPU depending on whether you have compression and/or encryption enabled. If you restore a bunch of DBs in parallel with these enabled, you might get contention but you'd really have to run many (actual count depends on your server resources, of course). As for #3, that's a mixed lot of waittypes. Why did you pick those waittypes specifically? Are they popping to the top of the waits list? If not, what are you trying to accomplish? LCK_M_S is a concurrency wait type - waiting for shared lock, can be high because of blocking or slow IO
    – SQLmojoe
    Dec 9, 2015 at 17:36

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • Having lots of VLFs will slow each DB restore independently, the impact is not cross database. I think you're trying to say the same thing but the way it's currently written, it could be interpreted the other way. MSDB aside, metadata updates may cause blocking if you have too many parallel restore sessions but those should be very short-lived though still measurable if you have the right monitoring/events captured. If you see extended blocking, there's a good chance it's a bug.
    – SQLmojoe
    Mar 15, 2016 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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