3

We have two AlwaysOn availability replicas - secondary readable. We do have an incremental statistics on a large table. When a failover happens, SQL Server tries to update statistics on that table with sample = 100%.
I've read that incremental statistics is not supported on AlwaysOn readable secondary databases.
So is it how SQL Server supposed to react on this situation?

We cannot allow this to happen, so our temporary solution is to remove those _WA_Sys_... statistics that SQL Server tries to update, then some of those statistics are still recreated by SQL Server but with a lot smaller sample rate.

Is anyone familiar with this issue and what can be done to prevent this kind of behavior?

  • Do you have auto create turned off for your partitioned table? Assume you dont, since auto stats all start with WA. Likely, you will need to do so, given the limitations of how auto stats work on secondaries (stored in tempdb so doesnt span restarts), you will likely find it easier manage all stats on it alone. – Andrew Loree Dec 21 '15 at 3:04
  • Are your previous read-intent queries hitting your now primary dbs after failover? Quite possible they would cause the creation of the new auto-stats on the table in question. – Andrew Loree Dec 21 '15 at 3:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.