TOP CPU Consuming Queries (Average CPU Consumption along with Query Hash)

Below query helps to pull the TOP CPU Consuming Queries with Query Hash – Statement Text columns shows you the list of queries that are consuming max CPU on an average that needs to be tweaked or worked on for improving the performance.

This Query helps to get the TOP CPU Consuming queries without any time constraints.

SELECT TOP 10 QUERY_STATS.QUERY_HASH AS "QUERY HASH",

    SUM(QUERY_STATS.TOTAL_WORKER_TIME) / SUM(QUERY_STATS.EXECUTION_COUNT) AS "AVG CPU TIME",
    MIN(QUERY_STATS.STATEMENT_TEXT) AS "STATEMENT TEXT"
FROM
    (SELECT QS.*,
    SUBSTRING(ST.TEXT, (QS.STATEMENT_START_OFFSET/2) + 1,
    ((CASE STATEMENT_END_OFFSET
        WHEN -1 THEN DATALENGTH(ST.TEXT)
        ELSE QS.STATEMENT_END_OFFSET END
        - QS.STATEMENT_START_OFFSET)/2) + 1) AS STATEMENT_TEXT
     FROM SYS.DM_EXEC_QUERY_STATS AS QS
     CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS ST) AS QUERY_STATS
GROUP BY QUERY_STATS.QUERY_HASH
ORDER BY 2 DESC;

GO

This Query helps to get the TOP CPU Consuming queries in any given window provided the query is created between the time window.

SELECT TOP 10 QUERY_STATS.QUERY_HASH AS "QUERY HASH",

SUM(QUERY_STATS.TOTAL_WORKER_TIME) / SUM(QUERY_STATS.EXECUTION_COUNT) AS "AVG CPU TIME",
MIN(QUERY_STATS.STATEMENT_TEXT) AS "STATEMENT TEXT"
FROM
    (SELECT QS.*,
    SUBSTRING(ST.TEXT, (QS.STATEMENT_START_OFFSET/2) + 1,
    ((CASE STATEMENT_END_OFFSET
        WHEN -1 THEN DATALENGTH(ST.TEXT)
        ELSE QS.STATEMENT_END_OFFSET END
            - QS.STATEMENT_START_OFFSET)/2) + 1) AS STATEMENT_TEXT
      FROM SYS.DM_EXEC_QUERY_STATS AS QS
     CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS ST) AS QUERY_STATS
WHERE QUERY_STATS.CREATION_TIME BETWEEN ('2016-07-19 11:00:00') AND ('2016-07-20 15:30:00')
GROUP BY QUERY_STATS.QUERY_HASH
ORDER BY 2 DESC;
GO


Hope it helps !!! :)