You are currently offline, waiting for your internet to reconnect

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

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)
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.
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.
You might be able to deploy an index hint.
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.
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.

Article ID: 276067 - Last Review: 10/21/2013 02:29:15 - Revision: 2.0

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix kbqfe KB276067