Article ID: 275304
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Service Pack 2
This article was previously published under Q275304
BUG #: 58130 (SQLBUG_70)
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.
Additional query words: regression sp2 servpack hash bailout remap valref
Keywords: kbbug kbfix kbqfe KB275304
