Microsoft KB Archive/255712

= FIX: UPDATE May Fail to Update All the Qualifying Rows on Multi-Proc Computers =

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.