locked
SqlServer Service consuming 100% CPU RRS feed

  • Question

  • How can I reduce the CPU usage of SqlServr? This is sqlserver 2012.


    • Edited by Avyayah Thursday, March 26, 2020 4:58 AM
    Thursday, March 26, 2020 4:53 AM

Answers

  • Hi Avyayah,

    Is the reply helpful?

    Is the CPU usage of SQL Server reduced?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Avyayah Monday, June 8, 2020 6:28 PM
    Tuesday, March 31, 2020 1:04 AM

All replies

  • hi,

    You need to check for 

    1. Which Queries are running on SQL. (During specific time or over span of period based on alerts you are receiving) 

    2. Queries making use of indexes or not using execution plan.

    3. If queries using indexes identify if any implicit conversion happening.

    Identification of queries running is necessary to understand CPU utilization.


    Regards, AKash Pawar

    Thursday, March 26, 2020 5:03 AM
  • ---This first thing to check if CPU is at 100% is to look for parallel queries:

    sp_askbrent @expertmode=1

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

    And how much memory have you set up to SQL Server?


    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, March 26, 2020 5:20 AM
    Answerer
  • Hi Avyayah,

    > How can I reduce the CPU usage of SqlServr? This is sqlserver 2012.

    1.Use the performance monitor to check the CPU usage of the SQL server in the server. If the high CPU usage is caused by the SQL server, continue to the next step of SQL Server’s inspection. If not, check other applications.
    2.Check whether the  SQL Server is working properly or not, check the error log to see whether there is the problems such as 17883/17884 occur or not, and be sure whether there is one serious problems such as Access Violation occurs or not; 
    3.Find out the most CPU-consuming statements that are running in SQL Server when the CPU is 100%, and then optimize them;
    -- list the top 50 CPU used

    select highest_cpu_queries.*,q.dbid, q.objectid, q.number, q.encrypted, q.[text] from (select top 50 qs.* from 
    sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries 
    cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc 
    go

    -- stored procedures that do recompilation most often

    select top 25 sql_text.text, sql_handle, plan_generation_num, execution_count,dbid, objectid
    from sys.dm_exec_query_stats a
    cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
    where plan_generation_num >1
    order by plan_generation_num desc
    go

    4.Reduce system load or upgrade hardware

    Note:
    The operations of SQL Server use CPU resources more intensively are as follows:
    1.Compile and recompile;
    2.Sort and Aggregation;
    3.Table join (Join) operation
    4.Max Degree of Parallelism
    5.Cost Threshold of Parallelism
    6.Max Worker Threads

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, March 26, 2020 8:58 AM
  • Hi Avyayah,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 27, 2020 1:07 AM
  • Hi Avyayah,

    Is the reply helpful?

    Is the CPU usage of SQL Server reduced?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Avyayah Monday, June 8, 2020 6:28 PM
    Tuesday, March 31, 2020 1:04 AM