Scalability of concurrent log restore for different databases on SQL Server
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.
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.
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.
sql-server restore sql-server-2014 wait-types sql-server-2016
asked Dec 9 '15 at 15:20
Alexander TarasulAlexander Tarasul
673
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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.
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.
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.
sql-server restore sql-server-2014 wait-types sql-server-2016
asked Dec 9 '15 at 15:20
Alexander TarasulAlexander Tarasul
673
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Why? Are you having problems with restoring databases/logs simultaneously? What are you attempting to solve?
– Max Vernon
Dec 9 '15 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 '15 at 17:36
add a comment |
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.
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.
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.
sql-server restore sql-server-2014 wait-types sql-server-2016
asked Dec 9 '15 at 15:20
Alexander TarasulAlexander Tarasul
673
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.
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.
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.
sql-server restore sql-server-2014 wait-types sql-server-2016
sql-server restore sql-server-2014 wait-types sql-server-2016
asked Dec 9 '15 at 15:20
Alexander TarasulAlexander Tarasul
673
asked Dec 9 '15 at 15:20
Alexander TarasulAlexander Tarasul
673
edited Dec 11 '15 at 4:22
Alexander Tarasul
asked Dec 9 '15 at 15:20
Alexander TarasulAlexander Tarasul
673
asked Dec 9 '15 at 15:20
Alexander TarasulAlexander Tarasul
673
asked Dec 9 '15 at 15:20
Alexander TarasulAlexander Tarasul
673
673
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Why? Are you having problems with restoring databases/logs simultaneously? What are you attempting to solve?
– Max Vernon
Dec 9 '15 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 '15 at 17:36
add a comment |
1
Why? Are you having problems with restoring databases/logs simultaneously? What are you attempting to solve?
– Max Vernon
Dec 9 '15 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 '15 at 17:36
1
1
Why? Are you having problems with restoring databases/logs simultaneously? What are you attempting to solve?
– Max Vernon
Dec 9 '15 at 15:30
Why? Are you having problems with restoring databases/logs simultaneously? What are you attempting to solve?
– Max Vernon
Dec 9 '15 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 '15 at 17:36
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 '15 at 17:36
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Dec 9 '15 at 21:56
KinKin
54k481192
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 '16 at 22:40
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f123321%2fscalability-of-concurrent-log-restore-for-different-databases-on-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Dec 9 '15 at 21:56
KinKin
54k481192
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 '16 at 22:40
add a comment |
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.
answered Dec 9 '15 at 21:56
KinKin
54k481192
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 '16 at 22:40
add a comment |
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.
answered Dec 9 '15 at 21:56
KinKin
54k481192
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.
answered Dec 9 '15 at 21:56
KinKin
54k481192
answered Dec 9 '15 at 21:56
KinKin
54k481192
answered Dec 9 '15 at 21:56
KinKin
54k481192
answered Dec 9 '15 at 21:56
KinKin
54k481192
54k481192
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 '16 at 22:40
add a comment |
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 '16 at 22:40
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 '16 at 22:40
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 '16 at 22:40
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f123321%2fscalability-of-concurrent-log-restore-for-different-databases-on-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Why? Are you having problems with restoring databases/logs simultaneously? What are you attempting to solve?
– Max Vernon
Dec 9 '15 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 '15 at 17:36