none
find Currently session that is costing high cpu

    Question

  • when sql server is 100% sqlserv service, what is the easy way to find what session/query is contributing the high cpu usage. There are a few queries on msdn for top cpu usage, but it is for last a few time period, not currently running session causing the high cpu.

    I used Activity monitor/SQL profiler/Perfmon, but didn't get easy way, not sure what is the proper way and what selection/option to check if using these tools.
    Friday, July 26, 2013 3:29 PM

Answers

  • You can try something like:

    select
      r.*,
      SUBSTRING(t.text, r.statement_start_offset / 2, 
        (CASE WHEN r.statement_end_offset = -1 
            THEN DATALENGTH(t.text)
            ELSE r.statement_end_offset END  - r.statement_start_offset) / 2)
    	as sqlcmd
    from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(r.sql_handle) as t
    where r.session_id > 50
      and r.status = 'running'
    order by cpu_time desc;

    Josh

    Friday, July 26, 2013 3:53 PM
  • generally when High CPU is there on your system we look for Parallel queries.

    First use this query to find out CU consuming queries.

    select top 50 
        sum(qs.total_worker_time) as total_cpu_time, 
        sum(qs.execution_count) as total_execution_count,
        count(*) as  number_of_statements, 
        qs.plan_handle 
    from 
        sys.dm_exec_query_stats qs
    group by qs.plan_handle
    order by sum(qs.total_worker_time) desc
    

    Look for queries which are using parallelism

    Look for excessive compilation and recomplilation

    Look for adhoc queries

    this query will give u queries causing lots of recompilation

    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
    

    Plan generation no >1 means query is recompiling


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, July 26, 2013 4:24 PM

All replies

  • You can try something like:

    select
      r.*,
      SUBSTRING(t.text, r.statement_start_offset / 2, 
        (CASE WHEN r.statement_end_offset = -1 
            THEN DATALENGTH(t.text)
            ELSE r.statement_end_offset END  - r.statement_start_offset) / 2)
    	as sqlcmd
    from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(r.sql_handle) as t
    where r.session_id > 50
      and r.status = 'running'
    order by cpu_time desc;

    Josh

    Friday, July 26, 2013 3:53 PM
  • Thanks, I tried that, also in Activity Monitor there is option to filter running sessions as well, however, when I did that, I only see my own session running. but when select runnable, I see two SSRS session, from experience, I knew it was the cause, then killed them, and the CPU dropped. but for other servers, I would like to know what is the easy way since runnable doesn't mean it used high cpu.
    Friday, July 26, 2013 3:59 PM
  • generally when High CPU is there on your system we look for Parallel queries.

    First use this query to find out CU consuming queries.

    select top 50 
        sum(qs.total_worker_time) as total_cpu_time, 
        sum(qs.execution_count) as total_execution_count,
        count(*) as  number_of_statements, 
        qs.plan_handle 
    from 
        sys.dm_exec_query_stats qs
    group by qs.plan_handle
    order by sum(qs.total_worker_time) desc
    

    Look for queries which are using parallelism

    Look for excessive compilation and recomplilation

    Look for adhoc queries

    this query will give u queries causing lots of recompilation

    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
    

    Plan generation no >1 means query is recompiling


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, July 26, 2013 4:24 PM
  • You are correct in syaing when a session status is runnable it doesn't mean high CPU. But you will have to find out which requests are causing CPU fluctuations and then why is the request causing it. I recommend that you read SQL 2005 waits and queues to get a good understanding of SQL servers execution model then you will understand the different statutes of executing sessions and wait types. Which SQL Server version are you using ?
    Saturday, July 27, 2013 8:05 AM
  • it can helps you-

    Troubleshooting Performance Problems in SQL Server 2008
    http://sqlcat.com/sqlcat/b/whitepapers/archive/2009/04/14/troubleshooting-performance-problems-in-sql-server-2008.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Monday, July 29, 2013 5:50 AM