none
RING_BUFFER_SCHEDULER_MONITOR reporting over 100% ProcessUtilization RRS feed

  • Question

  • I am using RING_BUFFER_SCHEDULER_MONITOR to get cpu history for the server. For some reason on our new intel box (system info below) we are experiencing above 100% (in the xml below it shows 129) ProcessUtilization. Is there any correction I need to be applying to this value or why would it be above 100 ever?

    Thank you

    <Record id="2835" type="RING_BUFFER_SCHEDULER_MONITOR" time="6535178385">
      <SchedulerMonitorEvent>
        <SystemHealth>
          <ProcessUtilization>129</ProcessUtilization>
          <SystemIdle>32</SystemIdle>
          <UserModeTime>30068412745</UserModeTime>
          <KernelModeTime>988422336</KernelModeTime>
          <PageFaults>604</PageFaults>
          <WorkingSetDelta>1974272</WorkingSetDelta>
          <MemoryUtilization>100</MemoryUtilization>
        </SystemHealth>
      </SchedulerMonitorEvent>
    </Record>

    SELECT  CAST(record AS XML), *
    FROM    sys.dm_os_ring_buffers (NOLOCK)
    WHERE   ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE N'%<SystemHealth>%'
    AND record LIKE '%>129<%'
    ORDER BY [timestamp] DESC
    CounterName CounterValue
    InstanceUpTime 1 days 22:47:31
    ServerUpTime 75 days 16:12:10
    IsClustered Clustered
    ClusterNodeCnt 2
    AuthenticationMode Mixed
    Port 1433
    SystemManufacturer System Manufacturer: 'Dell Inc.', System Model: 'PowerEdge R910'.
    ProcessorInfo Intel(R) Xeon(R) CPU E7- 4850  @ 2.00GHz
    CpuCount 80
    HyperthreadRatio 20
    TotalPhysicalMemoryMB 1,048,562.82
    AvailablePhysicalMemoryMB 28,388.65
    MemInUseMB 1,002,576.75
    MemUtil% 100
    SystemMemoryState Available physical memory is high
    MaxDegreeOfParallelism 4
    ClrEnabled 1
    RemoteAdminConnections 1
    OptimizeForAdHocWorkloads 1
    xp_cmdshell 1
    DefaultTraceEnabled 1
    ProcessId 35132
    IsSingleUser 0
    ProductVersion 11.0.2100.60
    Version Microsoft SQL Server 2012 - 11.0.2100.60 (X64)  |  Feb 10 2012 19:39:15  |  Copyright (c) Microsoft Corporation |  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) | 
    ProductLevel RTM
    ResourceLastUpdateDateTime Feb 10 2012  9:09PM
    ResourceVersion 11.00.2100
    IsIntegratedSecurityOnly Not Integrated security
    EngineEdition Enterprise Edition
    LicenseType DISABLED
    NumLicenses NULL
    BuildClrVersion v4.0.30319
    Collation SQL_Latin1_General_CP1_CI_AS
    CollationID 872468488
    ComparisonStyle 196609
    ProductEdition NULL
    IsFullTextInstalled Full-text is installed
    SqlCharSet 1
    SqlCharSetName iso_1
    SqlSortOrderID 52
    SqlSortOrderName nocase_iso


    Gokhan Varol

    Tuesday, February 12, 2013 4:28 AM

All replies

  • Hi,

    I would agree with you that process utilization that high is not normal. You may have to dig deeper to see if it is related to proc plans or the like.

    Here are some interesting links that may or may not give you some leads on troubleshooting this issue.

    http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx

    http://mssqlwiki.com/2010/11/30/how-to-find-sql-server-and-system-cpu-usage-history/

    Hope this helps until an MVP or MSFT Eng replies.

    Frank.


    Frank Garcia

    Tuesday, February 12, 2013 2:55 PM
  • Are you saying it's possible the ProcessUtilization will report over 100% ?

    Gokhan Varol

    Tuesday, February 12, 2013 3:06 PM
  • This doesnt look correct. I will look in to this and will update you

    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, February 12, 2013 3:20 PM
    Moderator
  • How frequent this issue is can u share the output of below query

     declare @ms_now bigint
     select @ms_now = ms_ticks from sys.dm_os_sys_info;
    select top 15 record_id,
      dateadd(ms, -1 * (@ms_now - [timestamp]), GetDate()) as EventTime,
      SQLProcessUtilization,
      SystemIdle,
      100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
     from (
      select
       record.value('(./Record/@id)[1]', 'int') as record_id,
       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
       timestamp
      from (
       select timestamp, convert(xml, record) as record
       from sys.dm_os_ring_buffers
       where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
       and record like '%<SystemHealth>%') as x
      ) as y
     order by record_id desc


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, February 12, 2013 3:26 PM
    Moderator
  • Hi,

    I'm saying it is not normal.

    Karthick will be able to help you further.

    Frank.


    Frank Garcia

    Tuesday, February 12, 2013 3:49 PM
  • This is not happening all the time, I just checked and the numbers were not illogical. The server has more traffic later in the evening, I will check them again and I'll report them.

    Below is the similar query from ring_buffers I pulled last night which I reported one invalidity of it.


    Gokhan Varol

    Tuesday, February 12, 2013 4:36 PM
  • Hello Gokhan,

    This looks like RDTSC drift issue. Can you share the output of RDTSCtest.exe. If you dont have the exe let me know I can share it with you.


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, February 15, 2013 5:45 AM
    Moderator
  • Hi Karthick

    Can I run this utility on a 80 core production system without causing trouble?

    If the utility did not ship with windows 2008 r2 we do not have it. Would you provide information on how to get it and how to run it please.Can I run it anytime or do I need to run it when cpu shows above 100 in sql server, by the way even sql showed cpu over 100 task manager seemed to be sccurate.Does the tool run for s long time?

    Thanks


    Gokhan Varol

    Friday, February 15, 2013 6:17 AM
  • This Exe will complete in second's and wil not have any performance impact.

    Let me know your email address so i can share the exe. It is just 100KB.

    Thanks


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, February 15, 2013 6:25 AM
    Moderator
  •  I can work on this after tomorrow afternoon.

    Thanyouiu


    Gokhan Varol


    Now you remove your email address from forum

    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, February 15, 2013 3:09 PM
    Moderator
  • Karthick

    The executable worked from my machine but does not work from the server, it crashes


    Gokhan Varol

    Friday, February 15, 2013 3:17 PM
  • Hello Gokhan,

    Use -mt parameter.


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Sunday, February 17, 2013 4:37 AM
    Moderator
  • Hi Karthick

    The server started acting weird after I got this error I posted, some applications that report cpu with graphs only reported cpus on 1 of the 4 nodes for over and half an hour etc. This is a production server, I do not want to risk anything on this server, this executable does not seem that innocent to me. Any suggestions?

    Thanks


    Gokhan Varol

    Monday, February 18, 2013 5:26 AM
  • Hi Karthick

    The server started acting weird after I got this error I posted, some applications that report cpu with graphs only reported cpus on 1 of the 4 nodes for over and half an hour etc. This is a production server, I do not want to risk anything on this server, this executable does not seem that innocent to me. Any suggestions?

    Thanks


    Gokhan Varol

    That by itself tells us that the problem is outside SQLServer and the exe which I gave you. This Exe just reports if there is CPU Drift and innocent. There is some thing outside

    1. Run--> Powercfg.cpl and make sure you have selected always ON or High performance.

    2. Check if you have any BIOS option in your system which is stepping down the CPU speed or cooling down the CPU and disable them. 


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, February 18, 2013 5:59 AM
    Moderator
  • http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx

    That's a great blog post Frank, thanks for posting!

    Gokhan, what do the perfmon CPU counters show, when the ring buffer shows cpu >100%?

    I haven't used these before (I will try them ASAP!), if they work by summing up batch completions, they could show blips like that after long-running jobs complete, jobs that run longer than whatever their sample period, just a thought.

    Josh

    • Proposed as answer by Frank J Garcia Tuesday, February 19, 2013 1:57 PM
    • Unproposed as answer by GV1973 Tuesday, February 19, 2013 2:01 PM
    Monday, February 18, 2013 4:23 PM
  • Hey Gokhan,

    Did you get chance to check the points which I mentioned in earlier reply.


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, February 19, 2013 2:05 PM
    Moderator
  • Hi Karthick

    The system is set for high performance in power config. I do not have access to bios nor I can shut the server down. Below is rdtsctest results

    c:\SysinternalsSuite>rdtsctest.exe -mt

    -- Current CPU Speeds --
    Runtime              CPU  ExpectedMHz ActualMHz RDTSCTicks           DriftMS
    -------------------- ---- ----------- --------- -------------------- ----------------
    2013-02-19 08:27:46     0        1064      1063 0x0001B29EC7679ED8E               0.0
    2013-02-19 08:27:46     1        1064      1063 0x0001B29EC767A267D               0.0
    2013-02-19 08:27:46     2        1064      1063 0x0001B29EC767A6421               0.0
    2013-02-19 08:27:46     3        1064      1063 0x0001B29EC767A9C55               0.0
    2013-02-19 08:27:46     4        1064      1062 0x0001B29EC767C3040               0.1
    2013-02-19 08:27:46     5        1064      1065 0x0001B29EC767C6BD7               0.2
    2013-02-19 08:27:46     6        1064      1066 0x0001B29EC767C9BA2               0.2
    2013-02-19 08:27:46     7        1064      1066 0x0001B29EC767CC813               0.2
    2013-02-19 08:27:46     8        1064      1062 0x0001B29EC767CF1C6               0.2
    2013-02-19 08:27:46     9        1064      1065 0x0001B29EC767D1DAC               0.2
    2013-02-19 08:27:46    10        1064      1065 0x0001B29EC767D48CE               0.2
    2013-02-19 08:27:46    11        1064      1065 0x0001B29EC767D7330               0.2
    2013-02-19 08:27:46    12        1064      1065 0x0001B29EC767DAECA               0.2
    2013-02-19 08:27:46    13        1064      1065 0x0001B29EC7F5F2D53             140.3
    2013-02-19 08:27:46    14        1064      1065 0x0001B29EC7F61F150             140.4
    2013-02-19 08:27:46    15        1064      1062 0x0001B29EC7F63140F             140.5
    2013-02-19 08:27:46    16        1064      1065 0x0001B29EC7F634840             140.5
    2013-02-19 08:27:46    17        1064      1065 0x0001B29EC82572BE7             187.1
    2013-02-19 08:27:46    18        1064      1065 0x0001B29EC8258D415             187.2
    2013-02-19 08:27:46    19        1064      1065 0x0001B29EC8F33C6E4             389.9
    2013-02-19 08:27:46    20        1064      1065 0x0001B29EC8F357F6F             390.0
    2013-02-19 08:27:46    21        1064      1062 0x0001B29EC8F35D2AC             390.0
    2013-02-19 08:27:46    22        1064      1066 0x0001B29EC9B5185C9             580.9
    2013-02-19 08:27:46    23        1064      1066 0x0001B29ECA7EFCE49             779.9
    2013-02-19 08:27:46    24        1064      1065 0x0001B29ECB4CAF35B             982.6
    2013-02-19 08:27:46    25        1064      1062 0x0001B29ECC0ABD436            1169.9
    2013-02-19 08:27:46    26        1064      1065 0x0001B29ECC0AE25A3            1170.1
    2013-02-19 08:27:46    27        1064      1062 0x0001B29ECCD89CE8C            1372.8
    2013-02-19 08:27:46    28        1064      1066 0x0001B29ECCD8B74B3            1372.9
    2013-02-19 08:27:46    29        1064      1066 0x0001B29ECDA64FCCC            1575.5
    2013-02-19 08:27:46    30        1064      1062 0x0001B29ECDA6764C6            1575.7
    2013-02-19 08:27:46    31        1064      1065 0x0001B29ECDA67C543            1575.7


    Gokhan Varol

    Tuesday, February 19, 2013 4:28 PM
  • Hello Gokhul,

    I was little busy last few days. Take a down time and check the BIOS. I will review the SQL Code to understand what can cause this.

     


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, March 8, 2013 9:02 AM
    Moderator
  • I could not find anything more than CPU drift which can cause this. Actually I see the CPU drift to go up to 1.5 seconds here which is pretty high and could cause calculation issues with process utilization.Check the power plan in BIOS see if the utilization number gets reported appropriately.You can also check with your hardware vendor to identify the cause of drift.


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, March 18, 2013 12:19 PM
    Moderator
  • Hello Karthick

    My new customer has the same issue (HP, SQL2012SP1, 256GB, 80CPU)

    I'm trying to find out the cause of the problem.

    So I'd like to check CPU drift you mentioned using RDTSCTest.exe.

    Can I get one? Can you send it to me by email ?

    My email is jskim@sqlroad.com

    Thanks in advance.


    Best Regards, Jungsun Kim

    Tuesday, June 17, 2014 7:35 AM
  • Hey  Karthick,

    Can you provide how this data is calculated? Just general steps?

    It is based a 1 second interval? For example, the 10%, is 10% from a one second interval monitring, like Task Manager do for each process ?


    []'s | Rodrigo Ribeiro Gomes | MCTS/MCITP Dev/DBA

    Wednesday, May 29, 2019 1:50 AM