Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We did different kinds of basic Tests and AlwaysOn passed in many tests. We finally did a heavy write test for AlwaysOn and it gave surprising results.

The actual Test details are here, the goal is to see if AlwaysOn availability group can accommodate a high write load.

  1. I have two VMs each running on 8 cores and 17GB of RAM allocated to SQL Server.

  2. We wrote a script to generate reasonably good write I/O (in 20 threads).

  3. Each thread basically inserts 24 MB of data into a table and deletes in an endless loop.

Within 15 minutes of the test run, the estimate for recovery time on automatic failover reached 12 minutes which is pretty bad. We tried a failover to confirm if it really takes 12 minutes, it took around 5 minutes which is still too high. Also if we continue the test for three hours recovery ETA is almost 3 hours and it is taking hours to recover on failover (clearly this should not have been the case if it was a cluster failover, because all transactions are committed transactions).

So couple of things..

It is very clear that the synchronous secondary replica is not able to keep-up with the load primary is generating (even though both machines are of same configuration). And the side effect of this is the log on primary will keep on growing (even we take log backups it can't truncate the log).

We know that the secondary uses one thread per every 4 CPU-cores for doing redo, which looks like a clear limitation. If the primary is running 100 threads to generate load, the secondary can't use that many threads anyway.

Additionally, the primary does all its transactions in-memory and leaves the actual data file writes to checkpoints. However, it seems that secondary has to read all transactions from the physical log drive and redo. The log pool on secondary which is supposed to make this process faster? But it is not doing a good job in this scenario.

Finally questions to AlwaysOn experts:

  1. Does anyone know how the redo process exactly happens?
    • is it cached ?
    • is the buffer pool involved at all ?
  2. Does the secondary use log pool to cache the log entries for redo?

  3. What is the size of log pool? Can it grow up to the max memory available?

  4. When redo happens, the redo thread reads the pages to buffer pool and maintains them as if it is a normal transaction?

  5. If secondary is not able to keep-up how come AlwaysOn articles say recovery time is a few seconds?

This makes the high availability part of Availability Groups questionable, since these recovery times are unsustainable.

[Edit by asker] Clarifications, since people seem to think this is answered, the transactions at primary are indeed acknowledged (i.e. the log is hardened), since the state of the secondary is always "synchronized". So it is not a problem with the hardening of the log. So it is the redo process that is taking forever upon failover. This means that AlwaysOn will always take longer to recover than without it for any load that generates log > redo threads capacity.

share|improve this question
Do you have wait stats from the secondary and a side-by-size IO stats (OS physical, SQL) between primary and replica(s)? – Remus Rusanu Dec 19 '12 at 8:04
I do, there are not that many HADR_COMMIT_SYNC waits (anyway the redo queue only builds after the log is hardened), and I have mentioned that the transactions are committed on the primary in time, so there is no question of log hardening not occurring.. – WrinkleFree Dec 19 '12 at 9:46
Do you use full recovery model? how large are you databases? how often do you backups of transaction log? – Aen Sidhe Dec 31 '12 at 10:48
i attended a sql server 2016 early-adopters panel session at PASS Summit this past year where they indicated that there was an implicit cap of 45 MB (yes, megaBYTES) per second on transmission between replicas due to a section of the codebase that encrypts the transmission of your log to other replicas. they said that 2016 removed this behavior. perhaps 2016 will be able to keep up with your test workload. – swasheck Jan 20 at 21:40

It is very clear that the synchronous secondary replica is not able to keep-up with the load primary is generating (even though both machines are of same configuration). And the side effect of this is the log on primary will keep on growing (even we take log backups it can't truncate the log)

In synchronous mirroring/alwayson the secondary must acknowledge that it hardened (written to disk) the log before the primary's commit is allowed to continue. The primary is then free to truncate/reuse it's own log as it needs. If you cannot truncate the primary it means the secondary is not synchronized. This would point toward a problem with the ability to ship the log to the secondary and write it to disk. The two obvious bottlenecks would be the network speed and the secondary's log file storage. Both are easy to measure and diagnose, as they're straight forward USE (utilization, saturation, errors) OS level metrics.

Note that I never mentioned recovery (secondary's redo). If the problem is indeed that the secondary is not able to synchronize then redo is not playing any real role here.

share|improve this answer
Yes, I had the same thought, however for some reason it does not allow me to truncate the log beyond the last redone lsn, instead of the last hardened lsn (I have verified this, when the redo queue is large, the log does not truncate (does so at a rate equal to the redo roughly)), I have not come up with a suitable theory (maybe because when we truncate the log, the log size changes, so it is not possible to have differently sized logs on the primary and secondary) – WrinkleFree Dec 19 '12 at 9:42
is the state 'synchronized' or 'synchronizing' ? – Remus Rusanu Dec 19 '12 at 9:54
Its always synchronized.. – WrinkleFree Dec 19 '12 at 10:32
@Rohan: Do not edit answers for clarifications. You can edit your question and add more relevant stuff any time. – ypercubeᵀᴹ Dec 28 '12 at 8:47
@ypercube: sorry about that, thanks for the edit :) – WrinkleFree Dec 28 '12 at 11:03

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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