[MSSQL] 21. 성능 많이 먹는 쿼리 플랜(실행 계획) 확인 쿼리

DB Instance에 성능을 많이 사용하고 있는 쿼리에 대한 실행 계획을 조회하는 쿼리입니다.

SELECT TOP 20 
    db_name(qp.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
	, qp.query_plan
FROM sys.dm_exec_query_stats AS deqs
OUTER APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS qp
ORDER BY deqs.total_worker_time DESC;
GO