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

Article translations Article translations
Article ID: 249730 - View products that this article applies to.
This article was previously published under Q249730
BUG #: 226979 (Shiloh_bugs)
BUG #: 56134 (SQLBUG_70)
Expand all | Collapse all

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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:
301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack

WORKAROUND

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.

STATUS

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.

Properties

Article ID: 249730 - Last Review: November 17, 2004 - Revision: 4.3
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbbug kbfix KB249730

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com