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 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;
See Also
Reference
Change History
Updated content |
|---|
Replaced example with updated code. |