Article ID: 255712
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q255712
BUG #: 57572 (SQLBUG_70)
SYMPTOMS
An UPDATE statement, which uses a join, may fail to update all of the qualifying rows if all the following conditions are true:
- The ALTER TABLE command is used to add a column on the table that is to be updated.
- The UPDATE statement updates the new column.
- The table to be updated has a unique clustered index defined on the join column.
- The update is performed on a multi-processor computer.
- The update results in page-splits.
- Microsoft SQL Server chooses a parallel scan for the update. That is, SQL Server uses multiple processors to perform the update.
CAUSE
You cannot use parallel table scans if pages might split during the scan.
RESOLUTION
The resolution is to use a serial scan instead of a parallel scan.
WORKAROUND
Here are two ways to work around the problem:
- Use the "MAXDOP 1" query hint to perform the update.
Set the max degree of parallelism configuration option to 1 to suppress parallel plan generation as follows:
EXEC sp_configure 'max degree of parallelism', 1 GO RECONFIGURE with Override GO
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
REFERENCES
For more information about the MAXDOP query hint and configuration setting, refer to the "UPDATE (T-SQL)" topic or "max degree of parallelism Option" topic in SQL Server Books Online.
Keywords: kbbug kbfix KB255712
