Query high-consumption SQL statements

Query high-consumption SQL statements

High IO consumption:

Select top 10
[Average IO] = (total_logical_reads + total_logical_writes)/qs.exe cution_count
, [Total IO] = (total_logical_reads + total_logical_writes)
, [Execution count] = qs.exe cution_count
, [Individual Query] = SUBSTRING (qt. text, qs. statement_start_offset/2,
(Case when qs. statement_end_offset =-1
Then len (CONVERT (NVARCHAR (MAX), qt. text) * 2
ELSE qs. statement_end_offset END-qs. statement_start_offset)/2)
, [Parent Query] = qt. text
, DatabaseName = DB_NAME (qt. dbid)
FROM sys. dm_exec_query_stats qs
Cross apply sys. dm_exec_ SQL _text (qs. SQL _handle) as qt
Order by [Average IO] DESC;

High CPU consumption:

Select top 10
[Average CPU used] = total_worker_time/qs.exe cution_count
, [Total CPU used] = total_worker_time
, [Execution count] = qs.exe cution_count
, [Individual Query] = SUBSTRING (qt. text, qs. statement_start_offset/2,
(Case when qs. statement_end_offset =-1
Then len (CONVERT (NVARCHAR (MAX), qt. text) * 2
ELSE qs. statement_end_offset END-
Qs. statement_start_offset)/2)
, [Parent Query] = qt. text
, DatabaseName = DB_NAME (qt. dbid)
FROM sys. dm_exec_query_stats qs
Cross apply sys. dm_exec_ SQL _text (qs. SQL _handle) as qt
Order by [Average CPU used] DESC;