0

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/

  • Report from SO stackoverflow.com/questions/57029133/… because was off-topic – user960567 Jul 15 '19 at 7:44
  • Is there any specific reason you want to use trigger and not stored procedure? Which type you want to use meaning after or instead? I guess, it would be after. You may go for stored procedure as it would be easier to extend in terms of business logic in the future and won't put load on table in comparison to trigger. – Learning_DBAdmin Jul 15 '19 at 7:50
  • The problem is that there are 100 of legacy systems are using this table. – user960567 Jul 15 '19 at 14:19
  • Your question is not clear, what does it mean: "When I try to add a trigger to this table, SQL Server takes too much time"? Do you mean it takes too long to execute your CREATE TRIGGER statement or every time you add it, all the queries begin to take too much or deadlocks appear? – sepupic Jul 15 '19 at 14:24
  • Yes it takes too much to create the trigger – user960567 Jul 15 '19 at 14:26
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.