FIX: Lock Monitor Uses Excessive CPU

This article was previously published under Q288122
This article has been archived. It is offered "as is" and will no longer be updated.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack


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				
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
263889 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.


SQL Server 2000 Books Online; topic: "Detecting and Ending Deadlocks"

Article ID: 288122 - Last Review: 01/16/2015 21:33:53 - Revision: 3.1

  • Microsoft SQL Server 2000 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix kbqfe kbsqlserv2000sp1fix KB288122