This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Do triggers start to degrade in performance (or sometimes fail) on highly transactional tables?
Question
How many transactions per second can SQL Server table triggers handle? If I had a highly transactional table that was inserted into 1,000 times a second, are insert triggers on that table reliable to fire every single time?
asked 2019-12-21 by J.D.
Answer
are insert triggers on that table reliable to fire every single time?
Absolutely. Triggers aren’t optional, they WILL fire.
Triggers CAN cause a performance impact on highly transactional tables because the trigger fires synchronously to your DML operation. On an insert trigger, if the trigger execution fails, your insert fails. You won’t get confirmation that your insert completed successfully until your trigger has also completed successfully.
In this way, your insert trigger WILL make your insert more expensive, because the trigger is adding work to a previously simple INSERT statement. Whether it’s going to be acceptable performance will depend on what your trigger is doing.
The same performance problems that can affect any query or procedure can also affect triggers. You’ll need to tune them and pay attention to whether that trigger is doing it’s job effectively.
How many transactions per second can SQL Server table triggers handle?
This isn’t a straightforward question. It depends on your schema, your data access patterns, your server hardware, etc. Needless to say, some companies are absolutely running SQL Server at massive scale with transactions per second that are mind boggling. But they have fast hardware, large budgets, and very skilled teams building their systems.
For this part of the question to be answerable, you’d need to propose a specific scenario & problem to see if anyone can help resolve that more specific issue.
answered 2019-12-21 by Andy Mallon