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.

-- 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;