Sql-server – Auto Update Statistics threshold SQL Server 2016

performancesql serverstatistics

since SQL Server 2016 the default auto statistics update threshold changed in that way that it uses a smaller threshold depending on the table size. In our application we periodically update statistics with fullscan. With this new behavior the auto update statistics runs after a smaller modification rate and uses only small sample rate. Until our maintenance job will update statistics again with fullscan next time, the auto statistics update runs and resets the ‘fullscan’ results. This could cause a performance problem in our application.
Is there any way to use the old behavior with auto statistics update without going back to a lower compatibility level?

Best Answer

Until our maintenance job will update statistics again with fullscan next time, the auto statistics update runs and resets the ‘fullscan’ results

This is the side effect of updating statistics with fullscan on a weekly basis, it becomes more of a guessing game when your statistics are going to be automatically updated.

As mentioned in the comments and since you hopefully are on a patch level of SQL Server 2016 SP1 CU4 or above, setting the persisted sample rate higher can work for the tables that you are updating WITH FULLSCAN on a weekly basis.

You could try working with a lower sample rate than fullscan but still higher than the default sample rate for these tables to reduce the time it takes to update.

Another setting that can help due to your auto update statistics taking a longer time when a higher sample rate is used is to enable ASYNC_STAT_UPDATES. Enabling asynchronous stat updates makes your queries not wait for your statistic updates when enough changes have been made, causing them to use the old statistics which have a high sample rate.

The caveat is that the lowest level possible to enable asynchronous statistic updates is for your entire database. It would be wise to test this setting in development.

UPDATE STATISTICS dbo.tablename WITH SAMPLE 1 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
UPDATE STATISTICS dbo.tablename WITH SAMPLE 5 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
UPDATE STATISTICS dbo.tablename WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

To enable asynchronous statistic updates:

ALTER DATABASE [DatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;

Remember that when you truncate the table the sample rate is no longer persisted and when new indexes and their corresponding statistics are created that they will have the default sample rate.

Another idea would be to update your statistics more often for the tables that have more changes if your server has the resources to do so.

Related Question