P

Hi Experts,

After performing some tests on my setup I recorded very large performance degradation between simple setup and mirrored setup (High Availability �C with witness). In some inserts tests (100K in a loop) it took up to 400% longer to complete for mirror setup compared to the simple one (40 seconds compared to 170 seconds).Is this performance hit normal? Is there something that can be done to make it perform better?

The PCs are connected using a 100MB switch and I'm using SQL Server Developer Edition.

Thanks,

Avi

Hi Avi,

The tests that you are running are bad scenario for high-availability mirroring. Each insert statement in the loop practically commits a single-statement transaction. This causes log to be sent to disk and mirror and waits for the mirror to harden that log. That way you are directly adding to the per-statement latency.

Here are a few things you can try:

- Don't run all inserts sequentially on a single connection. Rather, distribute them among multiple connections. That way you are increasing the chance that while one transaction is committing and thus waiting for the mirror to harden the log, others may run in parallel, thus amortising the wait time.

- If at all possible, make your transactions bigger (do several inserts in a single transaction). That way each log flush will carry several transactions.

- If at all possible, use high-performance mirroring mode (here you have the risk of having your log growing, though).

Hope this helps.

Thanks,

Kaloian.

|||

For better estimation of performance impact of database mirroring, your tests should be as close to the actual application as possible. For discussion on database mirroring performance, take a look at the paper: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

|||

PCs?

Would you please give more details on the hardware of the mirrored servers?

|||

Thank you all for the replys.

I use two dual Xeon PCs with 2GB memory. The disks are fast too. The performance without the mirroring is great - over 2000 inserts per second. However with mirroring it goes down sharply.

When I run the inserts in a single transaction it flies. However I cannot always do that. My workaround for now is to suspend the mirroring for a few seconds do my inserts and run the resume command. This brings me back to the performance I used to have, but I hope to find a more "clean solution". It is not so good to suspend and resume the mirroring so many times��

Any suggestions (hardware, configuration or application implementation)

Thanks,

Avi

|||

Avi,

What you are doing (suspend/insert/resume) is essentially similar to high-performance mirroring mode with possibly more overhead, because of the resynchronisation.

If you can tolerate possible data loss on failure, then SAFETY = OFF is the better choice. Does that work for you? If not can you give some more details on what you want to accomplish.

Thanks,

Kaloian.