PRB: Auto Created Statistics Are Sampled by Using a Specified Sampling Rate (819953)

SYMPTOMS

If you update the statistics in a table and specify a sampling rate that is less than 100 percent, the index statistics are updated by using a 100 percent sample, but the auto created statistics are updated by using the specified sampling rate. You may experience this problem when you run the following Transact-SQL statement:

UPDATE STATISTICS <TableName> WITH all, SAMPLE 10 PERCENT
UPDATE STATISTICS <TableName> SAMPLE 25 PERCENT

CAUSE

If the SQL Server optimizer determines that sampling the rows for the indexes at 100 percent instead of at the requested rate may not add significant costs, it performs a full sampling of the rows in the table. This full sampling helps the SQL Server optimizer to obtain more accurate information about the distribution of data. However, the auto created statistics in the table are sampled by using the specified sampling rate.

WORKAROUND

To work around this problem, make sure that the auto created statistics are sampled for all the rows in the table. To do so, use one of the following commands:

UPDATE STATISTICS <TableName> WITH COLUMNS, SAMPLE 100 PERCENT

-or-

UPDATE  STATISTICS <TableName> WITH FULLSCAN

MORE INFORMATION

You run the following Transact-SQL statement to update the statistics in the table:

UPDATE STATISTICS <TableName> WITH all, SAMPLE 10 PERCENT

After you run the previous statement, you use the following command to verify the number of rows that are sampled to update the statistics:

DBCC SHOW_STATISTICS <TableName>, <Target>

For the indexes, the number of rows that are sampled is equal to the number of rows in the table. In other words, the sampling rate is 100 percent. However, for the auto created statistics in the same table, the specified sampling rate is used to determine the number of rows that are sampled.

Note The naming convention for the auto created statistics is _wa_sys_<column_name>_<xxxx> (where <xxxx> is the object_id of the table in hexadecimal format).

REFERENCES

For more information about the UPDATE STATISTICS statement, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_1mpf.asp

For more information about the DBCC SHOW_STATISTICS statement, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_6jlf.asp