BUG: UPDATE Statement with SELECT DISTINCT Subquery with Parallelism Enabled May Cause Access Violation (263695)


The information in this article applies to:

  • Microsoft SQL Server 7.0 Service Pack 2
This article was previously published under Q263695
BUG #: 57948 (SQLBUG_70)

SYMPTOMS

When you run an UPDATE statement with a SELECT DISTINCT subquery, which involves a merge join, an access violation (AV) may occur.

This only happens on a multi-processor computer that has the Max Degree of Parallelism set to 0 (ON).

WORKAROUND

Set the Max Degree of Parallelism option to 1 (OFF) to prevent the access violation. You can set this value either at the:
  • Server level by using the Sp_configure option or from Enterprise Manager.

    For example:

    1. From Query Analyzer, running the following code to turn off Max degree of Parallelism: 
      sp_configure 'max degree of parallelism', 1
go
reconfigure with override
go
    2. From Enterprise Manager, right-click the server name, click Properties, and then click the Processor tab. Under the Parallelism category, select the option Use # of Processor(s) and specify 1 as the value.
    -or

  • Query level by using the hint "OPTION (MAXDOP 1)".

    For example: 
    USE PUBS
GO
SELECT * FROM authors OPTION (MAXDOP 1)
GO
    The code turns the Max Degree of Parallelism off at the query level without affecting any other queries or operations.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbpending KB263695
©2003 Microsoft Corporation. All rights reserved.