Triggers make it easy to automatically synchronize the behavior of data between tables and across databases. But their performance can be drastically affected by things that might not be obvious at first glance. Here I'll identify one command to reduce the impact of some unforeseen problems.
Take, for example, INSERT triggers that are fired repeatedly, especially when using INSERT INTO statements for massive insert operations. In such cases where the trigger is fired over and over again during the course of the statement, the trigger will issue DONE_IN_PROC messages for each INSERT action, which can slow things down drastically.
This slow down is especially pronounced if the trigger is being fired as the result of a scheduled SQL Server Agent job. SQL Server Agent automatically imposes a delay after each DONE_IN_PROC signal to avoid server congestion. If you try running the same set of commands through the Query Analyzer, it will execute much faster since no such delays are imposed. If you run such a query through Query Analyzer and see multiple "n rows affected" statements, there's a good chance the query is iterating repeatedly and re-firing the trigger many more times than it really needs to.
To turn off DONE_IN_PROC messages, use the SET NOCOUNT ON command at the start of a trigger statement. Most of the time the row count isn't needed for a trigger anyway. If it is, you may want to consider restructuring the commands that fire the trigger (or the trigger itself) so the changes are done as one. If the process is still taking too long, you should rethink the way triggers are fired for the sake of simplicity.
Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators -- please share your thoughts as well!
This was first published in January 2005