FIX: Access Violation with Queries that Involve Hash Join on Large Data Sets

Article translations Article translations
Article ID: 275304 - View products that this article applies to.
This article was previously published under Q275304
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 58130 (SQLBUG_70)
Expand all | Collapse all

SYMPTOMS

Complex queries involving hash joins on large data sets or data sets involving skewed distribution may be converted to merge joins when the query runs. This causes an access violation (AV) with a stack that looks similar to:

Short Stack Dump
0x0041773b Module(sqlservr+1773b) (CValRefRow::PvalInvokeSpecific+d)
0x00412ad8 Module(sqlservr+12ad8) (CEsCompValSeg::CreateExecValSeg+c0)
0x004129e8 Module(sqlservr+129e8) (CEsExec::CEsExec+10a)
0x0041298e Module(sqlservr+1298e) (CEs::Startup+3f)
0x004176ec Module(sqlservr+176ec) (CQueryExecContext::StartupExpr+15)
0x004d388a Module(sqlservr+d388a) (CQScanSort::CQScanSort+9e)
0x004d37de Module(sqlservr+d37de) (CXteSort::QScanGet+38)
0x00427a2d Module(sqlservr+27a2d) (CQScanStreamAggregate::CQScanStreamAggregate+b0)
0x00427981 Module(sqlservr+27981) (CXteStreamAggregate::QScanGet+8e)
0x0065aed7 Module(sqlservr+25aed7) (CQScanHashMatch::CreateBailoutQscan+18)
0x005507f2 Module(sqlservr+1507f2) (CQScanHashMatch::Iterate+397)
0x0042af33 Module(sqlservr+2af33) (CQScanHashMatch::GetRow+b0)
0x00428915 Module(sqlservr+28915) (CQScanTop::GetRow+94)
0x0048d5e2 Module(sqlservr+8d5e2) (CQScanUpdate::GetRow+a8)
0x00415a26 Module(sqlservr+15a26) (CQueryScan::GetRow+10)
0x004152b5 Module(sqlservr+152b5) (CStmtQuery::FExecuteQuery+441)
0x0040d45e Module(sqlservr+d45e) (CStmtDML::XretExecuteNormal+1a8)
0x0060aa72 Module(sqlservr+20aa72) (CStmtSelectInto::XretExecute+149)
0x004145f1 Module(sqlservr+145f1) (CMsqlExecContext::ExecuteStmts+11a)
0x0041409f Module(sqlservr+1409f) (CMsqlExecContext::Execute+16a)
				
The preceding AV may not occur every time the query runs because the error depends on the system resources available at the time the query runs.

CAUSE

During conversion from a hash join to a merge join, the precompiled plan is not valid any more and the plan involving the merge join was not remapped.

WORKAROUND

To work around this behavior, you can use any of the following:
  • Rerun the query when there are fewer users or a lesser load. Under less load, there may be more memory to accommodate the data set involved in the hash join plan.

  • Reduce the size of the data in the operation, use a smaller batch size, views, and so forth. This avoids the AV if the data size is decreased enough to fit in the available memory.

  • Identify the query that uses the hash join plan by using the SHOWPLAN option and use optimizer hints to force a join other than a hash join. You can also use a merge or loop join to avoid this problem. Refer to SQL Server Books Online for details about using Optimizer Hints.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

Properties

Article ID: 275304 - Last Review: February 28, 2014 - Revision: 5.0
APPLIES TO
  • Microsoft SQL Server 7.0 Service Pack 2
Keywords: 
kbnosurvey kbarchive kbbug kbfix kbqfe KB275304

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com