sys.dm_exec_trigger_stats - Usage & Examples

sys.dm_exec_trigger_stats aggregates runtime statistics from the Query Execution Engine for compiled trigger plans stored in the plan cache. When a trigger executes, the Storage Engine passes control to the Query Processing Engine, which retrieves the cached trigger plan or compiles a new one. The execution statistics accumulate in memory structures managed by the plan cache component.

The DMV returns cumulative statistics since the last service restart or manual statistics reset using DBCC FREEPROCCACHE. Each trigger gets one row per database where it exists, identified by object_id and database_id. Statistics include execution_count, total_elapsed_time, total_worker_time (CPU), total_logical_reads, total_logical_writes, and total_physical_reads.

SQL Server 2016 introduced additional columns for memory grant statistics and parallel execution tracking. SQL Server 2019 added support for batch mode execution statistics, though triggers rarely benefit from batch mode processing. The statistics reset behavior changed in SQL Server 2022 to better handle plan cache pressure scenarios.

Common misconceptions include believing this DMV shows currently executing triggers (it shows historical statistics) and assuming zero values indicate unused triggers (they may indicate plan cache eviction). Trigger statistics can disappear from this view during memory pressure when plans get evicted from cache.

-- Top 10 most executed triggers with execution metrics
SELECT TOP 10
    DB_NAME(database_id) as database_name,
    OBJECT_SCHEMA_NAME(object_id, database_id) as schema_name,
    OBJECT_NAME(object_id, database_id) as trigger_name,
    execution_count,
    total_elapsed_time,
    total_elapsed_time / execution_count as avg_elapsed_time_ms,
    total_worker_time / execution_count as avg_cpu_time_ms,
    total_logical_reads / execution_count as avg_logical_reads
FROM sys.dm_exec_trigger_stats
WHERE execution_count > 0
ORDER BY execution_count DESC;

-- Triggers with highest average duration and resource consumption
SELECT 
    DB_NAME(database_id) as database_name,
    OBJECT_SCHEMA_NAME(object_id, database_id) as schema_name,
    OBJECT_NAME(object_id, database_id) as trigger_name,
    execution_count,
    total_elapsed_time / execution_count as avg_elapsed_time_ms,
    total_worker_time / execution_count as avg_cpu_time_ms,
    total_logical_reads / execution_count as avg_logical_reads,
    total_logical_writes / execution_count as avg_logical_writes,
    last_execution_time
FROM sys.dm_exec_trigger_stats
WHERE execution_count > 10
ORDER BY (total_elapsed_time / execution_count) DESC;

-- Identify triggers causing high I/O pressure
SELECT 
    DB_NAME(database_id) as database_name,
    OBJECT_SCHEMA_NAME(object_id, database_id) as schema_name,
    OBJECT_NAME(object_id, database_id) as trigger_name,
    execution_count,
    total_physical_reads,
    total_logical_reads,
    total_logical_writes,
    (total_physical_reads * 1.0 / NULLIF(total_logical_reads, 0)) * 100 as buffer_cache_hit_ratio,
    last_execution_time
FROM sys.dm_exec_trigger_stats
WHERE total_physical_reads > 1000
ORDER BY total_physical_reads DESC;

-- Get trigger execution plans and text for performance analysis
SELECT 
    ts.database_id,
    DB_NAME(ts.database_id) as database_name,
    OBJECT_NAME(ts.object_id, ts.database_id) as trigger_name,
    ts.execution_count,
    ts.total_elapsed_time / ts.execution_count as avg_elapsed_time_ms,
    p.query_plan,
    m.definition as trigger_definition
FROM sys.dm_exec_trigger_stats ts
CROSS APPLY sys.dm_exec_query_plan(ts.plan_handle) p
INNER JOIN sys.sql_modules m ON ts.object_id = m.object_id
WHERE ts.execution_count > 0
ORDER BY (ts.total_elapsed_time / ts.execution_count) DESC;

-- Compare trigger performance across databases
SELECT 
    database_id,
    DB_NAME(database_id) as database_name,
    COUNT(*) as trigger_count,
    SUM(execution_count) as total_executions,
    AVG(total_elapsed_time / NULLIF(execution_count, 0)) as avg_duration_ms,
    SUM(total_logical_reads) as total_logical_reads,
    MAX(last_execution_time) as last_execution_time
FROM sys.dm_exec_trigger_stats
GROUP BY database_id
ORDER BY SUM(total_elapsed_time) DESC;

Remove unused or problematic triggers This script identifies triggers that haven't executed recently and may be candidates for removal.

-- WARNING: Test thoroughly before dropping triggers in production
-- Identify potentially unused triggers (no executions in recent cache period)
SELECT 
    'USE [' + DB_NAME(database_id) + ']; DROP TRIGGER [' + 
    OBJECT_SCHEMA_NAME(object_id, database_id) + '].[' + 
    OBJECT_NAME(object_id, database_id) + '];' as drop_statement,
    DB_NAME(database_id) as database_name,
    OBJECT_NAME(object_id, database_id) as trigger_name,
    execution_count,
    last_execution_time
FROM sys.dm_exec_trigger_stats
WHERE execution_count = 0 OR last_execution_time < DATEADD(day, -30, GETDATE())
ORDER BY DB_NAME(database_id), OBJECT_NAME(object_id, database_id);
-- Review results carefully and verify triggers are truly unused before executing

Clear trigger statistics for baseline measurement Resets execution statistics to establish new performance baseline.

-- Clear all procedure cache and trigger statistics
-- WARNING: This will recompile all cached plans on next execution
DBCC FREEPROCCACHE;

-- Alternative: Clear statistics for specific database
-- DBCC FREEPROCCACHE('database_name');

-- Verify statistics cleared
SELECT COUNT(*) as remaining_trigger_stats
FROM sys.dm_exec_trigger_stats;

Create monitoring for trigger performance regression Establishes ongoing monitoring for trigger performance issues.

-- Create alert for triggers exceeding performance thresholds
-- Store baseline metrics first, then compare against thresholds
IF OBJECT_ID('tempdb..#trigger_baseline') IS NOT NULL DROP TABLE #trigger_baseline;

SELECT 
    database_id,
    object_id,
    execution_count,
    total_elapsed_time,
    total_worker_time,
    total_logical_reads,
    GETDATE() as baseline_date
INTO #trigger_baseline
FROM sys.dm_exec_trigger_stats;

-- Query to identify performance regressions (run periodically)
SELECT 
    'ALERT: Trigger performance regression detected' as alert_message,
    DB_NAME(ts.database_id) as database_name,
    OBJECT_NAME(ts.object_id, ts.database_id) as trigger_name,
    ts.total_elapsed_time / ts.execution_count as current_avg_duration,
    bl.total_elapsed_time / bl.execution_count as baseline_avg_duration
FROM sys.dm_exec_trigger_stats ts
INNER JOIN #trigger_baseline bl ON ts.object_id = bl.object_id AND ts.database_id = bl.database_id
WHERE ts.execution_count > bl.execution_count
AND (ts.total_elapsed_time / ts.execution_count) > (bl.total_elapsed_time / bl.execution_count) * 1.5;

Optimize trigger plan cache usage Forces recompilation of poorly performing trigger plans.

-- Recompile specific trigger plans showing performance issues
-- Replace with actual database and object IDs from diagnostic queries
DECLARE @database_id int = 5; -- Replace with target database_id
DECLARE @object_id int = 123456; -- Replace with target object_id

-- Get plan handle for specific trigger
DECLARE @plan_handle varbinary(64);
SELECT @plan_handle = plan_handle
FROM sys.dm_exec_trigger_stats
WHERE database_id = @database_id AND object_id = @object_id;

-- Remove specific plan from cache
IF @plan_handle IS NOT NULL
    DBCC FREEPROCCACHE(@plan_handle);

-- Verify plan removed
SELECT COUNT(*) as plans_remaining
FROM sys.dm_exec_trigger_stats
WHERE database_id = @database_id AND object_id = @object_id;

Implement trigger performance monitoring using SQL Server Agent jobs that query sys.dm_exec_trigger_stats hourly and alert when average execution time exceeds baseline thresholds by more than 50%. Store historical statistics in a dedicated monitoring database to track trends over time.

Configure Query Store in all user databases to capture trigger execution patterns and plan choice regressions. Enable automatic plan correction for SQL Server 2017+ to automatically revert to better-performing trigger plans.

Establish trigger coding standards that require SET NOCOUNT ON, avoid cursors and scalar functions, and minimize transaction scope. Review trigger definitions quarterly using sys.sql_modules joined with performance statistics to identify optimization opportunities.

Set up plan cache sizing appropriately using max server memory configuration to prevent excessive plan eviction. Monitor plan cache hit ratios and memory grants to ensure trigger plans remain cached during normal operations.

Create database maintenance jobs that capture trigger statistics snapshots before and after maintenance windows to identify performance impacts from statistics updates or index maintenance.