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?

Improve this question
2
  • 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
    Commented Dec 21, 2015 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
    Commented Dec 21, 2015 at 3:14

0

Reset to default

Your Answer

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