FIX: A SQL Server 7.0 Transact-SQL scheduled task may run slower in SQL Server 7.0 and SQL Server 2000

A scheduled Microsoft SQL Server 7.0 Transact-SQL (T-SQL) task may run slower in SQL Server 7.0 or SQL Server 2000 than in Query Analyzer or Microsoft SQL Server 6.5.

Because the SQL Server Agent has to share server resources with the SQL Server engine, the SQL Server Agent introduces a delay when you process T-SQL task output. The purpose of the delay is to avoid consuming all available CPU resources when you run the task.

This behavior is designed to:

• Allow multiple jobs to run in parallel.
  
  -and-
  
  
• Decrease the impact on CPU resources, especially on Microsoft Windows 95 or Microsoft Windows 98.

This allows multiple jobs to run in parallel; however, because each job gets CPU time, in the case where there is only one scheduled job a decrease in job throughput occurs.

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

To obtain maximum throughput when there is only one scheduled job, create the task as a scheduled Cmdexec task that runs the OSQL utility.

If you have procedures that return several empty result sets (informational DONE_IN_PROC messages), you can reduce the number of delays that occur when you run the task as a scheduled T-SQL task by adding a SET NOCOUNT ON statement to the beginning of the stored procedure or T-SQL batch.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.