Microsoft KB Archive/299518

= PRB: Update Statistics Statement Without Fullscan Option Returns Wrong Density Value =

Article ID: 299518

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q299518



SYMPTOMS
When you run an update statistics statement without the fullscan option on SQL Server 2000, DBCC SHOW_STATISTICS output shows a Density column value larger than 1. The Density column from this output shows an incorrect density value:

Updated             Rows   Rows Sampled   Steps  Density -- -  -  May 8 2001 6:26PM   670000  23833           200   13.264631

When you run an update statistics statement with the fullscan option on SQL Server 2000, DBCC SHOW_STATISTICS output shows the correct Density column value; for example:

Updated             Rows   Rows Sampled   Steps  Density -- -  -  May 8 2001 6:26PM   670000  670000          200   1.2838818E-3

When you run update statistics with or without the fullscan option on SQL Server 7.0, DBCC SHOW_STATISTICS shows the correct density value.



CAUSE
The SQL Server 7.0 query optimizer uses the density reported by DBCC SHOW_STATISTICS statement. Basically, it is a measure of the average number of duplicates for distinct values read during statistics gathering.

On SQL Server 2000, the Density attribute is deprecated and is no longer used in query optimization. Because density information is tracked on a per histogram step basis, this attribute is no longer relevant.



WORKAROUND
To obtain accurate statistics, use update statistics with the fullscan option.



MORE INFORMATION
The inaccurate statistics reported by SQL Server 2000 without the fullscan option should not affect the SQL Server 2000 query optimizer.

Additional query words: inaccurate statistics update without fullscan

Keywords: kbprb KB299518

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.