We have MS SQL Server 2014 Enterprise Edition with an Availability Group with Readable Secondary. Our application reads data only from Secondary.
As far as I know, auto update statistics starts when reads (SELECT and maybe something else) performed. But our reads execute only on Secondary. Does it fire Auto Update Stats on Primary?
Thanks in advance.
P.S. And please, excuse my poor English.
Best Answer
No it does not fire any auto update statistics on the primary.
However
In Read only databases, regular permanent statistics can be leveraged to satisfy query plans. If sql server needs new statistics for the read only db or these permanent statistics in the database have become stale, then temporary statistics can be created / updated. These statistics reside in TempDB and are managed by SQL Server (you can only drop them).
There are two kinds of temporary statistics, these that get created due to missing statistics and those that get 'updated'.
Reducing the creation of temporary statistics
The creation of temporary statistics can be removed by adding the statistics manually on the primary database, generating the estimated execution plans of the reporting queries (also on the primary instance/db) or running the queries.
Reducing the updates of permanent to temporary statistics
Permanent statistics can be updated and 'transformed' to temporary statistics on a read only database.
The temporary stat updates can be resolved by updating your statistics on the primary db more often.
As a side note, on
Standby / Read Only(log shipping) databases you would have the option to runALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF;to disable the temp stat updates. This is not possible for database part of an Always On Availability Group.These stat creates / updates can become expensive depending on the amount of data and sample rates.
Parts of this answer have been taken from a previous post of mine