locked
Drop in performance when Mirroring turned ON RRS feed

  • Question

  • Hello SQL Experts,

    I have sql server 2008 R2 version with java application as front end application. My application runs nightly DML operations on the database, the approximate rows would be 300,000 and I schedule weekly full backup, 2 times daily diff backup and every 3 min tran log backup.. just to clear the log space so that the DML operations occurs with out any issues. As a DR plan we configured Mirroring on the database. Now we are having Issues that the DML operations are occuring very slowly and taking long times to execute... I can understand that we can change change the Isolation level and use NoLOCK.... apart from the what can I do..... I also try to rebuild the indexes with 80% fill factor so that the Updates and Inserts will occuer faster and I ofter use DBCC Shrinkfile to shrink the log file......... If I disable the Mirroring every thing works fine and perfect and DB performance is more.

    My question is how to optimize the log space and with out disabling Mirroring how to increase the performance...

    Thanks in advance for suggestions

     

    Sunday, August 21, 2011 3:32 AM

Answers

  • Hello,

     

    What mode of mirroring you are using ? High Safety(Synchronous) Or High Performance(Asynchronous) ?

    If High Safety ....

    After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction.

     

    In this case try changing to High Performance & check your DML performance.


    Rgds, Krishna Http://Blogs.SQLServer.in/
    Sunday, August 21, 2011 3:52 AM

All replies

  • Hello,

     

    What mode of mirroring you are using ? High Safety(Synchronous) Or High Performance(Asynchronous) ?

    If High Safety ....

    After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction.

     

    In this case try changing to High Performance & check your DML performance.


    Rgds, Krishna Http://Blogs.SQLServer.in/
    Sunday, August 21, 2011 3:52 AM
  • I forgot that option... thnx for remonding....

    It worked... the performance got improved along with other changs

    Thnaks

     

     

     

    Sunday, August 21, 2011 5:14 AM
  • Great...you are welcome !
    Rgds, Krishna Http://Blogs.SQLServer.in/
    Sunday, August 21, 2011 5:18 AM