You may receive error message 8650 when you run a query that uses intra-query parallelism

When you try to run a query on a multiprocessor computer, the query may stop responding or you may receive an error message if both of the following conditions are true:

• The max degree of parallelism option for your instance of Microsoft SQL Server 2000 Service Pack 3 (SP3) is set to 0 or to a number that is greater than 1.
• The query uses intra-query parallelism on a multiprocessor computer.

You may receive the following error message:

This problem occurs because SQL Server detected a deadlock that involves the threads that are used by the same parallel query.

To work around this problem, use one of the following methods:

Method 1: Enable the pre-SQL Server 2000 SP3 parallel scan behavior

To work around this error message, enable the pre-SQL Server 2000 SP3 parallel scan behavior, and then run the SQL Server query on the multiprocessor computer. To enable the pre-SQL Server 2000 SP3 parallel scan behavior, enable trace flag 683 that is included in SQL Server post-SP3 hotfix builds 8.00.0765 or later. To enable trace flag 683, follow these steps:

1. Start SQL Query Analyzer, and then connect to your instance of SQL Server.
2. Run the following Transact-SQL statement in SQL Query Analyzer:

   DBCC TRACEON (683)

Method 2: Run the query again

When this problem occurs, you can try to run the query again. If an application that is running SQL Query Analyzer receives the error message, you must configure the application to run the query one or more times when the problem occurs.

If you receive the error message when you run a SQL Server job, such as a replication job, you can add a job step to run the query again when the problem occurs.

Method 3: Use the loop or merge join strategy

To avoid a deadlock when a query execution plan uses intra-query parallelism, add the OPTION (MERGE JOIN, LOOP JOIN) query hint to your parallel query.

Method 4: Suppress the generation of a parallel plan

To avoid a deadlock, suppress the generation of a query execution plan that uses intra-query parallelism. To do so, use one of the following methods:

• Add the OPTION (MAXDOP 1) query hint to your query to disable parallel plan generation for the specific query.
• Disable the parallel plan generation globally for your instance of SQL Server by setting the max degree of parallelism option to 1. To do so, run the following Transact-SQL statement:

  USE master
  EXEC sp_configure 'show advanced option', '1'
  RECONFIGURE WITH OVERRIDE
  GO
  
  EXEC sp_configure 'max degree of parallelism', 1
  RECONFIGURE WITH OVERRIDE
  GO
  

Method 5: Rewrite the query

To work around this problem, rewrite the query to reduce the chance that a deadlock will occur.

Method 6: Perform general performance tuning

You can perform general performance tuning for your query or your SQL Server database. To significantly improve query performance and to reduce the chance that a deadlock will occur, you can create or update the column statistics and indexes. You must evaluate the indexes on the tables that are involved in your query, and you must also run the UPDATE STATISTICS Transact-SQL statement on your instance of SQL Server. For more information about query tuning in SQL Server 2000, see the following topics in SQL Server Books Online:

• Query tuning
• Query tuning recommendations
• Parallel query recommendations

When you run a query on a multiprocessor computer, SQL Server determines if the current system workload and the configuration information can support the parallel query execution. SQL Server also determines if the performance of your query can be improved by using a parallel plan. Before it actually runs the query that uses intra-query parallelism, SQL Server determines the number of threads that will be used to run the query. Therefore, every time that you run the query, the number of threads that are used to run the query may vary.

For example, when you run a query that uses intra-query parallelism, SQL Server may use a serial plan to run the query. If you run the same query for the second time, SQL Server may use a parallel plan that involves three threads to run the query. Similarly, if you run the same query for the third time, SQL Server may use a parallel plan that involves five threads to run the query. Therefore, you may not receive the error message that is mentioned in the "Symptoms" section every time that you run the query.

For more information about parallelism, see the following topics in SQL Server Books Online:

• Degree of parallelism
• Deadlocks involving parallelism

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: