Restarting server improves Mirror performance

Question

Hi Everyone,

This is second instance when we faced application load delays (around 5-6 minutes... which normally takes around 30 seconds) which is pointing to a SQL Server mirrored database instance. 

1. We have same set of application deployed in different reasons. In region 1, database is not mirrored and we have never encountered slow application load times.

2. In region 2, database is mirrored and this is the second time we faced this issue (in span of around 3 months).

3. First time we did most of the investigation related to PL SQL performance on the database but could not find anything relevant through the Performance trace. The problem was resolved when we simply restarted the principle and mirrored database servers.

This time, we did some further analysis and only thing which looks strange is the Transaction Log size. The DB file size is 100MB and transaction log size is 3363MB. We also reviewed the mirroring performance review (through the mirroring monitor) and found that average Send Rate of around 2-3 KB/PS and with and analysis showed no long running/blocked/uncommitted transactions. 

Questions,

1. What are the possible causes which improved the performance when the server was restarted. We did FREEPROCCACHE and DROPCLEANBUFFERS but this did not help.

2. What are the various ways to get to the root cause of the problem?

3. With profiler trace, we noticed many AuditLogout and sp_reset_connection (the queries exectured in betwee actually took few milliseconds) with AuditLogOut taking as much as 162293000 milliseconds and there are many such instances of this while the application was being loaded.. What does this indicate and is this normal?

Many Thanks, Vinay

---

Regards, Vinay

Answer 1

Hi Vinay,

Shrinking the database improves the performance

Check the network connectivity of the server

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 2

There are some things I could think about...

1) When you restart the server it starts the mirroring in Resync-Mode, which means that the principal does not wait for the mirror to commit before committing back to the client. Once it is back in sync it will again. (Assuming that you use sync mode...)

2) There is a limitation in the log buffer pool when reapplying on the secondary. But to hit this you would need to have extreme transaction rates...

3) Of course you can also run into a thread-problem if you are based on standard edition.

I would have a look at the Waitstats when the problem occurs. (Both on the principal and mirror...) They should tell you what your system is waiting for.

Lucifer