locked
SQL Job Invocation consuming most CPU time RRS feed

  • Question

  • Hello,

    Not sure if this is the right forum. We are experiencing 100% cpu situations on the principal server in our SQL 2012 mirror configuration in Azure IAAS. The top-sessions report shows that the 'Job invocation engine' and the 'Generic Refresher' are consuming most CPU time.

    All jobs are running fine, the start of the 100% situation does not correspond with the start time of one of the jobs.

    When we perform a failover of a particular database the CPU drops. The top-sessions report does not have the 'Job invocation engine' and the 'Generic Refresher' in the list anymore.

    We are puzzled on why the job invocation engine  can consume so much cpu. Can someone point us in the right direction for troubleshooting?

    Thanks!

    Regards,

    Erik

    Tuesday, March 31, 2015 8:14 AM

Answers

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

    • Marked as answer by Michelle Li Wednesday, April 8, 2015 7:41 AM
    Wednesday, April 1, 2015 8:52 AM

All replies

  • Hello,

    Verify the SPID with

    SP_who2

    or

    select * from sysprocesses where spid>50 orderr by cpu desc

    Once we have SPID use DBCC INPUTBUFFEr()

    this will give what spid actually doing and causing more CPU. Please verify is there any PAGE_IO_LATCHES or CXPACKET or any sort of wait types on the SPIDS.


    Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== http://www.dbainhouse.blogspot.in/

    Wednesday, April 1, 2015 4:46 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

    • Marked as answer by Michelle Li Wednesday, April 8, 2015 7:41 AM
    Wednesday, April 1, 2015 8:52 AM