none
database high total worker time - high CUPs RRS feed

  • Question

  • Hi  I am keep monitoring the DB  and total_worker_time is high and steadily remain same at the time. When I checked with following SQL

     

    WITH DB_CPU_Stats
    AS
    (SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], 
            SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms],SUM(execution_count)  AS [ExecutionCount],
    SUM(total_rows)  AS [RowsReturned]
     FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
     CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS pa
     GROUP BY DatabaseID)
    SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
           [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)], 
           CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) 
       OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent],
       [RowsReturned],
    [ExecutionCount]
    FROM DB_CPU_Stats
    WHERE DatabaseID <> 32767 -- ResourceDB
    ORDER BY [CPU Rank] OPTION (RECOMPILE);

    I restart the SQL Service and came down to 57%.  I am not sure s that any relation to SQL number of compilation and number of executions.

    Objective to reduce the total_worker_time to have better performance of the DB

    after restart 



    • Edited by ashwan Tuesday, June 25, 2019 3:48 AM
    Tuesday, June 25, 2019 3:18 AM

Answers

  • Waits stats , what does it return? If you use SQL Server 2016+ please enable Query Store and   you will see what queries are problematic in terms of CPU as well.

    ;WITH Waits AS
    (
      SELECT 
        wait_type,
        wait_time_ms  /1000. AS wait_time_s, 
             100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
             ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
          FROM sys.dm_os_wait_stats
          WHERE wait_type NOT IN (N'CLR_SEMAPHORE', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',   N'LAZYWRITER_SLEEP',
            N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
            N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
            N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
            N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
            N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
            N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
            N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
            N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
            N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
            N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP',
    N'ONDEMAND_TASK_QUEUE',N'BROKER_RECEIVE_WAITFOR',N'QDS_ASYNC_QUEUE'
    )
    )
          --filter out additional irrelevant waits
    ,cte1
    AS
    (
    SELECT 
      W1.wait_type,
      CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12,2))AS pct
      ,rn
     --- CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
    FROM Waits AS W1

     --- JOIN Waits AS W2
      ---  ON W2.rn<= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    -----HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
    ) SELECT * FROM 
    ( SELECT wait_type,wait_time_s,pct,SUM(pct) OVER(ORDER BY rn
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runqty FROM cte1
    ) AS Der WHERE runqty <95
    ORDER BY runqty;


    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

    Tuesday, June 25, 2019 5:47 AM
    Moderator
  • Hi ashwan,

    In SQL Server Management Studio run the "Performance - Top Queries By Total CPU Time" report to find high CPU query.


    You can try to troubleshoot "SQL Server consuming High CPU" follow below blog.

    SQL High CPU troubleshooting checklist


    Hope this could help you.

    Best regards,
    Cathy ji


    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

    Wednesday, June 26, 2019 7:00 AM
  • Do you see in Query Store what query takes almost CPU? I do not know what application that works against that db does, but perhaps you need to update statistics> What does the below query returns? 

    ----Here’s a sample query you can use to find the statistics that haven’t been updated in over 30 days:

    SELECT

       sch.name + '.' + so.name AS
    "Table",
       ss.name AS
    "Statistic",
         CASE
               WHEN ss.auto_Created = 0 AND ss.user_created = 0 THEN 'Index Statistic'
               WHEN ss.auto_created = 0 AND ss.user_created = 1 THEN 'User Created'
               WHEN ss.auto_created = 1 AND ss.user_created = 0 THEN 'Auto Created'
               WHEN ss.AUTO_created = 1 AND ss.user_created = 1 THEN 'Not Possible?'
         END 
    'Statistic Type',
       CASE
                 WHEN ss.has_filter = 1 THEN 'Filtered Index'
               WHEN ss.has_filter = 0 THEN 'No Filter'

         END "Filtered?",
       CASE

               WHEN ss.filter_definition
    IS NULL THEN ''

               WHEN ss.filter_definition
    IS NOT NULL THEN ss.filter_definition

         END  "Filter Definition",

       sp.last_updated AS
    "Stats Last Updated",

       sp.rows AS "Rows",

       sp.rows_sampled AS
    "Rows Sampled",

       sp.unfiltered_rows AS
    "Unfiltered Rows",

         sp.modification_counter AS
    "Row Modifications",
    sp.steps
     "Histogram Steps"

    FROM sys.stats ss

    JOIN sys.objects so ON ss.object_id = so.object_id

    JOIN sys.schemas sch ON so.schema_id = sch.schema_id

    OUTER APPLY sys.dm_db_stats_properties(so.object_id, ss.stats_id) AS sp

    WHERE so.TYPE = 'U'

    AND sp.last_updated < getdate() - 30

    ORDER BY sp.last_updated
    DESC;


    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

    Sunday, June 30, 2019 5:40 AM
    Moderator

All replies

  • Waits stats , what does it return? If you use SQL Server 2016+ please enable Query Store and   you will see what queries are problematic in terms of CPU as well.

    ;WITH Waits AS
    (
      SELECT 
        wait_type,
        wait_time_ms  /1000. AS wait_time_s, 
             100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
             ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
          FROM sys.dm_os_wait_stats
          WHERE wait_type NOT IN (N'CLR_SEMAPHORE', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',   N'LAZYWRITER_SLEEP',
            N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
            N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
            N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
            N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
            N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
            N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
            N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
            N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
            N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
            N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP',
    N'ONDEMAND_TASK_QUEUE',N'BROKER_RECEIVE_WAITFOR',N'QDS_ASYNC_QUEUE'
    )
    )
          --filter out additional irrelevant waits
    ,cte1
    AS
    (
    SELECT 
      W1.wait_type,
      CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12,2))AS pct
      ,rn
     --- CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
    FROM Waits AS W1

     --- JOIN Waits AS W2
      ---  ON W2.rn<= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    -----HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
    ) SELECT * FROM 
    ( SELECT wait_type,wait_time_s,pct,SUM(pct) OVER(ORDER BY rn
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runqty FROM cte1
    ) AS Der WHERE runqty <95
    ORDER BY runqty;


    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

    Tuesday, June 25, 2019 5:47 AM
    Moderator
  • Hi ashwan,

    In SQL Server Management Studio run the "Performance - Top Queries By Total CPU Time" report to find high CPU query.


    You can try to troubleshoot "SQL Server consuming High CPU" follow below blog.

    SQL High CPU troubleshooting checklist


    Hope this could help you.

    Best regards,
    Cathy ji


    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

    Wednesday, June 26, 2019 7:00 AM
  • Hi Uri

    Yes I enabled Query store and found a SQL with 100% cost. But not sure its leads to high worker count. DB is pritty slow.  This Environment is Always on enabled. But not sure performance any impact  . Following is the wait events on the DB

    Thursday, June 27, 2019 3:09 PM
  • Thank you Cathy  Good information. I collect the information as well .
    Thursday, June 27, 2019 3:10 PM
  • Hi Uri

    Currently CPU % is 99 and following waits on the instance, But Please note This particular database is not add to availability group yet. 

     

    Saturday, June 29, 2019 4:50 AM
  • The first query you used gives accumulated information so things might see high. Did you find out query causing high CPU ?

    What issue you are facing please be exact 


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, June 29, 2019 5:22 AM
    Moderator
  • Do you see in Query Store what query takes almost CPU? I do not know what application that works against that db does, but perhaps you need to update statistics> What does the below query returns? 

    ----Here’s a sample query you can use to find the statistics that haven’t been updated in over 30 days:

    SELECT

       sch.name + '.' + so.name AS
    "Table",
       ss.name AS
    "Statistic",
         CASE
               WHEN ss.auto_Created = 0 AND ss.user_created = 0 THEN 'Index Statistic'
               WHEN ss.auto_created = 0 AND ss.user_created = 1 THEN 'User Created'
               WHEN ss.auto_created = 1 AND ss.user_created = 0 THEN 'Auto Created'
               WHEN ss.AUTO_created = 1 AND ss.user_created = 1 THEN 'Not Possible?'
         END 
    'Statistic Type',
       CASE
                 WHEN ss.has_filter = 1 THEN 'Filtered Index'
               WHEN ss.has_filter = 0 THEN 'No Filter'

         END "Filtered?",
       CASE

               WHEN ss.filter_definition
    IS NULL THEN ''

               WHEN ss.filter_definition
    IS NOT NULL THEN ss.filter_definition

         END  "Filter Definition",

       sp.last_updated AS
    "Stats Last Updated",

       sp.rows AS "Rows",

       sp.rows_sampled AS
    "Rows Sampled",

       sp.unfiltered_rows AS
    "Unfiltered Rows",

         sp.modification_counter AS
    "Row Modifications",
    sp.steps
     "Histogram Steps"

    FROM sys.stats ss

    JOIN sys.objects so ON ss.object_id = so.object_id

    JOIN sys.schemas sch ON so.schema_id = sch.schema_id

    OUTER APPLY sys.dm_db_stats_properties(so.object_id, ss.stats_id) AS sp

    WHERE so.TYPE = 'U'

    AND sp.last_updated < getdate() - 30

    ORDER BY sp.last_updated
    DESC;


    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

    Sunday, June 30, 2019 5:40 AM
    Moderator

  • Your wait types are normal and top ones are related to Service Broker and replication related. As your query return at server level wait type info, you do see these wait types irrespective you enabled AO-AG for your database.

    Could you check the queries that is taking more time, you can use the script in here. Try to understand why these queries are taking time and post the plan here for us to help you better.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    Sunday, June 30, 2019 6:09 AM
    Answerer
  • Hi Uri

    Nothing has been return and stats are up todate.

    regards 

    Monday, July 1, 2019 12:58 AM
  • Shashank

    I enabled QS  and see the attachments. Issue with this DB is ETL team complain this DB is slow . But I cant see big issue with SQL by looking at Query store. But I had concern on HADR waits as its  looks to me all normal 

    Monday, July 1, 2019 1:01 AM
  • ETL team complains? Do you transfer huge data over the network? Is that OLTP or DW oriented database?

    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, July 1, 2019 4:33 AM
    Moderator
  • Shashank

    I enabled QS  and see the attachments. Issue with this DB is ETL team complain this DB is slow . But I cant see big issue with SQL by looking at Query store. But I had concern on HADR waits as its  looks to me all normal 

    Ask ETL team to be precise when they say DB is slow, what do they mean by slow, what stats they have to say that DB is slow. Is it that job is taking long time ? there could be n number of reasons for that. Dont just start finding fault in database please first try to understand what is "actually" the problem.

    People always find pretty easy to blame database when there own things are not in place


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Monday, July 1, 2019 7:06 AM
    Moderator
  • Hi Shashank

    You are correct . Please balm  on the DBs when its getting slow.

    Currently Server CPU is high now while ETL process to the DB . This DB loading data from other source .

    hi SQLZealots I run your sqls. Output  provided and and looks like  wait type LOGPOOL_FREEPOOLS


    Query store output

    blocks on the DB


    • Edited by ashwan Tuesday, July 2, 2019 11:52 PM
    Tuesday, July 2, 2019 11:18 PM
  • hi all Database waits are change following output time to time as data loading is on. I think this is normal while loading data to DB 

    SELECT
        [DOWT].[session_id],[DOWT].[exec_context_id],
        [DOWT].[wait_duration_ms],[DOWT].[wait_type],
        [DOWT].[blocking_session_id],[DOWT].[resource_description],
        CASE [DOWT].[wait_type]
            WHEN N'CXPACKET' THEN
                RIGHT ([DOWT].[resource_description],
                CHARINDEX (N'=', REVERSE ([DOWT].[resource_description])) - 1)
            ELSE NULL
        END AS [Node ID],[DES].[program_name],[DEST].text,
        [DER].[database_id],[DEQP].[query_plan],[DER].[cpu_time]
    FROM sys.dm_os_waiting_tasks [DOWT]
    INNER JOIN sys.dm_exec_sessions [DES] ON
        [DOWT].[session_id] = [DES].[session_id]
    INNER JOIN sys.dm_exec_requests [DER] ON
        [DES].[session_id] = [DER].[session_id]
    OUTER APPLY sys.dm_exec_sql_text ([DER].[sql_handle]) [DEST]
    OUTER APPLY sys.dm_exec_query_plan ([DER].[plan_handle]) [DEQP]
    WHERE [DES].[is_user_process] = 1
    ORDER BY [DOWT].[session_id], [DOWT].[exec_context_id];


    • Edited by ashwan Wednesday, July 3, 2019 12:13 AM
    Wednesday, July 3, 2019 12:07 AM