All 7 Triggers questions
More in Triggers
What is the difference between an AFTER trigger and an INSTEAD OF trigger? How do you access the old and new values of a row that was just updated from inside a trigger? Why should triggers generally be designed to handle multiple affected rows at once rather than assuming only a single row was changed? What are some alternatives to using a trigger for tasks like auditing or enforcing business rules, and when might those alternatives be preferable? How do nested and recursive triggers work in SQL Server, and what precautions should be taken to avoid unexpected behavior or infinite loops?
Advertisement
Back to results for “Performance”
SQL Server › Triggers

What are the performance implications of using triggers heavily on a busy, high transaction volume table?

Advanced

Answer

Triggers execute synchronously as part of the same transaction as the original statement, meaning any additional work performed inside a trigger directly adds to the time that transaction takes to complete and the duration locks are held, so a poorly optimized or overly complex trigger on a very busy table can become a significant performance bottleneck across the entire application.
-- A trigger performing a slow, unindexed lookup
-- will slow down every single insert on this table
CREATE TRIGGER trg_SlowCheck
ON Orders
AFTER INSERT
AS
BEGIN
  IF EXISTS (SELECT 1 FROM VeryLargeTable WHERE UnindexedColumn = 'value')
    PRINT 'Found';
END;
Real-world example A team notices insert performance on their orders table has degraded significantly since adding a trigger, discovering it was performing an unindexed lookup against a large unrelated table, and fixes it by properly indexing that lookup.

Common follow-ups: How do you measure the specific performance impact a trigger is adding to a table's write operations?;What alternatives to triggers might be considered for less time sensitive additional processing?

Query Optimization & Plans;Change Data Capture (CDC)
All 7 Triggers questions