locked
Database Mirroring Slow for this query RRS feed

  • Question

  • Hello,

    I'm running SQL 2008 database mirroring (high-safety) for a single database.
    The mirrored server is on the same LAN. I'm running the following query but
    it seems to be taking a huge amount longer when mirroring is enabled. IT
    takes around 15 seconds to run without mirroring and when I enable mirroring
    it takes almost 5 minutes!

    DECLARE @counter INT
    SET @counter = 0
    WHILE @counter < 100000
        BEGIN
            INSERT INTO t_names (int_id, [name]) VALUES (165, 'jane dale')
            SET @counter = @counter + 1
    END

    I'd obviously expect a slight performance hit but not the slowdown that I am
    seeing in this instance?

    Thanks
    Wednesday, May 26, 2010 11:15 AM

Answers

  • Hi,

    Generally, regarding database mirroring performance, you could consider the following factors: transaction safety level, I/O throughput, network performance and the capability of the principle server and mirrored server. My suggestion is:

    1. Since your database mirroring session runs in high-safety mode, the operations between the principle server and the mirrored server is synchronous. 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. In this case, when you insert 100000 rows into a table, the principle server need to wait for the mirrored server finishing this query and then commit the transaction, which may lead to the principal server’s transaction latency.
    The database mirroring session also supports asynchronous mirroring , which is a high-performance mode. In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.
    Although, the asynchronous mode enhances performance at the expense of high availability, supports only one form of role switching: forced service (with possible data loss), which uses the mirror server as a warm standby server. Forced service is one of the possible responses to the failure of the principal server. Because data loss is possible, you should consider other alternatives before forcing service to the mirror.

    2. Besides changing transaction safety level, you could setup the mirrored server with the same capacity  as the principle server and improve the network system.

    Reference:
    Database Mirroring Best Practices and Performance Considerations
    http://technet.microsoft.com/en-us/library/cc917681.aspx

    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, May 28, 2010 7:49 AM
  • You may have other options depending on your requirements.  If you are using enterprise edition you could consider async (High Performance) mirroring.  If you can hanle a few more minutes delay you could also try log shipping.
    Friday, May 28, 2010 10:22 PM

All replies

  • Hi,

    Generally, regarding database mirroring performance, you could consider the following factors: transaction safety level, I/O throughput, network performance and the capability of the principle server and mirrored server. My suggestion is:

    1. Since your database mirroring session runs in high-safety mode, the operations between the principle server and the mirrored server is synchronous. 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. In this case, when you insert 100000 rows into a table, the principle server need to wait for the mirrored server finishing this query and then commit the transaction, which may lead to the principal server’s transaction latency.
    The database mirroring session also supports asynchronous mirroring , which is a high-performance mode. In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.
    Although, the asynchronous mode enhances performance at the expense of high availability, supports only one form of role switching: forced service (with possible data loss), which uses the mirror server as a warm standby server. Forced service is one of the possible responses to the failure of the principal server. Because data loss is possible, you should consider other alternatives before forcing service to the mirror.

    2. Besides changing transaction safety level, you could setup the mirrored server with the same capacity  as the principle server and improve the network system.

    Reference:
    Database Mirroring Best Practices and Performance Considerations
    http://technet.microsoft.com/en-us/library/cc917681.aspx

    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, May 28, 2010 7:49 AM
  • You may have other options depending on your requirements.  If you are using enterprise edition you could consider async (High Performance) mirroring.  If you can hanle a few more minutes delay you could also try log shipping.
    Friday, May 28, 2010 10:22 PM