locked
high safety without automatic failover (sync) resulting slow performance. RRS feed

  • Question

  • I have done my live database (10gb size) mirroring with high safety [b]without[/b] automatic fail over (synchronous) resulting slow performance. As we have SQL server 2005 [b]std [/b]edition we can not go for asynchronous mode. And we can not go for enterprise edition now due to various other factors. This database is being connected from java web application and my customers are experiencing sever slow performance. Please could someone let me know how to know where and how to start analyzing the issue and possible reasons behind this slow performance like indexes etc...
    Friday, June 18, 2010 9:27 AM

Answers

  • Hi,

     

    Generally, since the principal server has to wait for the response of mirror server for every operation, the performance is slow than normal scenario. If the performance is equal to ever before, we don’t need to be worried about it. However, if we want to improve the performance, we could consider the Log Shipping which is also a way for High-Availability.

     

    However, if the performance is worse than ever before, this issue might be caused by several reasons:

    ·         Network
    since the principal server has to wait for the response of mirror server, if the network is too busy, the communication might be blocked and the performance will be impacted.
    Meanwhile, if there is only one network card used for data access and mirroring, we also could encounter the slow performance.

    ·         Memory
    if we have not enough memory, the machine will perform paging from memory to disk to gain more usable memory, the busy paging will impact the performance.

    ·         CPU
    if the statement has not been optimized enough, the CPU will be too busy to response the request.

    ·         IO
    if the machine encounter an IO threshold, the performance issue will occur.

     

    For troubleshooting, please follow the steps:

    1.       Monitor the usage of network
    if one server only have one network card, we recommend that you add additional network card to the server just for mirroring.
    We could use “Network Monitor” to monitor the usage of network to see if there is a network issue. We could download it from the following link:
    http://www.microsoft.com/downloads/details.aspx?FamilyID=983b941d-06cb-4658-b7f6-3088333d062f&displaylang=en

    2.       Monitor the usage of Memory, CPU, IO
    we could run “perfmon.msc” from windows run dialog to start Performance Monitor and add a new performance counter to monitor the usage of SQL Server. Please refer to this link to see which counter will be used:
    http://blogs.technet.com/b/vipulshah/archive/2006/11/30/understanding-perfmon-counters-while-troubleshooting-sql-server-performance-issues.aspx

    3.       Check if there is a blocking
    by using sys.dm_exec_requests and sys.dm_os_waiting_tasks, we could see if the blocking take place. If the blocking is take place, we have to try optimizing the statements to solve the performance issue.

     

    If anything is unclear, please let me know.

    • Marked as answer by Tom Li - MSFT Thursday, June 24, 2010 12:30 PM
    Tuesday, June 22, 2010 9:07 AM

All replies

  • you can check the SQL logs for error, check the error reporting to see which query takes load and optimize it.
    Tuesday, June 22, 2010 3:53 AM
  • Hi,

     

    Generally, since the principal server has to wait for the response of mirror server for every operation, the performance is slow than normal scenario. If the performance is equal to ever before, we don’t need to be worried about it. However, if we want to improve the performance, we could consider the Log Shipping which is also a way for High-Availability.

     

    However, if the performance is worse than ever before, this issue might be caused by several reasons:

    ·         Network
    since the principal server has to wait for the response of mirror server, if the network is too busy, the communication might be blocked and the performance will be impacted.
    Meanwhile, if there is only one network card used for data access and mirroring, we also could encounter the slow performance.

    ·         Memory
    if we have not enough memory, the machine will perform paging from memory to disk to gain more usable memory, the busy paging will impact the performance.

    ·         CPU
    if the statement has not been optimized enough, the CPU will be too busy to response the request.

    ·         IO
    if the machine encounter an IO threshold, the performance issue will occur.

     

    For troubleshooting, please follow the steps:

    1.       Monitor the usage of network
    if one server only have one network card, we recommend that you add additional network card to the server just for mirroring.
    We could use “Network Monitor” to monitor the usage of network to see if there is a network issue. We could download it from the following link:
    http://www.microsoft.com/downloads/details.aspx?FamilyID=983b941d-06cb-4658-b7f6-3088333d062f&displaylang=en

    2.       Monitor the usage of Memory, CPU, IO
    we could run “perfmon.msc” from windows run dialog to start Performance Monitor and add a new performance counter to monitor the usage of SQL Server. Please refer to this link to see which counter will be used:
    http://blogs.technet.com/b/vipulshah/archive/2006/11/30/understanding-perfmon-counters-while-troubleshooting-sql-server-performance-issues.aspx

    3.       Check if there is a blocking
    by using sys.dm_exec_requests and sys.dm_os_waiting_tasks, we could see if the blocking take place. If the blocking is take place, we have to try optimizing the statements to solve the performance issue.

     

    If anything is unclear, please let me know.

    • Marked as answer by Tom Li - MSFT Thursday, June 24, 2010 12:30 PM
    Tuesday, June 22, 2010 9:07 AM