sys.dm_exec_trigger_stats – Day 37 – One DMV a Day

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:

CREATE TABLE triggerExecStats_tbl
	(dbId INT
	,objId BIGINT
	,triggerName NVARCHAR(400)
	,Exec_Cnt BIGINT
	,Exec_Time BIGINT
	,Disk_Reads BIGINT
	,Mem_Reads BIGINT
	,Total_Writes BIGINT
	,Total_Time BIGINT
	,SampledOn DATETIME)
GO

CREATE NONCLUSTERED INDEX IX_triggerExecStats_SampledOn ON triggerExecStats_tbl(SampledOn)
GO
CREATE NONCLUSTERED INDEX IX_triggerExecStats_triggerName ON triggerExecStats_tbl(triggerName)
GO

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.

CREATE PROCEDURE usp_collectExecTriggerStats_prc
AS
BEGIN
	INSERT INTO triggerExecStats_tbl 
		(dbId
		,objId
		,Exec_Cnt 
		,Exec_Time 
		,Disk_Reads
		,Mem_Reads 
		,Total_Writes
		,Total_Time
		,SampledOn)
	SELECT database_id,
		object_id,
		execution_count AS Exec_Cnt,
		total_worker_time AS Exec_Time,
		total_physical_reads AS Disk_Reads,
		total_logical_reads AS Mem_Reads,
		total_logical_writes AS Total_Writes,
		total_elapsed_time AS Total_Time,
		GETDATE()
	FROM sys.dm_exec_trigger_stats 
	WHERE database_id NOT IN (1,2,3,4,32767)

	UPDATE triggerExecStats_tbl
		SET objName = OBJECT_NAME(objId, dbid)
	WHERE objName IS NULL
END
GO

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.

EXEC usp_collectExecTriggerStats_prc
GO
DELETE FROM HumanResources.Employee WHERE BusinessEntityID = 1
GO
EXEC usp_collectExecTriggerStats_prc
GO
DELETE FROM HumanResources.Employee WHERE BusinessEntityID = 1
GO
EXEC usp_collectExecTriggerStats_prc
GO

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.

;WITH c AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY sampledOn) As RowNum,
		TriggerName,
		Exec_Cnt,
		Exec_Time,
		Disk_Reads,
		Mem_Reads,
		Total_Writes,
		Total_Time,
		SampledOn
	FROM triggerExecStats_tbl
	WHERE TriggerName = 'dEmployee'
)
SELECT c1.TriggerName, 
	c1.Exec_Cnt - c2.Exec_Cnt AS Execs,
	(c1.Exec_Time - c2.Exec_Time)/(c1.Exec_Cnt - c2.Exec_Cnt) AS Avg_Exec_Time,
	(c1.Total_Time - c2.Total_Time)/(c1.Exec_Cnt - c2.Exec_Cnt) AS Avg_Total_Time,
	c1.Disk_Reads - c2.Disk_Reads AS Physical_Reads,
	c1.Mem_Reads - c2.Mem_Reads AS Logical_Reads,
	c1.Total_Writes - c2.Total_Writes AS Total_Writes,
	c1.SampledOn
FROM c c1
INNER JOIN c c2
ON c1.RowNum = c2.RowNum + 1

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