0

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.

0

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?

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 run ALTER 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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