The sys.dm_exec_trigger_stats Dynamic Management View is very similar to the sys.dm_exec_procedure stats Dynamic Management View that we viewed on yesterday (Day 7). This DMV is used to return performance statistics about triggers that have been cached on your SQL Server Instance. To illustrate the usage of this DMV, we will run a query against our AdventureWorks2012 database and view the result set.
SELECT * FROM sys.dm_exec_trigger_stats
WHERE database_id = db_id('AdventureWorks2012')
By looking at the results of this DMV, the first thing we notice, if you have been keeping up with this DMV series, is that it returns the exam same columns as the sys.dm_exec_procedure_stats DMV, except for triggers. As a result the same useful information can be gathered from this DMV about your trigger performance.
From a performance perspective, we are able to view statistics about the min, max, last, and total worker time a trigger used, which represents the amount of CPU time in microseconds that this trigger consumed.
We are able to see min, max, last, and total statistics for logical reads/writes and physical reads/writes. The duration that the trigger took to execute is also presented in the result set of this DMV represented as the min, max, last, and total elapsed times.
When you need to analyze the performance of a trigger on your system, this is a good place to begin your analysis.
For more information abut this Dynamic Management View, please see the below Books Online link:
Follow Me on Twitter @PrimeTimeDBA