Executes the slowest SQL statement Select (total_elapsed_time/execution_count)/1000 n ' Average time ms ', total_elapsed_time/1000 N ' Total time spent 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 reads ', Total_logical_reads n ' logical reads total number of times ', total_logical_writes n ' logical writes total number of times ', Execution_count n ' executions ', SUBSTRING (St.text, ( QS.STATEMENT_START_OFFSET/2) + 1, (Statement_end_offsetwhen-1 then datalength (st.text) ELSE Qs.statement_end_ Offset End-qs.statement_start_offset)/2) + 1) n ' execute statement ', Creation_time N ' statement compile time ', last_execution_time n ' last execution time ' Fromsys.dm_exec_query_stats as Qs Cross APPLY sys.dm_exec_sql_text (qs.sql_handle) stwheresubstring (St.text, ( QS.STATEMENT_START_OFFSET/2) + 1, (Statement_end_offsetwhen-1 then datalength (st.text) ELSE Qs.statement_end_ Offset End-qs.statement_start_offset)/2) + 1) Not like '? tch% ' ORDER bytotal_elapsed_time/execution_count DESC;
--The last SQL that consumes the most CPU:
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],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 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