Hello Geeks and welcome to the Day 37 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Since last two blogs I have covered stats collection using sys.dm_exec_procedure_stats and sys.dm_exec_query_stats. Today I will be covering another stats related execution DMV. Sys.dm_exec_trigger_stats is a DMV which shows the stats of trigger execution.

There is a misconception and a standard statement most of the database folks tell. “Triggers are bad.” I do not agree to that unless it is based on some facts. To pull that facts you can use the stats from sys.dm_exec_trigger_stats. Compare the overhead with trigger and other methods. It all boils down to the business use case.

So let us leave the business arguments out of this and see how you can come up with the stats. To use sys.dm_exec_trigger_stats is no different from any other stats DMV. So to make this blog independent I will post the scripts for you.

Create collection table:

Let us create the procedure to capture data from sys.dm_exec_trigger_stats. This is similar to what we did in last two blogs.

Now let us execute a delete on employee table in adventureworks2012 database. It has a trigger which doesn’t allow deletes. So the trigger call should get updated in sys.dm_exec_trigger_stats. Run below commands to collect the stats.

Now check the stats collected form sys.dm_exec_trigger_stats. This query will give you the difference between each run of the collection procedure.

sys.dm_exec_trigger_stats

There is a bug related to sys.dm_exec_trigger_stats which is not fixed until SQL Server 2012. The execution count increases even when the trigger is not run. For various reasons the details are not exposed. Please refer to this connect bug if you want to repro on 2008 R2.

Tomorrow I will be covering one more execution related DMV. Stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook