none
Sudden High CPU usage RRS feed

  • Question

  • We have been frequently experiencing the sudden rise in CPU usage from low value(<=20) to 95-99 and continues with the same usage value until the sql server service is restarted. The database server stops working and application halts. 

    How to know what exactly caused this. 

    After sql server service restart, it works fine.


    • Edited by Curendra Thursday, September 12, 2019 10:56 AM
    Thursday, September 12, 2019 10:55 AM

All replies

  • A common cause of these symptoms is query plan regression, due to one or more sub-optimal execution plans. This may be due to parameter sniffing or parallel queries needing attention to query and index tuning detail.

    If you are using SQL Server 2016 or later, consider enabling the query store to identify and remediate problem queries. Also consider changing the config values for 'max degree of parallelism' and 'cost threshold for parallelism' to mitigate impact of large parallel queries.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, September 12, 2019 11:06 AM
    Moderator
  • what is the sql version and service pack which is running... how many processors it has along with RAM.

    are you still able to query from SQL server when CPU is high?.

    If the CPU is during one particular time (or)at the time-try running select * from sys.dm_exec_requests -

    check how many sessions are running with what exactly in it.

    Like blocking in the system,long running jobs,wait time and waittype.

    More ever I would suggest to check the below link- for initial check.

    https://blogs.msdn.microsoft.com/docast/2017/07/30/sql-high-cpu-troubleshooting-checklist/

    Ofcourse check any errors like cpu yielding,threads issues in sql server error log for the same.


    Regards, S_NO "_"

    Thursday, September 12, 2019 11:09 AM
  • ---This first thing to check if CPU is at 100% is to look for parallel queries:


    -- Tasks running in parallel (filtering out MARS requests below):
    select * from sys.dm_os_tasks as t
     where t.session_id in (
       select t1.session_id
        from sys.dm_os_tasks as t1
       group by t1.session_id
      having count(*) > 1
      and min(t1.request_id) = max(t1.request_id));

    -- Requests running in parallel:
     select *
       from sys.dm_exec_requests as r
       join (
               select t1.session_id, min(t1.request_id)
              from sys.dm_os_tasks as t1
             group by t1.session_id
            having count(*) > 1
               and min(t1.request_id) = max(t1.request_id)
          ) as t(session_id, request_id)
         on r.session_id = t.session_id
        and r.request_id = t.request_id;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, September 12, 2019 1:38 PM
    Answerer
  • There are many reasons for high CPU.  The most common reason is the max server memory is not set correctly and the server is massively paging.  I would check that first.

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-2014&redirectedfrom=MSDN

    Then I would start here:

    https://blogs.msdn.microsoft.com/docast/2017/07/30/sql-high-cpu-troubleshooting-checklist/

    Thursday, September 12, 2019 1:55 PM
    Moderator
  • @Cathy Ji-- Kindly Await for the user confirmation prior to Proposed..trying to undestand the issues and need more details from him.

    Regards, S_NO "_"

    Friday, September 13, 2019 3:30 PM
  • Thank you everyone for the replies.

    The server has 2 physical CPUs, 16 logical CPUs and 64GB RAM. The max server memory is set to default value. I also started using query store to see regressed queries. There are queries that have multiple plans. There is another thing called parameter sniffing. These have been overwhelming for me.

    So, what is the best practice to deal with these things and how can I know that the changes I make work.

    One more question, is there a way to see how the queries ran last week or any time in history?


    • Edited by Curendra Monday, September 16, 2019 1:50 AM
    Monday, September 16, 2019 1:49 AM
  • Please set MAX memory param to 32 GB at least ( a start point) 

    >>>One more question, is there a way to see how the queries ran last week or any time in history?

    No, but now Query store  is your friend...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 16, 2019 4:08 AM
    Answerer
  • >>>One more question, is there a way to see how the queries ran last week or any time in history?

    No but now Query store is your friend...

    Exactly.

    Y'know, actually, if your CPU is running even a steady 20%, that is mildly high.

    The steady number should be fairly low, with spikes up to higher numbers.  If you don't have that headroom you will end up worrying about it - and probably not having the happiest users.

    Josh


    • Edited by JRStern Monday, September 16, 2019 4:50 AM
    Monday, September 16, 2019 4:49 AM