T SQL Script: Azure SQL Find Top IO Consumers (logical and physical reads)
T SQL Script: Azure SQL Find Top IO Consumers (logical and physical reads)
-- Azure SQL Find Top IO Consumers (logical and physical reads)
SELECT TOP 20
qs.total_logical_reads,
qs.total_physical_reads,
qs.execution_count,
qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
qs.total_physical_reads / qs.execution_count AS AvgPhysicalReads,
SUBSTRING(st.text, qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(st.text) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS StatementText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_logical_reads DESC;
SELECT TOP 20
qs.total_logical_reads,
qs.total_physical_reads,
qs.execution_count,
qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
qs.total_physical_reads / qs.execution_count AS AvgPhysicalReads,
SUBSTRING(st.text, qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(st.text) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS StatementText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_logical_reads DESC;