Operational Defect Database
BugZero found this defect 1308 days ago.
Data sources
All data on this page is proprietary to BugZero® or gathered from public sources
Microsoft SQL Server | 9704947
KB4039284 - Enhancement: New keyword is added to CREATE and UPDATE STATISTICS statements to persist sampling rate for future statistics updates in SQL Server - Microsoft Support
Last update date:
7/24/2023
Affected products:
SQL Server 2016 Developer - duplicate (do not use)
SQL Server 2016 Enterprise - duplicate (do not use)
SQL Server 2016 Enterprise Core - duplicate (do not use)
SQL Server 2016 Standard - duplicate (do not use)
SQL Server 2016 Service Pack 1
SQL Server 2017 on Windows (all editions)
SQL Server 2017 on Linux (all editions)
Affected releases:
build lower than 14.0.3006.16
Fixed releases:
14.0.3006.16
Description:
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
