4

How to get the details about the current running queries which are utilizing more CPU.

5
  • Do you mean just currently running queries, or also queries no longer running?
    – T.H.
    Feb 16 '17 at 10:15
  • Can you please give the queries for both separately?? @T.H.
    – SujithTee
    Feb 16 '17 at 10:17
  • 2
    Have a look at sp_blitzcache from the fine folks at Brent Ozar Unlimited. Open source and available on git. This will give you details of what is in the plan cache, what is consuming the most cpu time, query duration, logical reads etc. Feb 16 '17 at 10:22
  • 1
    For what is currently running you can look at sys.dm_exec_requests DMV or have a look at sp_whoisactive from Adam Machanic Feb 16 '17 at 10:24
  • 1
    This should get you started: github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/…
    – T.H.
    Feb 16 '17 at 10:31
6

there is several methods to find the queries that cause high CPU usage, most common waies:

Method 1: use build in SQL server report to find queries with high CPU time. enter image description here

Method 2: use the following queries:

Query 1: get all statement with CPU usage and from high CPU usage to low

SELECT
       -- using statement_start_offset and
       -- statement_end_offset we get the query text
       -- from inside the entire batch
       SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
                           ((CASE qs.statement_end_offset
                                        WHEN -1 THEN DATALENGTH(qt.TEXT)
                                        ELSE qs.statement_end_offset
                           END
                           - qs.statement_start_offset)/2)+1)
                           as [Text],
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
-- converting microseconds to seconds
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
       -- Retrieve the query text
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
       -- Retrieve the query plan
       CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC -- CPU time

Query 2: Top 3 CPU-sapping queries for which plans exist in the cache

SELECT TOP 3
total_worker_time ,
execution_count ,
total_worker_time / execution_count AS [Avg CPU Time] ,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN '-- see objectText column--'
ELSE '-- query --' + CHAR(13) + CHAR(10)
+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,
( ( CASE WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
END ) - deqs.statement_start_offset ) / 2)
END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;

Query 3: get number of core usage as well as CPU time (note this query has a lot of other uses like desk I/O)

SELECT TOP 50
    [Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
    [Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
    [Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
    [Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
    qs.execution_count,
    [Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
    [Total I/O] = total_logical_reads + total_logical_writes,
    Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
        (
            (
                CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(qt.[text])
                    ELSE qs.statement_end_offset
                END - qs.statement_start_offset
            ) / 2
        ) + 1
    ),
    Batch = qt.[text],
    [DB] = DB_NAME(qt.[dbid]),
    qs.last_execution_time,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where qs.execution_count > 5    --more than 5 occurences
ORDER BY [Total MultiCore/CPU time(sec)] DESC

EDIT
the following sorted proc is helpful too

sp_who
sp_who2
sp_who3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.