SQL Server, MS SQL - 트리거 성능 통계 보기 , Aggregate performance statistics for cached triggers.

--이거 그대로 붙여 넣으면 됨
SELECT TOP 5 d.object_id, d.database_id, DB_NAME(database_id) AS 'database_name',   
    OBJECT_NAME(object_id, database_id) AS 'trigger_name', d.cached_time,  
    d.last_execution_time, d.total_elapsed_time,   
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],   
    d.last_elapsed_time, d.execution_count  
FROM sys.dm_exec_trigger_stats AS d  
ORDER BY [total_worker_time] DESC;

Column nameData typeDescription

Column Name	Data Type	Description
database_id	int	데이터베이스 ID
object_id	int	트리거 고유 아이디
type	char(2)	Type of the object: TA = Assembly (CLR) trigger TR = SQL trigger
Type_desc	nvarchar(60)	Description of the object type: CLR_TRIGGER SQL_TRIGGER
sql_handle	varbinary(64)	This can be used to correlate with queries in sys.dm_exec_query_stats that were executed from within this trigger.
plan_handle	varbinary(64)	Identifier for the in-memory plan. This identifier is transient and remains constant only while the plan remains in the cache. This value may be used with the sys.dm_exec_cached_plans dynamic management view.
cached_time	datetime	캐시에 에드된 시간
last_execution_time	datetime	가장 최근에 트리거 된 시간
execution_count	bigint	마지막 컴파일 이후로 실행된 횟수
total_worker_time	bigint	이 트리거의 실행에 의해 소비된 총 CPU 시간(마이크로 초)
last_worker_time	bigint	가장 마지막에 발동된 트리거에 소비된 CPU 시간(마이크로초)
min_worker_time	bigint	가장 적게 트리거에 소비된 CPU 시간(마이크로초)
max_worker_time	bigint	가장 많이 트리거에 소비된 CPU 시간(마이크로초)
total_physical_reads	bigint	The total number of physical reads performed by executions of this trigger since it was compiled.
last_physical_reads	bigint	The number of physical reads performed the last time the trigger was executed.
min_physical_reads	bigint	The minimum number of physical reads that this trigger has ever performed during a single execution.
max_physical_reads	bigint	The maximum number of physical reads that this trigger has ever performed during a single execution.
total_logical_writes	bigint	The total number of logical writes performed by executions of this trigger since it was compiled.
last_logical_writes	bigint	The number of logical writes performed the last time the trigger was executed.
min_logical_writes	bigint	The minimum number of logical writes that this trigger has ever performed during a single execution.
max_logical_writes	bigint	The maximum number of logical writes that this trigger has ever performed during a single execution.
total_logical_reads	bigint	The total number of logical reads performed by executions of this trigger since it was compiled.
last_logical_reads	bigint	The number of logical reads performed the last time the trigger was executed.
min_logical_reads	bigint	The minimum number of logical reads that this trigger has ever performed during a single execution.
max_logical_reads	bigint	The maximum number of logical reads that this trigger has ever performed during a single execution.
total_elapsed_time	bigint	이 트리거가 완료될 때까지 걸렸던 시간 총합
last_elapsed_time	bigint	가장 마지막 이 트리거가 완료될 때까지 걸렸던 시간
min_elapsed_time	bigint	가장 적게 이 트리거가 완료될 때까지 걸렸던 시간
max_elapsed_time	bigint	가장 많이이 트리거가 완료될 때까지 걸렸던 시간
total_spills	bigint	The total number of pages spilled by execution of this trigger since it was compiled. Applies to: Starting with SQL Server 2017 (14.x) CU3
last_spills	bigint	The number of pages spilled the last time the trigger was executed. Applies to: Starting with SQL Server 2017 (14.x) CU3
min_spills	bigint	The minimum number of pages that this trigger has ever spilled during a single execution. Applies to: Starting with SQL Server 2017 (14.x) CU3
max_spills	bigint	The maximum number of pages that this trigger has ever spilled during a single execution. Applies to: Starting with SQL Server 2017 (14.x) CU3
total_page_server_reads	bigint	The total number of page server reads performed by executions of this trigger since it was compiled. Applies to: Azure SQL Database Hyperscale
last_page_server_reads	bigint	The number of page server reads performed the last time the trigger was executed. Applies to: Azure SQL Database Hyperscale
min_page_server_reads	bigint	The minimum number of page server reads that this trigger has ever performed during a single execution. Applies to: Azure SQL Database Hyperscale
max_page_server_reads	bigint	The maximum number of page server reads that this trigger has ever performed during a single execution. Applies to: Azure SQL Database Hyperscale

 

출처 : https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-trigger-stats-transact-sql?view=sql-server-ver15