SQL Server Triggers and Performance

SQL Server Triggers can cause performance problems if best practises are not considered. They're useful for tasks such as enforcing referential integrity but if not not applied correctly can cause performance issues, often difficult to diagnose.

The only time I’ve ever used Triggers is to apply Data Modification Audits. I avoid them – as there is normally always a solid alternative , such as stored procedures or constraints. As a DBA , I support many applications where SQL Triggers are used – here are some of the problems and observations I’ve encountered in supporting  SQL TRIGGERS

1) Triggers are not slower than any other database object. The usage and application of  logic causes the performance hit

2) Triggers are compiled

3) Use Triggers where useful. My basic rule – if business logic needs to be applied use stored procedure and do not apply business logic in Triggers

4) Keep it Simple. Don’t get complex with Triggers –  

5) Query Tuning. Apply best practises.What is SQL Server Query Tuning? - SQL Server DBA

6) Avoid nested or recursive triggers  - DML and DDL triggers which initiate another trigger. SQL server has up to 32 levels of nested trigger levels

To disable nested triggers , use these methods

--Method 1
sp_configure 'nested_triggers',0
GO
RECONFIGURE
GO
--Method 2
ALTER DATABASE DB_NAME
SET RECURSIVE_TRIGGERS ON | OFF

 Read More

 SQL Server – List of Triggers - SQL Server DBA

SQL Server – Delete SQL Logon TRIGGER syntax - SQL Server DBA

How to troubleshoot a slow running query - SQL Server DBA