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

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.

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).

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.
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.