FIX: Sampling Inconsistency Results in Access Violation or Slow-Running Queries (251094)

The information in this article applies to:

Microsoft SQL Server 7.0

This article was previously published under Q251094

BUG #: 57007 (SQLBUG_70)

SYMPTOMS

When updating existing statistics using sampling, two things may happen:

An Access Violation occurs during Query Optimization and the user is unable to retrieve the contents of that table using regular SELECT statements that use this index.
In cases where an Access Violation does not occur, it is possible that the user works with sub-optimal plans.

CAUSE

When updating existing statistics for non-unique clustered indexes on variable-length columns using sampling, either default or explicit, with a sample size of less than 100%, statistics may become inconsistent. This problem may also occur if SQL Server auto-updates the statistics on this index because the Auto Update Statistics database option is set for that database.

WORKAROUND

The workaround is to implement one or more of the following:

Clear the Auto update statistics database option.
Update statistics for this index using the FULL SCAN option.
Drop and recreate the index.
Do not update statistics for this index.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

For example, consider the following table with a clustered index:

create table t (c1 varchar(15), c2 varchar(25))
go
create clustered index t_c on t(c1, c2)
go

The statement that causes inconsistent data to be generated is:

UPDATE STATISTICS t(t_c) with sample 50 percent
go

REFERENCES

For information on how to disable automatic updating of statistics for a specific index, see the Books Online topic sp_autostats (T-SQL).

Modification Type:	Major	Last Reviewed:	3/14/2006
Keywords:	kbBug kbfix KB251094