locked
Mirroring very slow RRS feed

  • Question

  • Hi, we're trying to use synchronous Mirroring for the first time at a customer site and having a fairly major issue with performance. Our main app calls a lot of stored procs when the user presses Save and without the mirroring takes about 9 seconds.  With mirroring turned on it takes something like 35.

    We've tried to reproduce it on the office test system by restoring a backup from site, but it always takes 5 seconds or so with mirroring turned on or off.

    On site both PC's are quad core 2.8 with 4GB memory so don't think thats the problem.  Also the network pings always take <1ms.

    I've tried to look a the various performance counters available and both look similar on site to our office test system, but any suggestions at what I should look at?  I'm currently trying to work out why our office system is so much fatster than site, even with mirroring off on both.

    Thanks

    Wednesday, September 15, 2010 4:04 PM

All replies

  • When user presses on the Save button , your app calls lots of SPs or just one specific?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, September 16, 2010 7:36 AM
  • It calls something in the order of 1000 SPs, each in their own batch.  Doing some more testing in the office it normally takes 2-3 seconds to save on a standalone database, roughly 4 with a mirror.

    Any idea which performance counters I can look at to work out if the network is the problem?

    Thursday, September 16, 2010 9:17 AM
  • Hi Richard,

     

    Any idea which performance counters I can look at to work out if the network is the problem?

    I would like to recommend that you refer to the following link:

    http://msdn.microsoft.com/en-us/library/ms189931.aspx

    Meanwhile I would like to suggest you to run ping.exe continuously while the SQL Server is handling those stored procedures. In order to run ping.exe continuously, please add “-t” switch to this command.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Friday, September 17, 2010 11:50 AM
  • It calls something in the order of 1000 SPs, each in their own batch.  Doing some more testing in the office it normally takes 2-3 seconds to save on a standalone database, roughly 4 with a mirror.

    I assume each batch is an independent transaction?  You might consider executing all as a single transaction, if possible.  This will greatly improve performance with and without mirroring, especially in synchronous mode. 
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, September 17, 2010 12:10 PM