MAX worker thread in SQL server 2012/14/16
I was just browsing the system health extended event to come across an event with diagnostic result for query processing showing status for worker threads:
- why max worker threads showing 2994 when i have 80 logical processors which as per msdn calculation here
512 + ((logical CPU's - 4) * 16)does not looks correct. However per the latest link here makes sense for logical processors greater than 64 where calculation for 16 changes to 32. But that link says starting SQL 2017, however the number 2994 i am getting is for SQL2012, 14 16 where no of logical processors is 80. Am i missing something here?
max worker setting within sp_configure is set to '0'
- Coming back to that system health event status shows WARNING for below no:
maxWorkers="2944" workersCreated="456" workersIdle="314"
tasksCompletedWithinInterval="1021881" pendingTasks="1"
Should i be checking something else or can this be ignored?
sql-server sql-server-2012 sql-server-2014 cpu
asked 3 hours ago
BeginnerDBABeginnerDBA
5131417
add a comment |
I was just browsing the system health extended event to come across an event with diagnostic result for query processing showing status for worker threads:
- why max worker threads showing 2994 when i have 80 logical processors which as per msdn calculation here
512 + ((logical CPU's - 4) * 16)does not looks correct. However per the latest link here makes sense for logical processors greater than 64 where calculation for 16 changes to 32. But that link says starting SQL 2017, however the number 2994 i am getting is for SQL2012, 14 16 where no of logical processors is 80. Am i missing something here?
max worker setting within sp_configure is set to '0'
- Coming back to that system health event status shows WARNING for below no:
maxWorkers="2944" workersCreated="456" workersIdle="314"
tasksCompletedWithinInterval="1021881" pendingTasks="1"
Should i be checking something else or can this be ignored?
sql-server sql-server-2012 sql-server-2014 cpu
asked 3 hours ago
BeginnerDBABeginnerDBA
5131417
add a comment |
I was just browsing the system health extended event to come across an event with diagnostic result for query processing showing status for worker threads:
- why max worker threads showing 2994 when i have 80 logical processors which as per msdn calculation here
512 + ((logical CPU's - 4) * 16)does not looks correct. However per the latest link here makes sense for logical processors greater than 64 where calculation for 16 changes to 32. But that link says starting SQL 2017, however the number 2994 i am getting is for SQL2012, 14 16 where no of logical processors is 80. Am i missing something here?
max worker setting within sp_configure is set to '0'
- Coming back to that system health event status shows WARNING for below no:
maxWorkers="2944" workersCreated="456" workersIdle="314"
tasksCompletedWithinInterval="1021881" pendingTasks="1"
Should i be checking something else or can this be ignored?
sql-server sql-server-2012 sql-server-2014 cpu
asked 3 hours ago
BeginnerDBABeginnerDBA
5131417
I was just browsing the system health extended event to come across an event with diagnostic result for query processing showing status for worker threads:
- why max worker threads showing 2994 when i have 80 logical processors which as per msdn calculation here
512 + ((logical CPU's - 4) * 16)does not looks correct. However per the latest link here makes sense for logical processors greater than 64 where calculation for 16 changes to 32. But that link says starting SQL 2017, however the number 2994 i am getting is for SQL2012, 14 16 where no of logical processors is 80. Am i missing something here?
max worker setting within sp_configure is set to '0'
- Coming back to that system health event status shows WARNING for below no:
maxWorkers="2944" workersCreated="456" workersIdle="314"
tasksCompletedWithinInterval="1021881" pendingTasks="1"
Should i be checking something else or can this be ignored?
sql-server sql-server-2012 sql-server-2014 cpu
sql-server sql-server-2012 sql-server-2014 cpu
asked 3 hours ago
BeginnerDBABeginnerDBA
5131417
asked 3 hours ago
BeginnerDBABeginnerDBA
5131417
edited 3 hours ago
BeginnerDBA
asked 3 hours ago
BeginnerDBABeginnerDBA
5131417
asked 3 hours ago
BeginnerDBABeginnerDBA
5131417
asked 3 hours ago
BeginnerDBABeginnerDBA
5131417
5131417
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
- why max worker threads showing 2994 when i have 80 logical processors […]
It seems there is a documentation error when the item was copied over from MSDN to Docs. What you see in Dorr's post is correct and is applicable back to 2012 SP4 + latest CUs. I'll make the required documentation changes.
- Coming back to that system health event status shows WARNING for below no […]
The reason you have a warning is because you have pendingTasks="1". This could indicate an issue with a scheduler and should be investigated.
answered 1 hour ago
Sean GallardySean Gallardy
15.8k22548
Thanks Sean. I see pending task count varying sometimes 2, 4 , 8 or even higher. Can you guide me a link what needs to be check or if you can expand the answer as i am not sure when you say issue with scheduler. thanks again
– BeginnerDBA
1 hour ago
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%2f229560%2fmax-worker-thread-in-sql-server-2012-14-16%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
- why max worker threads showing 2994 when i have 80 logical processors […]
It seems there is a documentation error when the item was copied over from MSDN to Docs. What you see in Dorr's post is correct and is applicable back to 2012 SP4 + latest CUs. I'll make the required documentation changes.
- Coming back to that system health event status shows WARNING for below no […]
The reason you have a warning is because you have pendingTasks="1". This could indicate an issue with a scheduler and should be investigated.
answered 1 hour ago
Sean GallardySean Gallardy
15.8k22548
Thanks Sean. I see pending task count varying sometimes 2, 4 , 8 or even higher. Can you guide me a link what needs to be check or if you can expand the answer as i am not sure when you say issue with scheduler. thanks again
– BeginnerDBA
1 hour ago
add a comment |
- why max worker threads showing 2994 when i have 80 logical processors […]
It seems there is a documentation error when the item was copied over from MSDN to Docs. What you see in Dorr's post is correct and is applicable back to 2012 SP4 + latest CUs. I'll make the required documentation changes.
- Coming back to that system health event status shows WARNING for below no […]
The reason you have a warning is because you have pendingTasks="1". This could indicate an issue with a scheduler and should be investigated.
answered 1 hour ago
Sean GallardySean Gallardy
15.8k22548
Thanks Sean. I see pending task count varying sometimes 2, 4 , 8 or even higher. Can you guide me a link what needs to be check or if you can expand the answer as i am not sure when you say issue with scheduler. thanks again
– BeginnerDBA
1 hour ago
add a comment |
- why max worker threads showing 2994 when i have 80 logical processors […]
It seems there is a documentation error when the item was copied over from MSDN to Docs. What you see in Dorr's post is correct and is applicable back to 2012 SP4 + latest CUs. I'll make the required documentation changes.
- Coming back to that system health event status shows WARNING for below no […]
The reason you have a warning is because you have pendingTasks="1". This could indicate an issue with a scheduler and should be investigated.
answered 1 hour ago
Sean GallardySean Gallardy
15.8k22548
- why max worker threads showing 2994 when i have 80 logical processors […]
It seems there is a documentation error when the item was copied over from MSDN to Docs. What you see in Dorr's post is correct and is applicable back to 2012 SP4 + latest CUs. I'll make the required documentation changes.
- Coming back to that system health event status shows WARNING for below no […]
The reason you have a warning is because you have pendingTasks="1". This could indicate an issue with a scheduler and should be investigated.
answered 1 hour ago
Sean GallardySean Gallardy
15.8k22548
answered 1 hour ago
Sean GallardySean Gallardy
15.8k22548
answered 1 hour ago
Sean GallardySean Gallardy
15.8k22548
answered 1 hour ago
Sean GallardySean Gallardy
15.8k22548
15.8k22548
Thanks Sean. I see pending task count varying sometimes 2, 4 , 8 or even higher. Can you guide me a link what needs to be check or if you can expand the answer as i am not sure when you say issue with scheduler. thanks again
– BeginnerDBA
1 hour ago
add a comment |
Thanks Sean. I see pending task count varying sometimes 2, 4 , 8 or even higher. Can you guide me a link what needs to be check or if you can expand the answer as i am not sure when you say issue with scheduler. thanks again
– BeginnerDBA
1 hour ago
Thanks Sean. I see pending task count varying sometimes 2, 4 , 8 or even higher. Can you guide me a link what needs to be check or if you can expand the answer as i am not sure when you say issue with scheduler. thanks again
– BeginnerDBA
1 hour ago
Thanks Sean. I see pending task count varying sometimes 2, 4 , 8 or even higher. Can you guide me a link what needs to be check or if you can expand the answer as i am not sure when you say issue with scheduler. thanks again
– BeginnerDBA
1 hour ago
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%2f229560%2fmax-worker-thread-in-sql-server-2012-14-16%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
