ALTER STATISTICS

ALTER STATISTICS — change the definition of table statistics

Syntax

ALTER STATISTICS [schema_name.]statistics_name
    WITH ( statistics_option [, ...] )

Where statistics_option can be one or more of the following:

FULLSCAN|SAMPLE n {PERCENT|ROWS}
RESOLUTION n
AUTODROP {ON|OFF}

n must be an integer value.

Description

Use ALTER STATISTICS to change the definition of table statistics. Any statistics options that are not specified will not be altered. Table statistics that have been already computed will be unaffected until they are recomputed.

Parameters

statistics_name: Name (optionally schema-qualified) of the statistics to alter.
FULLSCAN|SAMPLE n {PERCENT|ROWS}: The statistics are computed by sampling the table. The sampling rate can be controlled by specifying either a number of rows, or a percentage of the number of rows in the table. FULLSCAN requests that all rows in the table are scanned during the computation of the statistics. FULLSCAN and SAMPLE 100 PERCENT produce the same result.
Default is SAMPLE 10000 ROWS.
RESOLUTION n: This option allows you to alter the RESOLUTION of the histogram, that is, the number of "buckets" used in the histogram. Increasing this number will improve the precision of statistics available to the query optimizer, which will then allow the optimizer to make better decisions, potentially leading to an improvement in query performance. The downside of increasing the number of buckets is that the statistics will consume more memory and require additional compuation.
Valid RESOLUTION values for statistics are 10 - 4096.
Default is RESOLUTION 256.
AUTODROP {ON|OFF}: When ON schema changes are not blocked by the presence of statistics on the table.
Default is OFF.

Examples

Example 1: Change the sampling rate only, other options are not altered

ALTER STATISTICS s1 WITH (FULLSCAN);
ALTER STATISTICS s1 WITH (SAMPLE 20 PERCENT);
ALTER STATISTICS s1 WITH (SAMPLE 5000 ROWS);

Example 2: Change a single statistics option only, the sampling rate and unspecified options are not altered

ALTER STATISTICS s1 WITH (RESOLUTION 512);
ALTER STATISTICS s1 WITH (AUTODROP ON);

Example 3: Change the sampling rate and the options

ALTER STATISTICS s1 WITH (SAMPLE 20 PERCENT, RESOLUTION 512, AUTODROP ON);