I have a table that is modified regularly. Due to these frequent modifications, statistics update on it very frequently. To my surprise, I even see queries running on a SQL Server 2022 readable secondary AG that are trying to update statistics. I have been able to distinguish two types of statistics updates.
I believe the first type to be the replica updating stats in response to being queried. In other words, it's an automatic stats update just like on the primary. I know this because EXEC sp_WhoIsActive @get_plans = 2 on the replica shows me execution plans that are clearly updating statistics (e.g. they have the blatant STATMAN stuff in their XML), despite the query text just being a normal user-written query of the replica. They also appear in the auto_stats Extended Event, usually as completing successfully.
With sp_WhoIsActive @show_system_spids = 1, @get_locks = 1, I can also see a second type of statistics update. This second type is the redo thread trying to take an Sch-M lock on a resource_type of METADATA.STATS. The blocked process report confirms this and gives me both the object_id and stats_id of the object in question.
My question is this: if I see a statistics update on a readable AG replica, is it always one of the two cases described above?
All of the documentation that I've read, particularly this, suggests that my first type can only be the creation of temporary statistics on the readable replica. I have five objections to this idea, listed below, so I am lead to believe that the two cases that I have listed above cannot be all of the possibly ways that a statistics update can happen on an AG replica. My objections are these:
- The workload on the secondary is much less than the primary despite the read-only queries being exactly the same, so there is no good reason why the statistics on the primary should be stale.
- I have never seen statistics from this table in the output of
SELECT * FROM [MyDb].sys.stats WHERE is_temporary = 1.tempdbdoesn't have it either. - The auto_stats Extended Event tells me which statistic it is updating. The suffix
_readonly_database_statisticnever occurs. I either get the names of real statistics or blanks (for failures). - The statistics create too frequently. On a bad day, auto_stats reports them being made every 15 minutes. Temporary statistics surely can't be that short lived?
- This blog suggests that there is a distinction between temporary statistics explicitly created to help a readable replica and statistics that already exist on the primary but are stale when the replica is queried. However, the documentation I linked earlier strongly suggests that all temporary statistics have the
_readonly_database_statisticsuffix that I am not seeing.