Article ID: 249730
Article Last Modified on 11/17/2004
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q249730
BUG #: 226979 (Shiloh_bugs)
BUG #: 56134 (SQLBUG_70)
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.
Additional query words: scheduled job TSQL execution time performance slow SQLAgent SQLServerAgent
Keywords: kbbug kbfix KB249730
