SQL Server Fullscan Statistics Being Overwritten with Sample Statistics
I observed this behavior only in following version of SQL Server while using Always On Availability Group feature.
Version: Microsoft SQL Server 2012 (SP3) (KB3072779) – 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )
Once I update my statistics with fullscan, with in 10~20 seconds some of the statistics on the same table are getting update on secondary with a sample pecent of rows. Meaning my best statistics are being overwritten with good (full vs sample) statistics. On primary node once I run “Update statistics Tablename with fullscan” . I see following about statistics status.
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. RowsModified column still showing zero records.
I ran an extended event (XE) trace on my secondary replica to capture the auto update stats event but nothing was logged. Here is the definition of my XE trace.
CREATE EVENT SESSION [AutoUpdateStats] 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. 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. If I hear back I will update this post.
There is a connect item about this behavior with a different twist. In my case RowsModified 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.