locked
SQL Server CPU through activity monitor RRS feed

  • Question

  • Hello,

    I have been trying to figure out whether the CPU utilization shown by task manager and SQL Server activity monitor can differ at some point of time.

    The back ground of this question lies in one of the anomalies I found recently.

    SQL activity monitor was showing CPU activity at 100% but when I checked the task manager it was within 70% mark.

    I used ring buffer to get the CPU and it was showing 180%-220% of CPU utilization whereas the server itself was at 70% CPU from the task  manager.

    any information on this would be very helpful.

    -Kanishka

    Friday, June 9, 2017 2:12 PM

Answers

  • Use Performance Monitor to captutre CPU 

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

    Sunday, June 11, 2017 7:43 AM
  • I would not recommend you looking at Activity Monitor

    https://mssqlwiki.com/tag/sql-server-cpu-utilization-high/


    ---- Signal Waits above 10-15% is usually a sign of CPU pressure
    SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
           CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
    FROM sys.dm_os_wait_stats OPTION (RECOMPILE);


    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, June 12, 2017 12:31 PM

All replies

  • Use Performance Monitor to captutre CPU 

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

    Sunday, June 11, 2017 7:43 AM
  • Thank you for checking this, however, perfmon data is at par with what we are seeing in task manager.

    Problem seems with the activity monitor that is showing incorrect data (apparently that what it looks like!)

    -Kanishka 

    Monday, June 12, 2017 12:27 PM
  • I would not recommend you looking at Activity Monitor

    https://mssqlwiki.com/tag/sql-server-cpu-utilization-high/


    ---- Signal Waits above 10-15% is usually a sign of CPU pressure
    SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
           CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
    FROM sys.dm_os_wait_stats OPTION (RECOMPILE);


    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, June 12, 2017 12:31 PM