[MSSQL] 19. 성능 많이 먹는 프로시저 조회

인스턴스에 캐시된 프로시저 중에 성능을 많이 사용하고 있는 프로시저를 조회하는 쿼리입니다.

장애 상황 시 이슈 프로시저를 잡아내는데 사용할 수 있습니다.

DECLARE @productversion VARCHAR(128)
DECLARE @Majorversion INT

SET @productversion = convert(VARCHAR(128), SERVERPROPERTY('ProductVersion'))
SET @Majorversion = convert(INT, left(@productversion, charindex('.', @productversion) - 1))

IF @Majorversion > 9
BEGIN
	SELECT TOP 300 DB_NAME(d.database_id) AS dbName
		,OBJECT_NAME(object_id, database_id) AS objName
		,d.execution_count AS execCNT
		,(d.total_worker_time / d.execution_count) / 1000 AS avgCPU
		,(d.total_elapsed_time / d.execution_count) / 1000 AS avgDuration
		,(d.total_logical_reads / d.execution_count) AS avgReads
		,d.max_worker_time / 1000 AS maxCPU
		,d.max_elapsed_time / 1000 AS maxDuration
		,d.max_logical_reads AS maxReads
		,d.total_worker_time / 1000 AS totalCPU
		,d.total_elapsed_time / 1000 AS totalDuration
		,d.total_logical_reads AS totalReads
		,d.last_execution_time AS last_runtime
		,d.cached_time AS cacheTime
	FROM sys.dm_exec_procedure_stats AS d
	WHERE d.database_id <> 32767
	ORDER BY d.total_worker_time DESC;
END
GO