Identify CPU time consuming queries in a Database

  1. SELECT Creation_Time,  
  2. Max_Worker_Time,(total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes,   
  3. (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, st.[text]  
  4. --, qp.query_plan   
  5. FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  
  6. ORDER BY Max_Worker_Time desc  
  7. --Order by Creation_Time