How can I improve SQL server mirroring

Question

Hello

(if the question is more appropriate for RackOverflow please let me know)

I've setup SQL server mirroring, using 2 SQL server 2005 standard editions. When the application is being stressed, response times increase 10-fold. I've pinpointed this to the mirror, because pausing the mirror shows acceptable response times.

What options are available for achieving better performance? Note that I'm using Standard Edition, so the excellent High Performance Mode is unavailable.

The server are in the same rack, connected to a gigabit switch.

Here's the code used to create the endpoints:

CREATE ENDPOINT [Mirroring] 
    AUTHORIZATION [sa]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)

Answer 1

Given that the servers are in the same rack do you really need Encryption turned on? RC4 is a relatively weak algorithm, so the benefit is low. And presumably the 1 Gigabit network is private between the servers?

ENCRYPTION = DISABLED

In response to @Remus Rusanu's comment : Saying that "RC4 is a strong algorithm" is totally wrong. This is what the MSDN page has to say:

Though considerably faster than AES, RC4 is a relatively weak algorithm, while AES is a relatively strong algorithm. Therefore, we recommend that you use the AES algorithm.

Answer 2

First you need to look at your redo queue on the mirror, how big is. This is the most likely culprit and indicates that your mirror machine is underpowered. More exactly, it cannot apply and write the log as it receives it from the principal fats enough to keep up, causing flow control to propagate back to the principal and delay transaction commits. In fact you should look at all the counters in the Mirroring Object, on both machines.

Unless you find measurements to back up suspicion on the endpoint settings, leave them as they are. The mirroring communication bandwidth is very very seldom the culprit.