SQL Server Triggers and Performance

08 May,2013 by Jack Vamvas

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
--Method 2

 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

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server Triggers and Performance

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer