FIX: Statistics Maintenance Creates Significant Bottleneck on SQL Servers that Use 16 or More CPUs

Article translations Article translations
Article ID: 293849 - View products that this article applies to.
This article was previously published under Q293849
BUG #: 217586 (SHILOH_BUGS)
Expand all | Collapse all

SYMPTOMS

The wait time that results from an update of the sysindexes system table during a transaction commit may be a significant bottleneck in transaction throughput. The wait time is not a problem on multi-processor computers with 2, 4 or 8 processors. However, the wait time is a major bottleneck for servers with 16 and 32 processors (16 way and 32 way computers).

CAUSE

Upon transaction commit, all the updates of a page or row count are performed directly to the sysindexes system table. Although the update is not a problem for 2, 4 and 8 way computer, it is a bottleneck for 16 way or 32 way computers.

RESOLUTION

To resolve this problem, obtain the latest service pack for 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
The page or row count changes are now cached and the sysindexes system table is periodically updated. To limit the duration of the values in cache, the cache is flushed frequently. The frequency of cache flushing is based on the percentage of changes in cache compared to disk or a small time interval since the last cache flush.

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

Properties

Article ID: 293849 - Last Review: November 6, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB293849

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