Microsoft KB Archive/818671

= PRB: Access Violation Exception Occurs When Optimized Query Plan Uses Hash Match Team Operator =

Article ID: 818671

Article Last Modified on 1/18/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-





SYMPTOMS
If the query plan uses Hash Team operators during query optimization, an access violation exception may occur in a SELECT statement. Query plans use Hash Team operators in the following two situations:
 * If the query performs two or more joins on the same set of columns.
 * If the query performs a grouping operation with one or more joins on the same set of columns.



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:
 * 1) In SQL Server Enterprise Manager, right-click , and then click Properties.
 * 2) Click the General tab, and then click Startup parameters.
 * 3) Type the following text in the Parameter box: -T8679
 * 4) Click Add, and then click OK two times.
 * 5) 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



Address = 0041F3D8 (ps_dl_sqlhilo + bd)
 * Exception


 * 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 ::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 &quot;Hash Match Team&quot; and &quot;Hash Match Root&quot; topics in SQL Server Books Online.

