Article ID: 288122 - View products that this article applies to.
This article was previously published under Q288122
BUG #: 351394 (SHILOH_bugs)
BUG #: 352221 (SHILOH_bugs)
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.
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.
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:
(http://support.microsoft.com/kb/290211/EN-US/ )INF: How to Obtain the Latest SQL Server 2000 Service Pack
HotfixNOTE: 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
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:
(http://support.microsoft.com/kb/262973/EN-US/ )PRB: Thread Starvation Detected Message Reported in Error Log
(http://support.microsoft.com/kb/224587/ )HOW TO: Troubleshoot Application Performance with SQL Server
(http://support.microsoft.com/kb/224453/EN-US/ )INF: Understanding and Resolving SQL Server 7.0 Blocking Problems
(http://support.microsoft.com/kb/271509/EN-US/ )INF: How to Monitor SQL Server 2000 Blocking
(http://support.microsoft.com/kb/263889/EN-US/ )INF: SQL Blocking Due to [[COMPILE]] Locks
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.
REFERENCESSQL Server 2000 Books Online; topic: "Detecting and Ending Deadlocks"