Blitz Result: Triggers Found on Tables

Lemme start out by saying we don’t have a problem with triggers in general.  Triggers can be an easy, bulletproof way to enforce business logic in the database tier.

Unfortunately, triggers don’t always scale well – especially when we try to perform a lot of row-by-row logic inside the triggers.

This part of our SQL Server sp_Blitz® script checks sys.triggers in each database.

To Fix the Problem

Assess your trigger health using one of the two methods below.

Return to sp_Blitz® or Ask Us Questions

---

How to Tell if Triggers Are Killing Performance

If You’re on SQL Server 2012 or higher, Your Life Is Easier

This is easier to check on SQL Server 2012 and later, because we get more accurate information in the SQL Server plan cache for trigger execution. Run sp_BlitzCache® and look at your top use plans and see if triggers are among your top offending queries.

Otherwise, Sort Through Those Triggers

SQL Server 2008R2 and earlier has a bug where trigger execution count in the DMVs can’t be trusted. This means you need to take a more manual approach.

Script out each trigger to understand what it’s doing.  Make sure it handles multiple records successfully – most don’t.  Generally speaking, we can’t rip triggers out right away, but we at least want to be aware that they’re around when we try to do performance tuning or why we’re troubleshooting record insert/update issues.