• Article

sys.dm_exec_trigger_stats (Transact-SQL)

Returns aggregate performance statistics for cached triggers. The view contains one row per trigger, and the lifetime of the row is as long as the trigger remains cached. When a trigger is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

Column name

Data type

Description

database_id

int

Database ID in which the trigger resides.

object_id

int

Object identification number of the trigger.

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

Time at which the trigger was added to the cache.

last_execution_time

datetime

Last time at which the trigger was executed.

execution_count

bigint

Number of times that the trigger has been executed since it was last compiled.

total_worker_time

bigint

Total amount of CPU time, in microseconds, that was consumed by executions of this trigger since it was compiled.

last_worker_time

bigint

CPU time, in microseconds, that was consumed the last time the trigger was executed.

min_worker_time

bigint

Maximum CPU time, in microseconds, that this trigger has ever consumed during a single execution.

max_worker_time

bigint

Maximum CPU time, in microseconds, that this trigger has ever consumed during a single execution.

total_physical_reads

bigint

Total number of physical reads performed by executions of this trigger since it was compiled.

last_physical_reads

bigint

Number of physical reads performed the last time the trigger was executed.

min_physical_reads

bigint

Minimum number of physical reads that this trigger has ever performed during a single execution.

max_physical_reads

bigint

Maximum number of physical reads that this trigger has ever performed during a single execution.

total_logical_writes

bigint

Total number of logical writes performed by executions of this trigger since it was compiled.

last_logical_writes

bigint

Number of logical writes performed the last time the trigger was executed.

min_logical_writes

bigint

Minimum number of logical writes that this trigger has ever performed during a single execution.

max_logical_writes

bigint

Maximum number of logical writes that this trigger has ever performed during a single execution.

total_logical_reads

bigint

Total number of logical reads performed by executions of this trigger since it was compiled.

last_logical_reads

bigint

Number of logical reads performed the last time the trigger was executed.

min_logical_reads

bigint

Minimum number of logical reads that this trigger has ever performed during a single execution.

max_logical_reads

bigint

Maximum number of logical reads that this trigger has ever performed during a single execution.

total_elapsed_time

bigint

Total elapsed time, in microseconds, for completed executions of this trigger.

last_elapsed_time

bigint

Elapsed time, in microseconds, for the most recently completed execution of this trigger.

min_elapsed_time

bigint

Minimum elapsed time, in microseconds, for any completed execution of this trigger.

max_elapsed_time

bigint

Maximum elapsed time, in microseconds, for any completed execution of this trigger.

Permissions

Requires VIEW SERVER STATE permission on server.

Remarks

Statistics in the view are updated when a query is completed.

Examples

The following example returns information about the top five triggers identified by average elapsed time.

PRINT '--top 5 CPU consuming 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;