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: )

In this article by David Barbarin similar behavior was also observed in SQL Server 2014 SP2.

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.