sys.dm_exec_trigger_stats (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 nameData typeDescription
database_idintDatabase ID in which the trigger resides.
object_idintObject identification number of the trigger.
typechar(2)Type of the object:

TA = Assembly (CLR) trigger

TR = SQL trigger
Type_descnvarchar(60)Description of the object type:

CLR_TRIGGER

SQL_TRIGGER
sql_handlevarbinary(64)This can be used to correlate with queries in sys.dm_exec_query_stats that were executed from within this trigger.
plan_handlevarbinary(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_timedatetimeTime at which the trigger was added to the cache.
last_execution_timedatetimeLast time at which the trigger was executed.
execution_countbigintNumber of times that the trigger has been executed since it was last compiled.
total_worker_timebigintTotal amount of CPU time, in microseconds, that was consumed by executions of this trigger since it was compiled.
last_worker_timebigintCPU time, in microseconds, that was consumed the last time the trigger was executed.
min_worker_timebigintMaximum CPU time, in microseconds, that this trigger has ever consumed during a single execution.
max_worker_timebigintMaximum CPU time, in microseconds, that this trigger has ever consumed during a single execution.
total_physical_readsbigintTotal number of physical reads performed by executions of this trigger since it was compiled.
last_physical_readsbigintNumber of physical reads performed the last time the trigger was executed.
min_physical_readsbigintMinimum number of physical reads that this trigger has ever performed during a single execution.
max_physical_readsbigintMaximum number of physical reads that this trigger has ever performed during a single execution.
total_logical_writesbigintTotal number of logical writes performed by executions of this trigger since it was compiled.
last_logical_writesbiginttotal_physical_readsNumber of logical writes performed the last time the trigger was executed.
min_logical_writesbigintMinimum number of logical writes that this trigger has ever performed during a single execution.
max_logical_writesbigintMaximum number of logical writes that this trigger has ever performed during a single execution.
total_logical_readsbigintTotal number of logical reads performed by executions of this trigger since it was compiled.
last_logical_readsbigintNumber of logical reads performed the last time the trigger was executed.
min_logical_readsbigintMinimum number of logical reads that this trigger has ever performed during a single execution.
max_logical_readsbigintMaximum number of logical reads that this trigger has ever performed during a single execution.
total_elapsed_timebigintTotal elapsed time, in microseconds, for completed executions of this trigger.
last_elapsed_timebigintElapsed time, in microseconds, for the most recently completed execution of this trigger.
min_elapsed_timebigintMinimum elapsed time, in microseconds, for any completed execution of this trigger.
max_elapsed_timebigintMaximum elapsed time, in microseconds, for any completed execution of this trigger.

In Windows Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn’t belong to the connected tenant is filtered out.

Requires VIEW SERVER STATE permission on server.

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

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;  

Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_procedure_stats (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)

Community Additions

ADD
Show: