View SQL statements that are the most resource-consuming time for SQL Server

-Execution of the slowest SQL statement
SELECT
(total_elapsed_time/execution_count)/1000 n ' Average time ms '
, total_elapsed_time/1000 n ' Total elapsed Time Ms '
, total_worker_time/1000 n ' Total CPU time MS '
, total_physical_reads n ' physical reads total number of times '
, total_logical_reads/ Execution_count n ' per logical read '
, total_logical_reads n ' logical read total number '
, total_logical_writes n ' logical write Total '
, Execution_ Count N ' executions '
, 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) N ' execute statement '
, Creation_time N ' statement compilation time '
, last_execution_time n ' last execution time '
from
Sys.dm_exec_query_stats as Qs Cross APPLY sys.dm _exec_sql_text (qs.sql_handle) St
WHERE
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) not as '? tch% '
ORDER by

Total_elapsed_time/execution_count DESC;

-the last SQL that consumes the most CPUs
SELECT TOP
    total_worker_time/1000 as [total CPU time (ms)],execution_count [run times],
    qs.total_worker_time/qs.execution_count/1000 as [AVG CPU time (ms)],
    Last_execution_ Time as [last execution],max_worker_time/1000 as [Maximum execution Time (ms)],
    SUBSTRING (Qt.text,qs.statement_start_ OFFSET/2+1,&NBSP
        (case qs.statement_end_offset = -1 
      & nbsp Then Datalength (qt.text)  
        ELSE Qs.statement_end_offset End-qs.statement_start_ Offset)/2 + 1)  
    As [syntax using CPU], Qt.text [full syntax],
    qt.dbid, Dbname=db_name (qt.dbid), br>     Qt.objectid,object_name (qt.objectid,qt.dbid) ObjectName
from Sys.dm_exec_query_stats QS with ( NOLOCK)
Cross apply Sys.dm_exec_sql_text (Qs.sql_handle) as QT
WHERE execution_count>1
ORDER by   Total_worker_time DESC

--The last SQL that consumes the most CPU on average
SELECT TOP 20
total_worker_time/1000 as [total CPU time (ms)],execution_count [run times],
qs.total_worker_time/qs.execution_count/1000 as [average CPU time (ms)],
Last_execution_time as [last execution Time],min_worker_time/1000 as [min. Execution time (MS)],
max_worker_time/1000 as [Maximum execution Time (ms)],
SUBSTRING (Qt.text,qs.statement_start_offset/2+1,
(case when qs.statement_end_offset =-1
Then Datalength (qt.text)
ELSE qs.statement_end_offset End-qs.statement_start_offset)/2 + 1)
As [syntax with CPU], Qt.text [full syntax],
Qt.dbid, Dbname=db_name (qt.dbid),
Qt.objectid,object_name (qt.objectid,qt.dbid) ObjectName
From Sys.dm_exec_query_stats Qs with (NOLOCK)
Cross apply Sys.dm_exec_sql_text (Qs.sql_handle) as Qt
WHERE execution_count>1
ORDER by (qs.total_worker_time/qs.execution_count/1000) DESC

View SQL statements that are the most resource-consuming time for SQL Server