PRB: Access Violation Exception Occurs When Optimized Query Plan Uses Hash Match Team Operator (818671)

WORKAROUND

To work around this problem, enable trace flag 8679. Trace flag 8679 prevents the SQL Server optimizer from using a Hash Match Team operator during query optimization. To enable trace flag 8679, use one of the following methods.

Method 1: Use the Transact-SQL DBCC TRACEON Statement

To enable the trace flag for the current client connection, run the following code in SQL Query Analyzer:
```
DBCC TRACEON (8679)
```
To enable the trace flag for all the client connections, run the following code in SQL Query Analyzer:
```
DBCC TRACEON (8679,-1)
```

Note If you enable the trace flag by using DBCC TRACEON, enable the trace flag each time that you restart SQL Server.

Method 2 : Use the Transact-SQL Server Startup Parameter

To set a server-wide trace flag, use the Transact-SQL Server startup parameter. To do so, follow these steps:

In SQL Server Enterprise Manager, right-click <server name>, and then click Properties.
Click the General tab, and then click Startup parameters.
Type the following text in the Parameter box: -T8679
Click Add, and then click OK two times.
Restart the MSSQLServer services.

For additional information about SQL Server startup parameters, click the following article number to view the article in the Microsoft Knowledge Base:

200103 INF: SQL Server 7.0 Startup Parameters

MORE INFORMATION

If you have a query that fails with an access violation exception, verify the query plan to determine if a Hash Match Team operator is involved. You can use the Transact-SQL SET SHOWPLAN_ALL command or a tool such as SQL Query Analyzer to view the estimated execution plan for your query. When the access violation exception occurs, SQL Server may generate a stack dump file, write the information to the SQL Server error log file, and then generate a symptom dump file.

The following stack is a partial stack trace that corresponds to the access violation exception caused by the Hash Team operators on SQL Server 7.0 Service Pack 3 (SP3) servers:

*******************************************************************************************
*
		  BEGIN STACK DUMP: 
* 03/21/03 18:05:18 spid 9
* 
* Exception
		  Address = 0041F3D8 (ps_dl_sqlhilo + bd)
* Exception Code = c0000005 E
		  
* Access Violation occurred reading address 00000000 

Short Stack
		  Dump

0x0041f3d8 Module(sqlservr+1f3d8) (ps_dl_sqlhilo+bd)
		  
0x0042bfba Module(sqlservr+2bfba) (CSsStr::CmpCompareStr+21)
		  
0x00639aeb Module(sqlservr+239aeb)
		  (CTEsCompareTuple<167>::I4CompareTupleXcArgArg+37) 
0x00410d75
		  Module(sqlservr+10d75) (CEs::GeneralEval+c7) 
0x0065f76f
		  Module(sqlservr+25f76f) (CQScanMergeJoin::GetRow+3f1) 
0x005516d8
		  Module(sqlservr+1516d8) (CQScanHashMatch::Iterate+435) 
0x0042611c
		  Module(sqlservr+2611c) (CQScanHashMatch::GetRow+b1) 
0x0066a292
		  Module(sqlservr+26a292) (CQScanXProducer::Open+31) 
0x00669f3f
		  Module(sqlservr+269f3f) (FnProducerThread+1e3) 
0x004ad883
		  Module(sqlservr+ad883) (subproc_main+f4)
0x41092a15 Module(ums+2a15)
		  (ProcessWorkRequests+ed) 
0x410932cb Module(ums+32cb)
		  (ThreadStartRoutine+139) 
0x7800bea1 Module(MSVCRT+bea1) (beginthread+ce)
		  
0x77e837cd Module(KERNEL32+37cd) (TlsSetValue+f0)
		  
**********************************************************************************

For additional information about the Hash Match Team and Hash Match Root operators, see the "Hash Match Team" and "Hash Match Root" topics in SQL Server Books Online.

PRB: Access Violation Exception Occurs When Optimized Query Plan Uses Hash Match Team Operator (818671)

SYMPTOMS

WORKAROUND

Method 1: Use the Transact-SQL DBCC TRACEON Statement

Method 2 : Use the Transact-SQL Server Startup Parameter

MORE INFORMATION

REFERENCES