Post reply

CPU Utilization more than 95% for more than 3 hours

  • October 15, 2014 at 4:12 am

    #291017

    HI Experts,

    In one of our servers CPU Utilization more than 95% for more than 3 hours, can anyone please guide me on troubleshoot or to find th root cause of this issue.

    No jobs,no blocking exists... Top CPU processes in sysprocesses are system processes .CHECKPOINT is showing CPU 21256586 which is highest.

    TIA

  • October 15, 2014 at 4:17 am

    #1752443

    Sysprocesses is useless for this, it's the cumulative totals since SQL started, so of course the system processes will show high.

    Did you have any form of extended events trace running at the time? Any profiler trace? Were you looking at active processes during those 3 hours? Do you have wait information from that time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 15, 2014 at 4:42 am

    #1752454

    Thanks Gail for the reply.

    We don't have any Extended events or trace running. Was check the sysprocesses only.

    Please advise what to check on this.

  • October 15, 2014 at 4:47 am

    #1752457

    If you're out of the period, there's not really any way to know what was happening without having set up the extended events ahead of time, or turning it on when you're seeing an issue. During the period, you could try querying the sys.dm_exec_requests to see which queries are using the most resources. But after the fact, there's nothing that records all the calls on the system unless you set it up.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • October 15, 2014 at 5:08 am

    #1752467

    A proper profiler-to-local-disk script can be run to capture heavy CPU users with very little overhead on the system. I have run my very tight collection of fields/events on systems with well over 1K executions per second with minimal impact.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • October 15, 2014 at 5:15 am

    #1752471

    Thanks Grant and Kevin,

    CPU is still high .

    What all events i need to select while create a profiler trace in this case?

  • October 15, 2014 at 5:20 am

    #1752472

    TheSQLGuru (10/15/2014)

    A proper profiler-to-local-disk script can be run to capture heavy CPU users with very little overhead on the system.

    And an extended events session will have even less.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 15, 2014 at 5:29 am

    #1752476

    Thanks Gail,

    Since we are still facing the issue i hope creating an Extended Event now also will give some insight.

    Can you please help me on creatin one

  • October 15, 2014 at 5:33 am

    #1752478

    Create an extended events session or server-side trace to record what's running against the server. Let it run long enough to catch a good sample of data. Look at the results, identify what was using the most CPU, then analyse from there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 15, 2014 at 5:40 am

    #1752479

    Have your already determined that it's SQL Server consuming the CPU and not something else? The network admins could have other things (system updates, etc.) running on the server. It would be worth a 10-second check to make sure you're looking in the right area.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • October 15, 2014 at 5:50 am

    #1752482

    Thanks Gail,

    Yes its SQL Server using more than 95% now.

  • October 15, 2014 at 6:24 am

    #1752503

    I have got around 130 trace files now.

    In between the CPU came down to 13 at 7:54 and back to 95 again i have trace in between those times.

    Can anyone help me to pin point the cause these traces?

  • October 15, 2014 at 6:39 am

    #1752508

    I'm sure I've given you this link before.

    https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 15, 2014 at 7:05 am

    #1752514

    Yes Gail,

    Thanks Again.

  • October 15, 2014 at 8:02 am

    #1752544

    Use extended events because they're much lower cost than trace (much, much, much lower). Capture rpc_complete and sql_batch_complete. That can show you which queries are running to completion and what resources they use.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply