It appears that this process has completed as of 11:10 pm MST (2:10 am AST.)
Here are my observations and recommendations.
1.) The session thread was doing quite a bit of sleeping. This normally
occurs when control is handed off to another thread. The session information did not show any other sub-threads for this session. It appears that the thread was waiting for the commit response from the secondary.
Currently, the two servers are configured for synchronous mode. That means that the transaction will not commit on the primary until it gets the commit response back from the secondary. With any network latency, this can be quite slow during bulk processes. It is more apt to be up to date though.
2.) I saw the session wait for a logical log buffer quite often. Not
near as it was sleeping (which was 90% of the time), but it happened often enough to be looked at.
Change DRINTERVAL from -1 to 0. This will disable synchronous mode with HDR and will go to asynchronous. This is much better for performance as the primary will no longer have to wait for the secondary server to acknowledge to transaction before moving on. Checkpoints are still done synchronously.
Increase LOGBUFF setting from 32 to 64. This will increase the logical log buffers and prevent processes from waiting for them as often as they did.
1 month ago