Article ID: 837983 - View products that this article applies to.
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:
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).
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 behaviorTo 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:
Method 2: Run the query againWhen 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 strategyTo 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 planTo 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:
Method 5: Rewrite the queryTo work around this problem, rewrite the query to reduce the chance that a deadlock will occur.
Method 6: Perform general performance tuningYou 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:
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:
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/315662/ )FIX: Parallel query may encounter undetected deadlock with itself
Contact us for more help
Connect with Answer Desk for expert help.