sys.dm_os_performance_counters to collect cpu usage

Question

Hello everyone,

I use sys.dm_os_performance_counters to collect cpu usage for SQL Server 2016 SP3, but the values are always 0.
Is it normal?

Thanks,

Answer 1

Hi @Igor Gelin ,

Could you share us the scripts that you use. Did you check the CPU used for SQL 2016 from other method?

Quote from a similar thread;

>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 the documentation to show that the counter is useless now.

Did you have any high CPU issues? To troubleshoot high CPU issues, we can use Windows task manager resource monitor or performance monitor or SQL Standard report or some DMVS, etc. Please refer to below blog to get detail troubleshoot steps.

SQL High CPU troubleshooting checklist

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

I use sys.dm_os_performance_counters to collect cpu usage

And how does your query look like? Which SQL Server version are you using? We can't guess this details.

What do this query return?

-- Utilization History
DECLARE @ticksNow bigint, @ticksMs bigint;

-- Calculate ticks to timestamp
SELECT @ticksNow = OSI.cpu_ticks / CONVERT(float, OSI.cpu_ticks/ms_ticks)
      ,@ticksMs = cpu_ticks/ms_ticks
FROM sys.dm_os_sys_info AS OSI;

;WITH util AS
   (SELECT RBS.Rc.value('(./Record/@id)[1]', 'bigint') AS RecordID
          ,RBS.Rc.value('(//SystemHealth/SystemIdle)[1]', 'bigint') AS SystemIdle
          ,RBS.Rc.value('(//SystemHealth/ProcessUtilization)[1]', 'bigint') AS ProcessUtil
          ,RBS.Rc.value('(//SystemHealth/MemoryUtilization)[1]', 'bigint') AS MemoryUtil
          ,RBS.Rc.value('(//SystemHealth/PageFaults)[1]', 'bigint') AS PageFaults
          ,RBS.Rc.value('(//SystemHealth/UserModeTime)[1]', 'bigint') AS UserModeTime
          ,RBS.Rc.value('(//SystemHealth/KernelModeTime)[1]', 'bigint') AS KernelModeTime
          ,RBS.EventStamp
    FROM (SELECT ORB.[timestamp] AS EventStamp
                ,CONVERT(XML, ORB.record) AS Rc 
          FROM sys.dm_os_ring_buffers AS ORB
          WHERE ORB.ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
          ) AS RBS
    )
SELECT UT.RecordID
      ,CONVERT(datetime2(2), DATEADD(ms, -1 * (@ticksNow - UT.EventStamp), GETDATE())) AS EventTime
      ,UT.SystemIdle
      ,UT.ProcessUtil      
      ,UT.MemoryUtil
      ,UT.PageFaults
      ,UT.UserModeTime
      ,UT.KernelModeTime
FROM util AS UT
ORDER BY UT.RecordID DESC;

Answer 3

It is really useful script to know what is happening at the AWS RDS server if you don't have access to console