Article ID: 288122
Article Last Modified on 9/12/2003
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q288122
BUG #: 351394 (SHILOH_bugs)
BUG #: 352221 (SHILOH_bugs)
SYMPTOMS
A SQL Server server appears to stop responding (hang), and query throughput on the server as measured by the "Batch Requests/sec" counter of the SQL Server:SQL Statistics object in System Monitor (Sysmon) is severely hampered.
If you monitor the CPU utilization of threads in the SQL Server process space by using the "%Processor Time" counter of the Threads object in the System Monitor, one thread exhibits high CPU utilization. All other threads (or the vast majority of threads) appear to be "sleeping" and will "track" at close to 0 (zero) processor utilization.
Examination of the value reported for the "cpu" column of the sysprocesses table in the master database for the "LOCK MONITOR" server process id (SPID), SPID 4, shows that the "LOCK MONITOR" has used a large amount of CPU time, and is continuing to accumulate CPU as demonstrated by successive queries to the sysprocesses table. Additionally, many SPIDs in the sysprocesses table may be be blocked waiting for some type of lock resource.
CAUSE
SQL Server 2000 deadlock detection was enhanced to detect thread level deadlocks. In some instances, the thread deadlock detection was overly aggressive, and may lead to excessive CPU consumption by the lock monitor thread.
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
NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 1.
The English version of this fix should have the following file attributes or later:
File name Platform
--------------------
s80249i.exe x86
WORKAROUND
To encounter this problem, it is expected that SQL Server is experiencing some type of performance bottleneck that is severely inhibiting the throughput of SQL Server. Correcting the aforementioned bottleneck also indirectly address this problem. Two areas that warrant investigation are poor query performance and blocking. Refer to the following articles in the Microsoft Knowledge Base for assistance with diagnosing and troubleshooting such issues:
262973 PRB: Thread Starvation Detected Message Reported in Error Log
224587 HOW TO: Troubleshoot Application Performance with SQL Server
224453 INF: Understanding and Resolving SQL Server 7.0 Blocking Problems
271509 INF: How to Monitor SQL Server 2000 Blocking
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 2000 Service Pack 1.
REFERENCES
SQL Server 2000 Books Online; topic: "Detecting and Ending Deadlocks"
Additional query words: SPIN CPU TIMEOUT BLOCK HUNG LOOP
Keywords: kbbug kbfix kbqfe kbsqlserv2000sp1fix KB288122
