During query compilation, if the optimiser decides that cardinality estimates would be improved by having statistical information about the data in a column it will attempt to create the necessary column statistic during this process.The column statistic creation is done over a random sample of the pages to keep compilation time to a minimum.
Look at the rows_sampled and last_updated column, you will see the sample row number and last_updated column time is within few seconds of update in primary. I ran an extended event (XE) trace on my secondary replica to capture the auto update stats event but nothing was logged. CREATE EVENT SESSION [Auto Update Stats] ON SERVER ADD EVENT sqlserver.auto_stats( ACTION(sqlserver.client_app_name,sqlserver.server_principal_name) WHERE ( [package0].[equal_uint64]([database_id],(25)) ) ) WITH ( max_memory=4096 kb, event_retention_mode=allow_single_event_loss, max_dispatch_latency=30 seconds, max_event_size=0 kb, memory_partition_mode=none, track_causality=OFF, startup_state=OFF ) GO After opening a case with Microsoft I found a solution by rebuilding the Clustered Index or Heap.
Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.
3) Rebuilding an index, for example by using the ALTER INDEX …
I was informed by Microsoft engineers that at least 2 other customers saw similar behavior.
Microsoft is investigating further to find the root cause and permanent solution. There is a connect item about this behavior with a different twist.