2

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?

  • 3
    "it uses a smaller sample rate" - That is not true, what has changed is the amount of rows modified required to trigger a statistics update, not the sample rate. It seems like what you really want may be PERSIST_SAMPLE_PERCENT to lock the sample rate in for any stats updates. – LowlyDBA Jun 29 at 13:44
  • well... if you are doing periodically updates with the app... do you really need to "auto-update" to be turned on ? – Dominique Boucher Jun 29 at 15:52
  • @LowlyDBA thanks for your remark - i made a correction in my post – Thomas Jun 30 at 7:56
  • @Dominique Boucher : I was thinking about it, but I wasn't sure if it was a good idea – Thomas Jun 30 at 8:00
  • Whether or not to disable auto update for stats requires a lot of planning. For most people, most of the time, it's doing the job well enough. If you're managing a very small number of databases, you could probably build scripts & schedules to do it for you. Otherwise, most people leave it enabled and the augment with additional scripts. – Grant Fritchey Jun 30 at 10:53
1

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.

| improve this answer | |
  • is there a way to prevent building auto statistics update on certain Tables / Columns? – Thomas Jul 6 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.