THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tamarick Hill

July, the 31 Days of SQL Server DMO’s – Day 8 (sys.dm_exec_trigger_stats)

 

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')

image

image

image

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:

http://msdn.microsoft.com/en-us/library/cc280646.aspx

Follow Me on Twitter @PrimeTimeDBA

Published Monday, July 08, 2013 10:01 AM by Tamarick Hill

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement