Find the Most Time Consuming Code in your SQL Server Database

SQL에서 많은 시간을 소비하는 쿼리 찾기

 

--Top 10 codes that takes maximum time
select top 40 source_code,stats.total_elapsed_time/1000000 as seconds,
last_execution_time 
from sys.dm_exec_query_stats as stats cross APPLY
(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle)) AS query_text 
order by total_elapsed_time DESC


--Top 10 codes that takes maximum physical_reads
select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,
last_execution_time from sys.dm_exec_query_stats as stats
cross APPLY
(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
order by total_physical_reads desc