When I run a stored procedure in Query Analyzer, it runs in about two minutes. But if I execute the same stored procedure as a job, it runs several hours.

Question

When I run a particular stored procedure in Query Analyzer, it runs in about two minutes.  But if I execute the same stored procedure as a scheduled job, it runs several hours before it completes. What could be the obvious reasons for this?

Answer

When I first got this question, I didn't have an answer. Based on my previous experience, a stored procedure is a stored procedure, no matter what is used to fire it off. I would expect some time differences between runs of the stored procedure, but not of the magnitude experienced by this DBA.

Since I had never run across this problem before, I suggested that the DBA use Profiler to capture a trace of running the same stored procedure under both Query Analyzer and as a scheduled job to see if he could find any clues as to the cause of the problem.

The DBA wrote me back soon afterward, with a solution to this mystery. Apparently, the problem lied in the fact that the exact same code was not being run in both the Query Analyzer and the job. The difference between the code was very minor, so minor that many people would probably have ignored it. The difference was that in the stored procedure running as part of a job, the stored procedure did not have the "SET NOCOUNT ON" statement. Once this was added to the stored procedure, it ran very quickly as a job, and the performance problem went away.

There are several lessons to be learned from this. First, be sure when your are performance testing that you are actually comparing apples to apples. In this case, the same code was not actually being run in both Query Analyzer and the job. Second, sometimes simple things, such as a missing statement, can make a lot of difference in performance. And last, some performance tips are more valuable than others. For example, on the website I have this tip:

Include in your stored procedures the statement, "SET NOCOUNT ON". If you don't turn this command on, then every time a SQL statement is executed, SQL Server will send a response to the client indicating the number of rows affected by the statement. It is rare that this information will ever be needed by the client. Using this statement will help reduce the traffic between the server and the client. [6.5, 7.0, 2000, 2005]

It seems like a minor tip, but it is actually one of the most important ones you need to follow when optimizing your stored procedures.