What are the scenarios to update SQL Server statistics?
I know SQL Server updates stats when 20% of data is changed in a table. It keeps track of it by using the modification counter which we can see in the [modification_counter] column of sys. stats table. But I can see for one of my key tables in the database that stats are getting updated much before they reach the 20% data modification threshold. Just wanted to know is there anything else that 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 the creation of a 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?
For the large tables, we need to update 20% of a row to auto-update the SQL Server 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, uses a dynamic statistics update threshold, and it adjusts automatically according to the number of rows in the table. Threshold = √((1000)*Current table cardinality) For example, in 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) = 31622SQL Server updates the statistics after the approx. 31622 modifications in the object. Note: the database compatibility level should be 130 or above to utilize these dynamic threshold statistics calculations efficiently. Regarding the sampling rate, I would suggest yes the automatic statistics updates do not do a full scan, they do % sampling which might not always be optimal. You have to implement your own solution that will do more frequent statistics updates with a full scan. Check Ola Hallengren's maintenance scripts.