PRB: Update Statistics Statement Without Fullscan Option Returns Wrong Density Value (299518)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
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.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbprb KB299518 |
|---|
|