병목현상 및 부하 쿼리

SELECT

SUBSTRING(text, qs.statement_start_offset / 2

, (CASE WHEN qs.statement_end_offset = -1 THEN

LEN(CONVERT(NVARCHAR(MAX), text)) * 2

ELSE qs.statement_end_offset

END - qs.statement_start_offset) / 2)

, qs.plan_generation_num as recompiles

, qs.execution_count as execution_count

, qs.total_elapsed_time - qs.total_worker_time as total_wait_time

, qs.total_logical_reads as reads

, qs.total_logical_writes as writes

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

LEFT OUTER JOIN sys.dm_exec_requests r

ON qs.sql_handle = r.sql_handle

ORDER BY 3 DESC