Top 10 CPU-Consuming Queries in SQL Server

Sometimes you just want to know which queries are running and how long they are taking to execute. This query will tell you which queries are the top 10 queries that are using the most CPU time on your instance of SQL Server.

SELECT TOP 10
    qs.total_worker_time/(qs.execution_count*60000000) as [Minutes Avg CPU Time],    
    qs.execution_count as [Times Run],
    qs.min_worker_time/60000000 as [CPU Time in Mins],
    SUBSTRING(qt.text,qs.statement_start_offset/2,
    (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
     else qs.statement_end_offset end -qs.statement_start_offset)/2) as [Query Text],
    db_name(qt.dbid) as [Database],
    object_name(qt.objectid) as [Object Name]
FROM sys.dm_exec_query_stats qs cross apply
     sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Minutes Avg CPU Time] DESC