Use SET NOCOUNT ON for better trigger performance

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

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.