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

SYMPTOMS

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:

Msg 8650:
Intra-query parallelism caused your server command (process ID #50) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).

WORKAROUND

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:

Start SQL Query Analyzer, and then connect to your instance of SQL Server.
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

MORE INFORMATION

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