Implications of adding a trigger on production database

Posted on

Question :

I am trying to add a trigger on a production database table which includes millions of rows and is used very heavily. There are around 70k-100k inserts per day.

When I try to add a trigger to this table, SQL Server takes too much time – until I stop the execution or SQL Server throws a deadlock error.

I need to run this trigger on production. How can I add a trigger with minimal impact on running the production database?

The trigger is just inserting a record in a audit table 100% similar to https://www.mssqltips.com/sqlservertip/5909/sql-server-trigger-example/

Answer :

To create a trigger on a table a Sch-M lock must be taken on that table. SQL Server allows a single schema modification lock (Sch-M) on any locked object, so a transaction which creates the trigger must wait to acquire a Sch-M lock on the target object.

While a process is waiting to acquire a Sch-M lock it is blocking other processes which can try to begin using the table.

You should try to create the trigger during your maintenance window when the table is free from other locks.

The Sch-M lock is Evil – this is on indexes rebuilding but lock mechanics is the same.

Trigger won’t scale.

When it is needed, try to define one trigger per DML operation.

It means one for INSERT, one for UPDATE and one for DELETE.

IMO in order to find out who creates a record or update it, there is no need to use triggers.

However, deleting is a completely different story. In that case, check out my article How to Capture Deleted Records

Leave a Reply

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