Are the triggers degrading your SQL Server performance?

Category: Performance
Item: Trigger usage

What are SQL Server triggers?

They are a particular type of stored procedure that automatically runs when an event occurs in the database.

Why should you care about them?

The delete, update, and insert operations against theses objects incur extra costs, which affects performance.

Massive bulks and recursive triggers can cause severe performance.

How can I Find the triggers?

Triggers on a table

  1. Open SSMS, expand the databases, and choose your database.
  2. Click on the target table and Expand the triggers to list all items.

Triggers on a database

  1. Open SSMS, expand the databases, and choose your database.
  2. Click on programmability and
  3. Choose triggers to list all items.

You can use the query below to identify all the triggers in your database tables:

  1. SELECT  table_name = OBJECT_NAME(parent_object_id) ,
  2.         trigger_name = name ,
  3.         trigger_owner = USER_NAME(schema_id) ,
  4.         OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS isupdate ,
  5.         OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS isdelete ,
  6.         OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS isinsert ,
  7.         OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') AS isafter ,
  8.         OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof ,
  9.         CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')
  10.           WHEN 1 THEN 'Disabled'
  11.           ELSE 'Enabled'
  12.         END AS status
  13. FROM    sys.objects
  14. WHERE   type = 'TR'
  15. ORDER BY OBJECT_NAME(parent_object_id)

How to fix them?

Triggers are not always bad, but they must be used wisely.

  1. Look into triggers with the highest resource consumption.
  2. See if they can be replaced by another method.
  3. If not, see if the trigger code can be optimized.

More information

Microsoft – Create DML Triggers.
Daniel Calbimonte, SQLShack – Are SQL Server database triggers evil?

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.