One of the questions I was asked recently while speaking at the Los Angeles SQL Server Professionals Group and the Orange County SQL Server User Group (*) was regarding the order in which jobs like rebuilding indexes or updating statistics should be executed as part of the database maintenance activities.In general you should consider the following important points, focusing in the fact that there are two kinds of statistics: index and column statistics.Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.
On primary node once I run “Update statistics Tablename with fullscan” . After 10~20 seconds of updating statistics in primary node if I check the status of the same on my secondary nodes, I see fullscan statistics is replaced by sample statistics.
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.
In my case Rows Modified column value was zero but in the connect item it is mentioned “modification counter is also strongly incremented (on the secondary only) “.
I would like to hear if you encounter this issue and how you are dealing with it.
I was informed by Microsoft engineers that at least 2 other customers saw similar behavior.