Sql Server 2016 Counter Reporting Zero
I utilize the resource governor on my 2016 Sql Server. In prior 2016 sql server versions, this query:
select *
from master.sys.dm_os_performance_counters opc
where opc.object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage target %'
Would yield the target value of CPU usage % for the resource pool based on the resource pool configuration settings and system load. However, now they are always zero. I also used perfmon to confirm this (along with running legit queries). Does anyone know why or if this counter changed? According to the msdn article, it should still work.
sql-server sql-server-2016
edited Oct 7 '16 at 20:43
Tom V
13.8k74677
asked Oct 7 '16 at 20:29
HunterX3HunterX3
35639
bumped to the homepage by Community♦ 8 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 |
I utilize the resource governor on my 2016 Sql Server. In prior 2016 sql server versions, this query:
select *
from master.sys.dm_os_performance_counters opc
where opc.object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage target %'
Would yield the target value of CPU usage % for the resource pool based on the resource pool configuration settings and system load. However, now they are always zero. I also used perfmon to confirm this (along with running legit queries). Does anyone know why or if this counter changed? According to the msdn article, it should still work.
sql-server sql-server-2016
edited Oct 7 '16 at 20:43
Tom V
13.8k74677
asked Oct 7 '16 at 20:29
HunterX3HunterX3
35639
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
I opened a Microsoft Connect item: connect.microsoft.com/SQLServer/feedback/details/3106449
– HunterX3
Oct 13 '16 at 16:00
add a comment |
I utilize the resource governor on my 2016 Sql Server. In prior 2016 sql server versions, this query:
select *
from master.sys.dm_os_performance_counters opc
where opc.object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage target %'
Would yield the target value of CPU usage % for the resource pool based on the resource pool configuration settings and system load. However, now they are always zero. I also used perfmon to confirm this (along with running legit queries). Does anyone know why or if this counter changed? According to the msdn article, it should still work.
sql-server sql-server-2016
edited Oct 7 '16 at 20:43
Tom V
13.8k74677
asked Oct 7 '16 at 20:29
HunterX3HunterX3
35639
I utilize the resource governor on my 2016 Sql Server. In prior 2016 sql server versions, this query:
select *
from master.sys.dm_os_performance_counters opc
where opc.object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage target %'
Would yield the target value of CPU usage % for the resource pool based on the resource pool configuration settings and system load. However, now they are always zero. I also used perfmon to confirm this (along with running legit queries). Does anyone know why or if this counter changed? According to the msdn article, it should still work.
sql-server sql-server-2016
sql-server sql-server-2016
edited Oct 7 '16 at 20:43
Tom V
13.8k74677
asked Oct 7 '16 at 20:29
HunterX3HunterX3
35639
edited Oct 7 '16 at 20:43
Tom V
13.8k74677
asked Oct 7 '16 at 20:29
HunterX3HunterX3
35639
edited Oct 7 '16 at 20:43
Tom V
13.8k74677
edited Oct 7 '16 at 20:43
Tom V
13.8k74677
edited Oct 7 '16 at 20:43
Tom V
13.8k74677
13.8k74677
asked Oct 7 '16 at 20:29
HunterX3HunterX3
35639
asked Oct 7 '16 at 20:29
HunterX3HunterX3
35639
asked Oct 7 '16 at 20:29
HunterX3HunterX3
35639
35639
bumped to the homepage by Community♦ 8 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♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
I opened a Microsoft Connect item: connect.microsoft.com/SQLServer/feedback/details/3106449
– HunterX3
Oct 13 '16 at 16:00
add a comment |
I opened a Microsoft Connect item: connect.microsoft.com/SQLServer/feedback/details/3106449
– HunterX3
Oct 13 '16 at 16:00
I opened a Microsoft Connect item: connect.microsoft.com/SQLServer/feedback/details/3106449
– HunterX3
Oct 13 '16 at 16:00
I opened a Microsoft Connect item: connect.microsoft.com/SQLServer/feedback/details/3106449
– HunterX3
Oct 13 '16 at 16:00
add a comment |
2 Answers
2
active
oldest
votes
You posted a bug on this at Microsoft Connect:
SQL Server 2016 SQLServer:Resource Pool Stats:CPU usage target % Always Returns Zero - by HunterX3
Given that Microsoft didn't respond there either, it's probably a bug. Open a support case with Microsoft, and if it turns out to be a bug, they refund your support fee.
answered Feb 15 '17 at 21:25
Brent OzarBrent Ozar
34.2k19101229
Support ticket opened, will post back results to close this issue out hopefully.
– HunterX3
Mar 28 '17 at 14:01
After talking with Microsoft Support a few times, they finally told me this is a bug. However, the product team said the algorithms for this particular counter in Sql Server 2016 are too complicated to fix. They will be updating books online to show that the counter is useless now. Because we monitor these counters to make sure the Governor is working, I will have to come up with a home grown solution. I'm open for any suggestions.
– HunterX3
May 8 '17 at 16:28
add a comment |
Try this and see if this gives a value other than zero:
SELECT perfCount.object_name ,
perfCount.counter_name ,
CASE WHEN perfBase.cntr_value = 0 THEN 0
ELSE ( CAST(perfCount.cntr_value AS float) / perfBase.cntr_value ) * 100
END AS cntr_Value
FROM ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage %' ) AS perfCount
INNER JOIN ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage % base' ) AS perfBase
ON perfCount.object_name = perfBase.object_name;
answered Jan 27 '17 at 19:18
Vic WorkVic Work
1976
That query returns results, but they are not correct. Can you add some description of what you were expecting the query to do and why?
– HunterX3
Jan 27 '17 at 20:50
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%2f151749%2fsql-server-2016-counter-reporting-zero%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You posted a bug on this at Microsoft Connect:
SQL Server 2016 SQLServer:Resource Pool Stats:CPU usage target % Always Returns Zero - by HunterX3
Given that Microsoft didn't respond there either, it's probably a bug. Open a support case with Microsoft, and if it turns out to be a bug, they refund your support fee.
answered Feb 15 '17 at 21:25
Brent OzarBrent Ozar
34.2k19101229
Support ticket opened, will post back results to close this issue out hopefully.
– HunterX3
Mar 28 '17 at 14:01
After talking with Microsoft Support a few times, they finally told me this is a bug. However, the product team said the algorithms for this particular counter in Sql Server 2016 are too complicated to fix. They will be updating books online to show that the counter is useless now. Because we monitor these counters to make sure the Governor is working, I will have to come up with a home grown solution. I'm open for any suggestions.
– HunterX3
May 8 '17 at 16:28
add a comment |
You posted a bug on this at Microsoft Connect:
SQL Server 2016 SQLServer:Resource Pool Stats:CPU usage target % Always Returns Zero - by HunterX3
Given that Microsoft didn't respond there either, it's probably a bug. Open a support case with Microsoft, and if it turns out to be a bug, they refund your support fee.
answered Feb 15 '17 at 21:25
Brent OzarBrent Ozar
34.2k19101229
Support ticket opened, will post back results to close this issue out hopefully.
– HunterX3
Mar 28 '17 at 14:01
After talking with Microsoft Support a few times, they finally told me this is a bug. However, the product team said the algorithms for this particular counter in Sql Server 2016 are too complicated to fix. They will be updating books online to show that the counter is useless now. Because we monitor these counters to make sure the Governor is working, I will have to come up with a home grown solution. I'm open for any suggestions.
– HunterX3
May 8 '17 at 16:28
add a comment |
You posted a bug on this at Microsoft Connect:
SQL Server 2016 SQLServer:Resource Pool Stats:CPU usage target % Always Returns Zero - by HunterX3
Given that Microsoft didn't respond there either, it's probably a bug. Open a support case with Microsoft, and if it turns out to be a bug, they refund your support fee.
answered Feb 15 '17 at 21:25
Brent OzarBrent Ozar
34.2k19101229
You posted a bug on this at Microsoft Connect:
SQL Server 2016 SQLServer:Resource Pool Stats:CPU usage target % Always Returns Zero - by HunterX3
Given that Microsoft didn't respond there either, it's probably a bug. Open a support case with Microsoft, and if it turns out to be a bug, they refund your support fee.
answered Feb 15 '17 at 21:25
Brent OzarBrent Ozar
34.2k19101229
answered Feb 15 '17 at 21:25
Brent OzarBrent Ozar
34.2k19101229
answered Feb 15 '17 at 21:25
Brent OzarBrent Ozar
34.2k19101229
answered Feb 15 '17 at 21:25
Brent OzarBrent Ozar
34.2k19101229
34.2k19101229
Support ticket opened, will post back results to close this issue out hopefully.
– HunterX3
Mar 28 '17 at 14:01
After talking with Microsoft Support a few times, they finally told me this is a bug. However, the product team said the algorithms for this particular counter in Sql Server 2016 are too complicated to fix. They will be updating books online to show that the counter is useless now. Because we monitor these counters to make sure the Governor is working, I will have to come up with a home grown solution. I'm open for any suggestions.
– HunterX3
May 8 '17 at 16:28
add a comment |
Support ticket opened, will post back results to close this issue out hopefully.
– HunterX3
Mar 28 '17 at 14:01
After talking with Microsoft Support a few times, they finally told me this is a bug. However, the product team said the algorithms for this particular counter in Sql Server 2016 are too complicated to fix. They will be updating books online to show that the counter is useless now. Because we monitor these counters to make sure the Governor is working, I will have to come up with a home grown solution. I'm open for any suggestions.
– HunterX3
May 8 '17 at 16:28
Support ticket opened, will post back results to close this issue out hopefully.
– HunterX3
Mar 28 '17 at 14:01
Support ticket opened, will post back results to close this issue out hopefully.
– HunterX3
Mar 28 '17 at 14:01
After talking with Microsoft Support a few times, they finally told me this is a bug. However, the product team said the algorithms for this particular counter in Sql Server 2016 are too complicated to fix. They will be updating books online to show that the counter is useless now. Because we monitor these counters to make sure the Governor is working, I will have to come up with a home grown solution. I'm open for any suggestions.
– HunterX3
May 8 '17 at 16:28
After talking with Microsoft Support a few times, they finally told me this is a bug. However, the product team said the algorithms for this particular counter in Sql Server 2016 are too complicated to fix. They will be updating books online to show that the counter is useless now. Because we monitor these counters to make sure the Governor is working, I will have to come up with a home grown solution. I'm open for any suggestions.
– HunterX3
May 8 '17 at 16:28
add a comment |
Try this and see if this gives a value other than zero:
SELECT perfCount.object_name ,
perfCount.counter_name ,
CASE WHEN perfBase.cntr_value = 0 THEN 0
ELSE ( CAST(perfCount.cntr_value AS float) / perfBase.cntr_value ) * 100
END AS cntr_Value
FROM ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage %' ) AS perfCount
INNER JOIN ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage % base' ) AS perfBase
ON perfCount.object_name = perfBase.object_name;
answered Jan 27 '17 at 19:18
Vic WorkVic Work
1976
That query returns results, but they are not correct. Can you add some description of what you were expecting the query to do and why?
– HunterX3
Jan 27 '17 at 20:50
add a comment |
Try this and see if this gives a value other than zero:
SELECT perfCount.object_name ,
perfCount.counter_name ,
CASE WHEN perfBase.cntr_value = 0 THEN 0
ELSE ( CAST(perfCount.cntr_value AS float) / perfBase.cntr_value ) * 100
END AS cntr_Value
FROM ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage %' ) AS perfCount
INNER JOIN ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage % base' ) AS perfBase
ON perfCount.object_name = perfBase.object_name;
answered Jan 27 '17 at 19:18
Vic WorkVic Work
1976
That query returns results, but they are not correct. Can you add some description of what you were expecting the query to do and why?
– HunterX3
Jan 27 '17 at 20:50
add a comment |
Try this and see if this gives a value other than zero:
SELECT perfCount.object_name ,
perfCount.counter_name ,
CASE WHEN perfBase.cntr_value = 0 THEN 0
ELSE ( CAST(perfCount.cntr_value AS float) / perfBase.cntr_value ) * 100
END AS cntr_Value
FROM ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage %' ) AS perfCount
INNER JOIN ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage % base' ) AS perfBase
ON perfCount.object_name = perfBase.object_name;
answered Jan 27 '17 at 19:18
Vic WorkVic Work
1976
Try this and see if this gives a value other than zero:
SELECT perfCount.object_name ,
perfCount.counter_name ,
CASE WHEN perfBase.cntr_value = 0 THEN 0
ELSE ( CAST(perfCount.cntr_value AS float) / perfBase.cntr_value ) * 100
END AS cntr_Value
FROM ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage %' ) AS perfCount
INNER JOIN ( SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
AND counter_name = 'CPU usage % base' ) AS perfBase
ON perfCount.object_name = perfBase.object_name;
answered Jan 27 '17 at 19:18
Vic WorkVic Work
1976
answered Jan 27 '17 at 19:18
Vic WorkVic Work
1976
answered Jan 27 '17 at 19:18
Vic WorkVic Work
1976
answered Jan 27 '17 at 19:18
Vic WorkVic Work
1976
1976
That query returns results, but they are not correct. Can you add some description of what you were expecting the query to do and why?
– HunterX3
Jan 27 '17 at 20:50
add a comment |
That query returns results, but they are not correct. Can you add some description of what you were expecting the query to do and why?
– HunterX3
Jan 27 '17 at 20:50
That query returns results, but they are not correct. Can you add some description of what you were expecting the query to do and why?
– HunterX3
Jan 27 '17 at 20:50
That query returns results, but they are not correct. Can you add some description of what you were expecting the query to do and why?
– HunterX3
Jan 27 '17 at 20:50
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%2f151749%2fsql-server-2016-counter-reporting-zero%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
I opened a Microsoft Connect item: connect.microsoft.com/SQLServer/feedback/details/3106449
– HunterX3
Oct 13 '16 at 16:00