- Rowmodctr or modification_counter does not track the “percentage of the table that has been modified,” a common misconception. They really track the “number of times a DML operation affected key values in the index.”
- The counters know nothing about whether the modifications were to one row repeatedly or to many rows. They are not perfect – and they can’t exactly tell us “how much” of the table is changed.
- Auto update stats is triggered by query optimization – that is, the optimizer needs to take a “new” query, where the statistics used for optimization have been invalidated, and it will request new statistics, assuming auto update stats is on, which are collected just before this new query is optimized. On a real, running system this probably happens very quickly, but some cases exist where a plan might be out in cache and no optimization happens to make the stats update.