0

I know SQL Server update stats when 20% of data is changed in a table. It keeps tracks of it by using modification counter which we can see in [modification_counter] column of sys.stats table.

But I can see for one of my key table in the database that stats are getting updated much before they reach 20% data modification threshold. Just wanted to know is there any thing else which will force SQL Server to update stats automatically.

Why I am worried is that when SQL Server update stats automatically, it is using 5% sampling rate which might cause creation of bad / inaccurate execution plan. (need confirmation on that is totally different issue).

So I want to know why SQL Server is so often updating stats?

Improve this question
2

1 Answer 1

Reset to default
1

For the large tables, we require to update 20% of a row to auto-update the statistics. For example, a table with 1 million rows requires 200,000 rows updates. It might not be suitable for the query optimizer to generate an efficient execution plan.

SQL Server 2016 onwards, it uses dynamic statistics update threshold, and it adjusts automatically according to the number of rows in the table.

Threshold = √((1000)*Current table cardinality)

For example, a table with one million rows we can use the formula to calculate the number of updates after which SQL Server will automatically update statistics.

Threshold = √(1000*1000000) = 31622

SQL Server updates the statistics after the approx. 31622 modifications in the object.

Note: the database compatibility level should be 130 or above to use this dynamic threshold statistics calculations.

Regarding the sampling rate, yes - automatic statistics updates do not do full scan, they do % sampling which might not always be optimal... you have to implement your own solution that will do more frequent statistics update with fullscan. Check Ola Hallengren's maintenance scripts

Improve this answer
2
  • we have 1 table which is most important table and is heavily being modified. So approximately in every 1 hour, few stats of those tables are getting updated automatically. So wanted to know 1. how to determine the impact of having these stats in place with default sampling rate? 2. What is the impact if we update these specific stats every 1 hour, including production hours (or even all stats on the table)? 3. Is there any impact of already cached execution plans using that table if we update the stats manually with full scan? will there be blocking? FYI - SQL 2016 and table is partitioned.
    – sachin-SQLServernewbiee
    Commented Feb 4, 2020 at 13:38
  • 1. not sure if anyone can answer this question :) 2. try to run update statistics on that table with full scan - does it run fast ? if it runs fast (1 to few seconds), then impact is low. If it runs long time, that means all queries that depend on that column, will wait until update stats completes. 3. plans are getting recompiled after stats are updated
    – Aleksey Vitsko
    Commented Feb 4, 2020 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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