none
Application real slow RRS feed

  • Question

  • Hi All,

    We have an application which has inbuilt reports. For some reason lately it has been very slow. I ran a query and found many wait types "ASYNC_NETWORK_IO " and few as SUSPENDED. Where should i start looking and why all of a sudden it has been slow?

    Can someone please give me pointers to begin with?

    Thanks

    Wednesday, May 15, 2019 4:45 PM

Answers

  • 1) Were there any changes recently to your application or DB server. ASYNC_NETWORK_IO is either a network issue or your application takes much time to consume the data from SQL Server. 

    2) I have seen scenarios where application server was moved to a different place than the database server and that caused this wait.



    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    • Marked as answer by LisaKruger Tuesday, May 21, 2019 2:19 PM
    Thursday, May 16, 2019 1:45 AM
  • Hi LisaKruger,

    The ASYNC_NETWORK_IO wait indicates that one of two scenarios are happening:
    The first scenario is that the session must wait for the client application to process the data received from SQL Server in order to send the signal to SQL Server that it can accept new data for processing. This is a common scenario that may reflect bad application design, and is the most often cause of excessive ASYNC_NETWORK_IO wait type values
    The second is that network bandwidth is maxed out. A clogged Ethernet will cause the slow data transmission back and forth from the application. This, in and of itself, will degrade the efficiency of the application.

    You can follow the steps from below document to troubleshoot it. Please refer to Reducing SQL Server ASYNC_NETWORK_IO wait type.

    SUSPENDED:
    It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAITit can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. For example, if the query the has posted a I/O request to read data of a complete table tblStudents then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue.

    So if it is waiting, check the wait_type column to understand what it is waiting for and troubleshoot based on the wait_time. Using below T-SQL to check.

    SELECT  wt.session_id, 
        ot.task_state, 
        wt.wait_type, 
        wt.wait_duration_ms, 
        wt.blocking_session_id, 
        wt.resource_description, 
        es.[host_name], 
        es.[program_name] 
    FROM  sys.dm_os_waiting_tasks  wt  
    INNER  JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address 
    INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id 
    WHERE es.is_user_process =  1
    
    Hope this could help you.

    Best regards,
    Cathy Ji



    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

    Thursday, May 16, 2019 2:22 AM
  • The app can be on the same box, but the code can still be slow in consuming the result from a query. Most often, this wait stats is *not* because of slow network, but because the developers do something which takes time when processing the result from a query, between each row.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by LisaKruger Tuesday, May 21, 2019 2:19 PM
    Friday, May 17, 2019 6:41 AM
    Moderator
  • Hi LisaKruger,

    If you have resolved your issue, please close the thread by marking the useful reply as answer. It will make it easier for other community members to find the useful ones.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy Ji

    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

    • Marked as answer by LisaKruger Tuesday, May 21, 2019 2:19 PM
    Friday, May 17, 2019 10:07 AM

All replies

  • 1) Were there any changes recently to your application or DB server. ASYNC_NETWORK_IO is either a network issue or your application takes much time to consume the data from SQL Server. 

    2) I have seen scenarios where application server was moved to a different place than the database server and that caused this wait.



    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    • Marked as answer by LisaKruger Tuesday, May 21, 2019 2:19 PM
    Thursday, May 16, 2019 1:45 AM
  • Hi LisaKruger,

    The ASYNC_NETWORK_IO wait indicates that one of two scenarios are happening:
    The first scenario is that the session must wait for the client application to process the data received from SQL Server in order to send the signal to SQL Server that it can accept new data for processing. This is a common scenario that may reflect bad application design, and is the most often cause of excessive ASYNC_NETWORK_IO wait type values
    The second is that network bandwidth is maxed out. A clogged Ethernet will cause the slow data transmission back and forth from the application. This, in and of itself, will degrade the efficiency of the application.

    You can follow the steps from below document to troubleshoot it. Please refer to Reducing SQL Server ASYNC_NETWORK_IO wait type.

    SUSPENDED:
    It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAITit can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. For example, if the query the has posted a I/O request to read data of a complete table tblStudents then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue.

    So if it is waiting, check the wait_type column to understand what it is waiting for and troubleshoot based on the wait_time. Using below T-SQL to check.

    SELECT  wt.session_id, 
        ot.task_state, 
        wt.wait_type, 
        wt.wait_duration_ms, 
        wt.blocking_session_id, 
        wt.resource_description, 
        es.[host_name], 
        es.[program_name] 
    FROM  sys.dm_os_waiting_tasks  wt  
    INNER  JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address 
    INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id 
    WHERE es.is_user_process =  1
    
    Hope this could help you.

    Best regards,
    Cathy Ji



    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

    Thursday, May 16, 2019 2:22 AM
  • Thanks Vivek and Cathy. The application and the sql server are on the same box so why will it show as an I/O issue.

    Cathy will try your method and let you know if anything improved.

    • Marked as answer by LisaKruger Tuesday, May 21, 2019 2:19 PM
    • Unmarked as answer by LisaKruger Tuesday, May 21, 2019 2:19 PM
    Thursday, May 16, 2019 1:05 PM
  • The app can be on the same box, but the code can still be slow in consuming the result from a query. Most often, this wait stats is *not* because of slow network, but because the developers do something which takes time when processing the result from a query, between each row.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by LisaKruger Tuesday, May 21, 2019 2:19 PM
    Friday, May 17, 2019 6:41 AM
    Moderator
  • Hi LisaKruger,

    If you have resolved your issue, please close the thread by marking the useful reply as answer. It will make it easier for other community members to find the useful ones.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy Ji

    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

    • Marked as answer by LisaKruger Tuesday, May 21, 2019 2:19 PM
    Friday, May 17, 2019 10:07 AM
  • Thanks All for the insight.
    Tuesday, May 21, 2019 2:20 PM