FIX: Miscalculation of CPU Time Occurs If SQL Server Uses a New Thread to Execute a Batch on a Connection That Already Exists (309377)

CAUSE

SQL Server maintains a pool of worker threads that execute the tasks given to it. SQL Server may assign a different thread from this pool to a given SPID to execute a new query batch. When SQL Server assigns a different thread to a SPID, SQL Server does not properly calculate the CPU time to indicate the accumulated CPU time up to that point for the SPID.

RESOLUTION

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

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

Hotfix

The English version of this fix should have the following file attributes or later:

   Version      File name       Platform
   -------------------------------------
   8.00.440     s80440i.exe     x86

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

MORE INFORMATION

Steps to Reproduce Problem

To reproduce this problem, follow these steps:

Start a Query Analyzer session and connect to a SQL Server 2000 instance.

Continuously execute this code:

Select SPID, CPU from master..sysprocesses where SPID = @@spid

The value returned for the CPU column may not increase consistently.

You can also reproduce this behavior by starting a SQL Profiler Trace and trace only the Audit:Logout event. The CPU time reported may be much larger than the "Duration" time of the session.

REFERENCES

SQL Server 2000 Books Online; topic: "max worker threads Option"