Post reply

ASYNC_NETWORK_IO wait type

  • November 9, 2011 at 9:27 am

    #259662

    Hello Everyone,

    Hope all is well.

    I am seeing a lot of ASYNC_NETWORK_IO wait types. I was able to identify the SQL that has ASYNC_NETWORK_IO wait type based on Adam Machanic's whoisactiv script. This is what I see in the wait_info column of SP_whoisactiv

    (1x: 215ms)ASYNC_NETWORK_IO

    (1x: 535ms)ASYNC_NETWORK_IO

    (1x: 896ms)ASYNC_NETWORK_IO

    (1x: 1224ms)ASYNC_NETWORK_IO

    (1x: 1213ms)ASYNC_NETWORK_IO

    (1x: 1583ms)ASYNC_NETWORK_IO

    (1x: 1916ms)ASYNC_NETWORK_IO

    (1x: 413ms)ASYNC_NETWORK_IO

    (1x: 1678ms)ASYNC_NETWORK_IO

    The select statement is trying to fetch records between 7 and 90 depending on an ID. On the network switch side we have increased the bandwidth from 100MB to 1 GB now and still see no change in the wait times or wait type. So I am assuming that the SPID that is waiting (with the suspended status) is waiting for the client application to fetch the data.

    The graphical execution plan suggests that adding a missing nonclustered index will impact the performance with an improvement of 60%. I am not sure if I need to go in this route to remove this wait_type yet.

    Can someone help me with this. I want to prove that this is not a Database issue.

    Thanks a ton in advance.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • November 9, 2011 at 9:34 am

    #1405317

    Async network IO is usually a result of a client app that's slow to receive or process the resultset from SQL Server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 9, 2011 at 9:44 am

    #1405334

    Thanks for the response Gail.

    Does that mean whether or not I add the missing nonclustered index or irrespective of increasing the bandwidth of network switches I would still encounter this wait type until its the code for the app is fixed?

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • November 9, 2011 at 9:53 am

    #1405352

    If the client app is the cause, yes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 9, 2011 at 9:59 am

    #1405356

    I will create the missing index and see if there is any change in the wait types.

    Thanks Much Gail.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • November 9, 2011 at 10:07 am

    #1405360

    Sapen (11/9/2011)

    I will create the missing index and see if there is any change in the wait types.

    Thanks Much Gail.

    Can't be. You will return the exact same amount of data over the network.

    You might solve a perf problem, but not over the network.

  • November 9, 2011 at 5:57 pm

    #1405482

    ok..thnx

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply