FIX: Duplicate Distribution Steps May Lead to Bad Plan Selection for >= or =< Predicates

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

SYMPTOMS

Under rare conditions, a symptom of having duplicate histogram steps for greater than or less than operations is that SQL Server selects a less than optimal query plan. The plan generally avoids the use of a valid index and uses a table or index scan to retrieve the data.

CAUSE

When SQL Server calculates the difference between the histogram steps a divide by zero error may occur. The divide by zero exception is handled by SQL Server but may result in incorrect cardinality estimation.

WORKAROUND

You might be able to deploy an index hint.

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.

MORE INFORMATION

This problem exists on all builds of SQL Server 7.0. However, due to other optimizer changes and enhancements the problem occurs more frequently with SQL Server 7.0 Service Pack 2 (SP2).

  1. To determine if you are encountering this bug, use SQL Server Query Analyzer to test the query in question. Use either SET STATISTICS PROFILE ON or SET SHOWPLAN_ALL ON and run the query. The resulting plan output must contain a BETWEEN, greater than or less than operator. If these operators do not exist, the query does not encounter the bug.

  2. Next, use DBCC SHOW_STATISTICS for the objects in question to see if there are any statistical indexes or physical indexes that contain duplicate step values.

Properties

Article ID: 276067 - Last Review: October 21, 2013 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix kbqfe KB276067

Give Feedback

 

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