Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

(reposted from stackoverflow)

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)
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Does the link between the principle and mirror have sufficient bandwidth to cope with the transaction log generation at the principle?

Are your log files optimised? See these links:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx

share|improve this answer
    
I enabled a second NIC in each computer and setup a small private VLAN for these. Switching the mirror to these NIC's made a large difference in performance –  Edoode Jul 22 '09 at 21:37
add comment

While pausing the mirror helps it may not be the actual cause of your trouble. In my experience, using a synchronous mirror only doubles the response time. If you're seeing a response time that gets worse with higher loads, something else is likely to be afoot.

A few things to think about and try:

  • Check that the server is not underpowered (CPU/memory)
  • Check that the network isn't the bottleneck (by directly connecting the 2 SQL servers and forcing the mirroring traffic to go over that direct link)
  • Check that I/Os aren't the bottleneck (although this seems unlikely to me given the 10x response time increase only with mirroring on). The usual stuff applies: better hardware RAID/cache, separate devices, ...
  • High performance mode is asynchronous. If that's okay with you, then you could use replication rather than mirroring. (I forget the MS term for this feature.)
share|improve this answer
    
Thanks I've checked most other alternatives for the high response times. I'll link the servers using a second NIC and see if that helps. High performance mode is only available in Enterprise Edition –  Edoode Jul 8 '09 at 6:30
    
logshipping may be a better performance alternative in 2005 std if your considering high performance mirroring. –  Nick Kavadias Jul 8 '09 at 11:46
add comment

Your Answer

 
discard

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.