Enhancement: New keyword is added to CREATE and UPDATE STATISTICS statements to persist sampling rate for future statistics updates in SQL Server



When Microsoft SQL Server creates or updates statistics, if a sampling rate isn't manually specified, SQL Server will calculate a default sampling rate. Depending on the real distribution of data in the underlying table, the default sampling rate may not accurately represent the data distribution. This may cause degradation of query plan efficiency.

To improve this scenario, a database administrator can choose to manually update statistics by using a fixed sampling rate that can better represent the distribution of data. However, a subsequent Automatic Update Statistics operation will reset back to the default sampling rate, and possibly reintroduce degradation of query plan efficiency.

This enhancement allows the fixed sampling rate to be persisted across subsequent manual or automatic update statistics operations.


This update introduces a new keyword PERSIST_SAMPLE_PERCENT = { ON | OFF } that's an extension to the existing CREATE/UPDATE STATISTICS Transact-SQL. The keyword will indicate SQL Server to lock the sampling rate (provided when it's created or updated) for future use.

For more information about statistics, see Create Statistics.

The feature is available in the following cumulative updates for SQL Server:

Cumulative Update 1 for SQL Server 2017

Cumulative Update 4 for SQL Server 2016 SP1




Learn about the terminology that Microsoft uses to describe software updates.