FIX: Lock Monitor Uses Excessive CPU

Article translations Article translations
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)
Expand all | Collapse all

On This Page

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
263889 INF: SQL Blocking Due to [[COMPILE]] Locks

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"

Properties

Article ID: 288122 - Last Review: September 12, 2003 - Revision: 3.1
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbqfe kbsqlserv2000sp1fix KB288122

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com