[MSSQL] 18. 성능 많이 먹는 쿼리 조회

인스턴스의 캐시에 올라와있는 쿼리 중 비용을 많이 소비하고 있는 쿼리를 뽑는 쿼리입니다.

장애 상황에서 제일 문제되는 쿼리를 뽑을 때 사용할 수 있습니다.

SELECT TOP 300 
    db_name(execText.dbid) AS dbName
	,execution_count AS execCnt
	,(total_worker_time / execution_count) / 1000 AS avgCPU
	,(total_elapsed_time / execution_count) / 1000 AS avgDuration
	, total_logical_reads / execution_count AS avgReads
	, max_worker_time AS maxCPU
	, max_elapsed_time AS maxDuration
	, max_logical_reads AS maxReads
	, total_worker_time / 1000 AS totalCPU
	, total_logical_reads as totalReads
	,object_name(execText.objectid, execText.dbid) AS objName
	,'"' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(7600), SUBSTRING(execText.TEXT, deqs.statement_start_offset / 2 + 1, (
							(
								CASE 
									WHEN deqs.statement_end_offset = - 1
										THEN LEN(CONVERT(NVARCHAR(max), execText.TEXT)) * 2
									ELSE deqs.statement_end_offset
									END
								) - deqs.statement_start_offset
							) / 2)), '"', ''''), CHAR(13), ' '), CHAR(10), ' ') + '"' AS queryText
--	, qp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
--OUTER APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS qp
ORDER BY deqs.total_worker_time DESC;
GO