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

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.

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.

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
  					

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: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.