locked
how to improve mirroring performance RRS feed

  • Question

  • We have used High Saftey mode, but sometimes, we are facing timeout due to mirroring wait. We do not want to change the mode from High Saftey to High performance. 

    Could you please help how to improve High Saftey mirroring performance.

    Thanks in Advance!!!

     
    Monday, July 10, 2017 10:53 AM

All replies

  • Hi R987654,

    Could you please elaborate more on the issue, exactly what error you are seeing? And what timeout is it, query timeout?

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 11, 2017 6:22 AM
  • Hi Lin,

    I am seeing wait DBMIRROR_DBM_EVENT. Deadlock and timeout in databases due to this wait. 

    on principle server

    Database mirroring is inactive for the database.This is an informational message only. No user action is required.

    on the mirror server

    The mirroring connection timed out for after 10 seconds without a response.  Check the service and network connections.

    I have checked in mirroring monitor for latency but did not find any heavy traction for mirror DBs for that duration.





    • Edited by R987654 Tuesday, July 11, 2017 7:01 AM
    Tuesday, July 11, 2017 6:57 AM
  • In high-safety mode a transaction can only be committed if it has been applied to both databases. This can cause higher transaction latency but you will not lose any data if one of the two servers suddenly dies.

    BTW, do you see timeout in the ERROR.LOG?

    https://technet.microsoft.com/en-us/library/cc917681.aspx?f=255&MSPPError=-2147217396

    /*The timeout can be modified by using the ALTER DATABASE SET PARTNER TIMEOUT command. If the principal doesn’t respond (to the ping message) within the timeout period, it is considered to be down. If you change the timeout setting, it is a best practices recommendation to set it to 10 seconds or higher. Setting it to lower than 10 seconds may result in false failures under heavily loaded or sporadic network conditions.*/


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 11, 2017 7:06 AM
  • Hi R987654,

    >>I am seeing wait DBMIRROR_DBM_EVENT. Deadlock and timeout in databases due to this wait. 

    This wait type is listed as informational purposes only, I’d suggest you review this blog for more detailed information.

    >>on principle server
    >>Database mirroring is inactive for the database. This is an informational message only. No user action is required.
    >>on the mirror server
    >>The mirroring connection timed out for after 10 seconds without a response.  Check the service and network connections.

    I’m sensing intermittent connectivity issue between principal and mirror here, could you please work with your network team to verify that?

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, July 11, 2017 9:41 AM