Problem solve Get help with specific problems with your technologies, process and projects.

Use SET NOCOUNT ON for better trigger performance

Trigger performance is often slowed by unforeseen, automatic SQL Server delays. Serdar Yegulalp identifies the command you need to keep triggers running quickly.

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 last published in January 2005

Dig Deeper on Windows Operating System Management

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.