Microsoft KB Archive/288122

= FIX: Lock Monitor Uses Excessive CPU =

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 &quot;Batch Requests/sec&quot; 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 &quot;%Processor Time&quot; 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 &quot;sleeping&quot; and will &quot;track&quot; at close to 0 (zero) processor utilization.

Examination of the value reported for the &quot;cpu&quot; column of the sysprocesses table in the master database for the &quot;LOCK MONITOR&quot; server process id (SPID), SPID 4, shows that the &quot;LOCK MONITOR&quot; 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.