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. |
The following example returns information about the top 10 CPU-consuming triggers sorted by average elapsed time.
PRINT '--top 10 CPU consuming triggers 'SELECT TOP 10 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_countFROM sys.dm_exec_trigger_stats AS dORDER BY [total_worker_time] DESC;
