SQL Server Performance Tuning

How Triggers Affect Your SQL Server Performance

Updated October 29, 20252 min read

Are triggers slowing your SQL Server? A curving line of dominoes topples one after another.

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.
Finding triggers on a table in object explorer
Figure 1 - Finding triggers on a table in Object Explorer

Triggers on a database

  1. Open SSMS, expand the Databases and choose your database.
  2. Click on Programmability and
  3. Choose Database Triggers to list all items.
Finding triggers on a database in object explorer
Figure 2 - Finding triggers on a database in Object Explorer

How to identify the triggers

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

T-SQL
SELECT  table_name = OBJECT_NAME(parent_object_id)
	,        trigger_name = name
	,        trigger_owner = USER_NAME(schema_id)
	,        OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS isupdate
	,        OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS isdelete
	,        OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS isinsert
	,        OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') AS isafter
	,        OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof
	,        CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')          
		WHEN 1
			THEN 'Disabled'          
		ELSE 'Enabled'        
		END AS STATUS FROM    sys.objects WHERE   type = 'TR'
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

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜