2

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 full scan, with 10~20 seconds some of the statistics on the same table are getting update on secondary with a sample. I ran extended event trace to capture auto update stats on secondary node but nothing was logged. On primary node once I run this: Update statistics Tablename with fullscan I see this about my stats status enter image description here

When I query for the stats on secondary I get this. Look at the rows_sampled and last_updated column. enter image description here

I also ran an extended event trace to capture the auto update stats but nothing was logged. Here is the defination 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
4
  • No matter what I do, I can't get this to repro. Do you have an exact list of steps that you can repro this every time? Commented Dec 14, 2016 at 20:53
  • Yes I can. Update statistics on table1 with full scan. check statistics on both node. Running XE trace to capture auto update stats on Secondary node. Commented Dec 15, 2016 at 13:19
  • That's exactly what I attempted (except for the XE) and could not reproduce. Does this happen on all of your 2012 AGs? Commented Dec 15, 2016 at 14:44
  • Not sure. I was not explicitly looking for this but found out when one of the query was taking longer on secondary node. I even tried to reproduce this in another table of same database but could not. Commented Dec 15, 2016 at 17:08

1 Answer 1

0

I did open a case with Microsoft Support. I was notified similar behavior was reported by other customers. For now the solution is to rebuild the index. Microsoft is investigating further about the root cause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.