SQLServer -: Find Top CPU Intensive Queries

Queries that are currently in cache that are consuming more CPU

;WITH eqs

AS (

    SELECT 

         [execution_count]

        ,[total_worker_time]/1000  AS [TotalCPUTime_ms]

        ,[total_elapsed_time]/1000  AS [TotalDuration_ms]

        ,query_hash

        ,plan_handle

        ,[sql_handle]

    FROM sys.dm_exec_query_stats

    )

SELECT TOP 10 est.[text], eqp.query_plan AS SQLStatement

    ,eqs.*

FROM eqs

OUTER APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp

OUTER APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS est

ORDER BY [TotalCPUTime_ms] DESC

Queries with average CPU usage 

select query_stats.query_hash,

SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) as avgCPU_USAGE,

min(query_stats.statement_text) as QUERY

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;