Queries that cumulatively consume high CPU

 

PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--';

SELECT TOP 10 GETDATE() runtime, *

FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', SUM(logical_reads) 'Total_Request_Logical_Reads', MIN(start_time) 'Earliest_Request_start_Time', COUNT(*) 'Number_Of_Requests', SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS "Statement_Text"

    FROM (SELECT req.*, SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1) AS statement_text

            FROM sys.dm_exec_requests AS req

                CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats

    GROUP BY query_hash) AS t

ORDER BY Total_Request_Cpu_Time_Ms DESC;

PRINT '--top 10 Active CPU Consuming Queries by sessions--';

SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms', OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName', SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1), CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text

FROM sys.dm_exec_requests AS req

    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST

ORDER BY cpu_time DESC;

GO