SQL Server Fullscan Statistics Being Overwritten with Sample Statistics
March 3, 2017
I observed this behavior only in the 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, within 10~20 seconds some of the statistics on the same table are getting an update on secondary with a sample percent of rows. Meaning my best statistics are being overwritten with good (full vs sample) statistics. On the primary node, once I run “Update statistics Tablename with FULLSCAN”. I see the status of the following statistics.
After 10~20 seconds of updating statistics in a primary node if I check the status of the same on my secondary nodes, I see FULLSCAN statistics are 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 the 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, the 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.