This error occures on multi-processor MSSQL servers.

Seems to be agravated by service packs, including windows OS service packs. It has been observed to occur on both W2k & W2003 servers and since there has not been a SQL service pack in a while, seem to be aggravated most by windows service packs

The Problem;

larger queries can across multiple processors causing a deadlock if they are not designed well. If this starts showing up for quiries that worked before, try service packs....

Solution- summary;

Check service pack level- easiest

tune the query(only if above did not work)

change the

See notes;

refrering BOL:

SQL Server 2000 supports intra-query parallelism on servers that have more than one microprocessor, or CPU. Individual SQL statements can be split into two or more tasks that operate concurrently to return the results faster.

Comment from luani
Date: 07/14/2003 03:30AM PDT
Comment


Expand a server group.


Right-click a server, and then click Properties.


Click the Processor tab.


Under Parallelism, select the number of processors to execute queries in parallel.
By default, all available processors are used.


Accepted Answer from Cynicszm
Date: 07/14/2003 03:35AM PDT
Grade: C
Accepted Answer


1. In the SQL Server settings ----> Processor tab on a multiple processor machine you can specify which processors to use when executing a query. There is also a Parallelism setting to use All available processors.

When SQL Server executes a query in parallel, it breaks down this single query into subsections and spreads these subsections across multiple processors. (intra-query parallelism

2.a. To solve the deadlock examine the T-SQL involved in the deadlock and ensure that the objects are accessed in the same order.

2.b. Use showplan - to see the objects involved and troubleshoot the execution.

3. Query hint(maxdrop 1) - When you run the SQL specify the OPTION (MAX DROP 1) to disable parallelism for that query. This is a last resort - troubleshoot the deadlock first.



Comment from Cynicszm
Date: 07/14/2003 03:37AM PDT
Comment


To troubleshoot the deadlock you can also enable TRACE FLAG 1204 to output detailed deadlock information in the errors.log file.

DBCC TRACEON (1204)

This flag is set for entire SQL server (or instance) depending on the SQL version you are using.


ref

http://www.experts-exchange.com/Dat...&topics=42




Posted:5/10/2005 4:41:36 PM Delete Edit Quote Reply
Found this info on MSFT web page at;

http://support.microsoft.com/?kbid=837983

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

Back to the top

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

Back to the top

REFERENCES
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
315662 FIX: Parallel query may encounter undetected deadlock with itself
Back to the top