Poor performance on mirrored DB

Question

Hi!

Windows Server 2008 R2, SQL Server 2008 R2, both updated to latest updates.

I'm having some performance issue on mirrored DB. Principal and mirror servers are on two different locations. Principal mirror has two additional DB plus mirrored DB. Mirror only has mirrored DB. Network traffic is low between servers (can't be a reason). Disks on principal are slightly overloaded during peaks (response time over 15ms but under 20ms). Average transaction delay is 100ms. 

 

Other performance counters seems OK to me, and I can't figure it out what's wrong. When transactions/sec peaks occure on SQL server, users starts to complain they have to wait too long for application response (application gets data from SQL server).

Is there any whitepaper, blog, document, anything covering database mirroring performance issue and gives performance monitor best values of database mirroring counters?

 

Thanks for any advice, help, ...!

Best regards, Dejc

Answer 1

http://sqlcat.com/sqlcat/b/whitepapers/archive/2007/11/19/database-mirroring-best-practices-and-performance-considerations.aspx

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Hi,

What edition of SQL Server are you on?  Is your mirroring synchronous?

 

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 3

High performance or high safety mirroring?  Also, what version of SQL, Standard or Enterprise?  Synchronous Mirroring (High Safety) is alsways going to have some overhead, especiallywhen replicating across a WAN.

---

David A. Bermingham, MVP, Senior Technical Evangelist, SIOS Technology Corp

Answer 4

 

Thanks for fast replays! I'm using Standard 64bit SQL on both servers with high safety mirroring (synchronus mirroring). I believe ther can be some overhead when replicating over WAN, but in this case it's LAN (distance between server locations is 100 yards). 

@Uri Dimant thanks! I'm familiar with this whitepaper but I haven't find any "best values" within document. I admit I haven't read it all. I've just made quick lookup.

I've managed to find out in last hour and half it's disk write time, log write wait time and avarage lock wait time not mirroring issue. I will check what is going on with locking, because avarage lock wait time is almost 3/4 second. Could it be mirroring the reason?

 

 

Best regards, Dejc

Answer 5

I don't believe mirroring cause any issues in your case....  might have some blocking going on your principal dbs ... start SQL Trace and find out any blocks / deadlocks.

If you are lucky couple of indexes would resolve the problem .... by the way do you have maintenance plans (like index de-fragmentation,update stats ect..)

---

http://uk.linkedin.com/in/ramjaddu

Answer 6

Dejc

There are lots of 'best values' just read it carefully 

/*

One of the most important database mirroring configuration decisions is to select the appropriate transaction safety level. The transaction safety level determines whether the changes on the principal database are applied to the mirror database synchronously or asynchronously. SQL Server 2005 provides two safety levels—OFF and FULL.

*/

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 7

Check forblocking in the principle server,

using

select * from sys. sysprocesses where blocked<>0

 

Answer 8

How rude I am. I haven't provide you feedback for almost two months.

Problem was in queries not in mirroring.

Again, thanks to everyone with advices and help! 

Answer 9

Thanks for the feedback dejc...

---

Fabrício França Lima | MCP, MCTS, MCITP | Visite meu site: http://fabriciolima.net | Dicas de artigos SQL: Siga-me no twitter @fabriciodba.

Answer 10

check this link

http://stackoverflow.com/questions/1092953/how-can-i-improve-sql-server-mirroring