Trigger Overhead: Measuring the Hidden Cost of DML Triggers

An insert can spend more time auditing than storing its main row. Measuring trigger overhead exposes that additional work inside the caller's transaction.

Follow the Entire Insert

A DML trigger runs as part of the statement that fires it. Its reads, writes, locks, and failures affect the caller. A short application command therefore does not guarantee a short database operation.

I inspect triggers when an apparently small insert does unexpected work. I also read their definitions before comparing execution times. Measuring an insert without understanding its attached logic leaves part of the operation unexplained.

The important question is what the trigger must accomplish synchronously. An audit record tied to the transaction has a clear reason to stay. A slow external notification has a different reliability and latency requirement.

Triggers do not receive a separate appointment on the application's calendar. They run while the caller waits. That makes a hidden dependency visible to users even when the application never names it.

Use a disposable user database for the following objects. The enabled and disabled comparisons intentionally change auditing behavior. They belong in a controlled experiment rather than a production troubleshooting shortcut.

Build a Set-Based Audit Trigger

Create a source table and an audit table with separate keys. The trigger copies all inserted rows through one INSERT SELECT. This is necessary because a single statement can insert several rows.

CREATE TABLE dbo.TriggerSourceDemo
(
    ItemID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_TriggerSourceDemo PRIMARY KEY,
    Amount decimal(12,2) NOT NULL
);
CREATE TABLE dbo.TriggerAuditDemo
(
    AuditID bigint IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_TriggerAuditDemo PRIMARY KEY,
    ItemID int NOT NULL,
    Amount decimal(12,2) NOT NULL,
    CapturedAt datetime2(3) NOT NULL
);
GO
CREATE TRIGGER dbo.TriggerSourceDemo_Audit
ON dbo.TriggerSourceDemo
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM inserted) RETURN;
    INSERT dbo.TriggerAuditDemo(ItemID, Amount, CapturedAt)
    SELECT ItemID, Amount, SYSUTCDATETIME()
    FROM inserted;
END;
GO

The fixture records the inserted amount and a UTC capture time. It does not identify the application user or reconstruct every later change. Define those audit requirements before treating this small example as a complete audit design.

The audit table's indexes also need maintenance during every trigger execution. More audit indexes improve some reads while increasing the write work. Inspect both the source and audit structures when reviewing the cost.

An AFTER trigger does not run once for each inserted row. It runs for the statement and receives a set through inserted. Avoid scalar variables that silently reduce that set to one arbitrary row.

Compare Trigger Overhead With Equivalent Inserts

Enable the actual execution plan in SSMS before running this block. STATISTICS IO reports logical reads, while STATISTICS TIME reports CPU and elapsed time. Read the messages for both the source operation and trigger work.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
INSERT dbo.TriggerSourceDemo(Amount)
SELECT CONVERT(decimal(12,2), 10 + a.n)
FROM (VALUES(1),(2),(3),(4),(5)) AS a(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5)) AS b(n);
DISABLE TRIGGER dbo.TriggerSourceDemo_Audit ON dbo.TriggerSourceDemo;
BEGIN TRY
    INSERT dbo.TriggerSourceDemo(Amount)
    SELECT CONVERT(decimal(12,2), 10 + a.n)
    FROM (VALUES(1),(2),(3),(4),(5)) AS a(n)
    CROSS JOIN (VALUES(1),(2),(3),(4),(5)) AS b(n);
    ENABLE TRIGGER dbo.TriggerSourceDemo_Audit ON dbo.TriggerSourceDemo;
END TRY
BEGIN CATCH
    ENABLE TRIGGER dbo.TriggerSourceDemo_Audit ON dbo.TriggerSourceDemo;
    THROW;
END CATCH;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

The second insert skips audit writes by design. Compare the messages rather than expecting a particular timing difference. Cache warmth, compilation, logging, and background activity influence the observed measurements.

The source table grows between these two statements. Repeat a balanced experiment from an equivalent fixture when that difference affects the workload. Use the same input shape and the same plan collection setting for both paths.

Confirm the trigger is enabled after the experiment. The CATCH restores it for catchable failures, but a canceled connection requires a manual check. An interrupted test must not leave its temporary behavior unexplained.

Inspect sys.triggers for is_disabled and verify the expected audit rows with your own queries. The disabled insert should have no corresponding audit entries. That difference confirms the comparison changed the intended behavior.

Run the verification after restoring the trigger. The source count includes both fixture inserts, while the audit count includes only the enabled path. Compare the identifiers if other writes occurred during the experiment.

SELECT name, is_disabled
FROM sys.triggers
WHERE object_id = OBJECT_ID(N'dbo.TriggerSourceDemo_Audit');
SELECT COUNT_BIG(*) AS SourceRows FROM dbo.TriggerSourceDemo;
SELECT COUNT_BIG(*) AS AuditRows FROM dbo.TriggerAuditDemo;

Account for rollback when evaluating audit completeness. Because the trigger shares the caller's transaction, rollback removes its audit rows as well. Persisting failed attempts outside that transaction requires a separate design.

Read the Trigger Plan

SSMS displays plans for statements executed inside the trigger as well as the outer insert. Locate the INSERT into the audit table and its input. Check whether it scans additional tables or performs unexpected lookups.

A slow plan can come from more than the audit insert itself. Joins, scalar functions, and missing indexes add work inside the trigger. Locks on an audit dependency also extend the caller's wait.

The trigger overhead includes work beyond a single operator's estimated percentage. Read actual row flow and statement measurements together. An estimated cost percentage is not a measured share of elapsed time.

Inspect Cached Trigger Statistics

The trigger statistics view reports aggregate counters for cached triggers. Join its database and object identity carefully so the names belong to the current database. Total worker time uses microseconds, not milliseconds.

Disabling and enabling the trigger removed its row from this view in my test. The block below runs one more enabled insert first, so the view has a fresh row to show.

INSERT dbo.TriggerSourceDemo(Amount)
SELECT CONVERT(decimal(12,2), 10 + a.n)
FROM (VALUES(1),(2),(3),(4),(5)) AS a(n)
CROSS JOIN (VALUES(1),(2),(3),(4),(5)) AS b(n);

SELECT t.name AS TriggerName, ts.cached_time, ts.last_execution_time,
       ts.execution_count, ts.total_worker_time,
       ts.total_logical_reads,
       ts.total_worker_time / NULLIF(ts.execution_count, 0) AS AverageWorkerMicroseconds
FROM sys.dm_exec_trigger_stats AS ts
JOIN sys.triggers AS t ON t.object_id = ts.object_id
WHERE ts.database_id = DB_ID()
ORDER BY ts.total_worker_time DESC;

Use VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. Older versions require VIEW SERVER STATE for this view. Metadata visibility also affects the trigger names available to the inspecting account.

These counters describe the cached plan's lifetime, not an arbitrary reporting window. Restart, eviction, or recompilation can remove or restart that evidence. Save two compatible snapshots if you need interval differences.

An empty result does not prove the database has no triggers. List sys.triggers separately and inspect definitions. A trigger without a currently cached plan has no row in this statistics view.

Cut Trigger Overhead by Moving Slow Work

What must succeed before the original insert is allowed to commit? Keep that requirement explicit when evaluating a queue. A transactional queue row can preserve the obligation while a later worker performs slow delivery.

The worker then needs retry rules, duplicate protection, and a record of delivery failures. Moving work outside the trigger changes when errors reach the application. It does not eliminate the work or its reliability requirements.

Measure trigger overhead again after the proposed change using equivalent input. Verify the business and audit results alongside the messages and plans. A faster insert that loses required audit data is an incomplete change.

Finish by documenting synchronous responsibilities and the monitoring needed for deferred work. Keep the trigger small, set-based, and transactional where correctness requires it. Its hidden work should remain visible in the investigation.

Related reading on this blog: UPDATE() in a Trigger Is True Even When Nothing Changed and How to Avoid Triggers for Multiple Row Operations in a Table.

A trigger is not invisible bookkeeping, it is work inside every affected transaction.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.