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
  • 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? – Sean Gallardy Dec 14 '16 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. – SqlWorldWide Dec 15 '16 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? – Sean Gallardy Dec 15 '16 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. – SqlWorldWide Dec 15 '16 at 17:08
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, privacy policy and cookie policy

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