I do not like triggers at all and in my life, I have never written a trigger for my business logic. Recently, I had a client for  Comprehensive Database Performance Health Check who had lots of performance problems. After investigation, we figured out it is due to many triggers on their key tables. As soon as we did disable all triggers on the tables, we were able to see a huge performance improvement.
Here is the script which can enable or disable all triggers on your server.
Disable All Triggers
DISABLE Trigger ALL ON ALL SERVER;
Enable All Triggers
ENABLE Trigger ALL ON ALL SERVER;
If you are facing performance issues with your SQL Server, I would suggest that you try out this option of disabling all the triggers on your server, I am confident that it will give your SQL Server a performance boost. However, you should try this out first on your developer server because if you do this on a production server, you may start having data integrity issues.
Often people ask me what is the replacement of the triggers, my answer is simple, include your workflow of what you need in the trigger in your T-SQL code or Stored Procedure. Ideally, if you plan well, there is no need for triggers.
Let me know if you are interested to know more about this topic. I will write more blogs as well as create an SQL in Sixty Seconds video.
Here are my few recent videos and I would like to know what is your feedback about them.
- Bitwise Puzzle – SQL in Sixty Seconds 160
- Find Expensive Queries – SQL in Sixty Seconds #159
- Case-Sensitive Search – SQL in Sixty Seconds #158
- Wait Stats for Performance – SQL in Sixty Seconds #157
- Multiple Backup Copies Stripped – SQL in Sixty Seconds #156
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
