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

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

On This Page

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

Properties

Article ID: 251094 - Last Review: October 20, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB251094

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