3

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.

0

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.

  • 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
-1

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; 
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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