SQL Server CPU Intensive Queries

-- CPU Intensive Queries
-- 
-- Lists the queries that have used the most CPU time.
-- 
-- If you are trying to reduce CPU usage by SQL Server queries, concentrate your
-- tuning efforts on the queries listed by this script.
-- 
-- Data is aggregated for multiple runs of each query.
-- 
-- Note that only those queries with a cached query plan are listed. Once a
-- query's plan is removed from the cache, the CPU usage statistics are cleared.
-- The results from this script will probably become more accurate over time, as
-- SQL Server collects more aggregated data.
-- 
-- CPU usage statistics for the entire server (including non-SQL Server tasks)
-- are listed by the Server CPU utilization script, also provided with SQL
-- Scripts Manager.

declare @count int;
set @count = 10;

select top (@count)
    rank() over(order by deqs.total_worker_time desc) as Rank,
    convert(decimal(38, 2), convert(float, total_worker_time) / 1000) as [Total CPU Time (ms)],
    execution_count as [Execution Count],
    convert(decimal(38, 2), (convert(float, total_worker_time) / execution_count) / 1000) as [Average CPU Time (ms)],
    substring(execText.text,
    -- starting value for substring 
    case
      when deqs.statement_start_offset = 0 or deqs.statement_start_offset is null 
        then 1
      else deqs.statement_start_offset / 2 + 1
    end,
    -- ending value for substring
    case
      when deqs.statement_end_offset = 0 or deqs.statement_end_offset = -1 or deqs.statement_end_offset is null 
        then len(execText.text)
      else deqs.statement_end_offset / 2
    end - case
            when deqs.statement_start_offset = 0 or deqs.statement_start_offset is null
                then 1
            else deqs.statement_start_offset / 2
          end + 1) as [Query Text],
    execText.text as [Object Text]
from sys.dm_exec_query_stats as deqs
     cross apply sys.dm_exec_sql_text(deqs.plan_handle) as execText
order by
    deqs.total_worker_time desc;