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

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core More

Summary


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.

Resolution


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

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References


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