none
weblogic连接sql2005数据库,经常出现某个查询的sql连接被挂起的现象,等待类型为ASYNC_NETWORK_IO,请问是什么情况? RRS feed

  • 问题

  • 我分别使用过微软的官方驱动sqljdbc4.jar(2012年3月6日发布的),和weblogic自带的驱动(weblogic.jdbc.sqlserver.SQLServerDriver),都有这个问题。

    也分别试过sql2005的32位和64位不同的版本,WebLogic Server 版本: 10.3.5.0,网络也绝对没有问题,我在不同的多台机器及自己的本地测试过。

    请教有经验者给予解答,谢谢!


    2012年4月10日 8:03

答案

  • 按照下面的步骤检查一下,

    "ASYNC Network IO
    The “async network io” (in SQL 2005/2008) and “networkio”
    (in SQL 2000) wait types can point to network related issues, but most often are
    caused by a client application that is not processing results from the SQL
    Server quickly enough. This will result in filling the network buffers so that
    SQL Server cannot send more data to the client. Therefore, the process executing
    the batch will need to wait for the ability to continue sending results to the
    client.
    Reducing Waits / Wait times:
    If there are significant wait times
    on “async network io’, review the client applications. Most often, client
    applications will process rows one at a time using fetches. This may cause the
    server process to wait on “async network io” when serving up many rows. If this
    is the issue, there is nothing you can do to improve the SQL Server process
    performance. Instead, the client application (or middle tier if a web
    application) may need to be modified to allow for more efficient retrieval of
    data. Review the following list for client application issues:
    • Some
    applications, such as Microsoft Access, will ask for large result sets
    (typically identified by select statements with no where clause or full table
    scans), and then further filter the data on the client. If this is causing
    significant wait time, see if it’s possible to create a view for the client
    application to use instead. This will significantly reduce the amount of data
    being pushed to client since all of the filtering will done on SQL Server.
    Another fix could be to add a ‘where clause’ or further restrict the query so
    that less data is being sent to the client.
    • Identify large result sets and
    verify with the application or developer team how the data is being consumed. If
    the application is querying large result sets but using only a few rows,
    consider only querying the rows that are needed or use ‘TOP n’ to reduce the
    number of rows returned.
    • If you are encountering high “async network io”
    wait times when performing data loads on the server, make sure the shared memory
    protocol is enabled for the SQL Server instance and the session is connected
    using net_transport = ‘Shared memory’. You can determine the net_transport for
    the connection by looking at the DMV – sys.dm_exec_connections.
    If the above
    tuning tips are reviewed and applied, but the server is still is encountering
    high “async network io” times, then ensure there aren’t any network related
    issues:
    • Review counters such as ‘Batch Requests per second’. Any values
    over 3000 for a 100MB network card are getting excessive. ‘Current Bandwidth’ on
    the Network Interface with values over 0.6 are getting excessive.
    • Check
    network adapter bandwidth - 1 Gigabit is better than 100 megabits and 100
    megabits is better than 10 megabits.
    • Look at your NIC configuration on the
    server to make sure there are no issues with the physical card. Also, check if
    autodetect is picking the fastest speed.
    • Validate all of the network
    components between the client application and the SQL Server instance (e.g.
    switches / routers).



    Conclusion
    When a session waits on the
    "async network io” event, it may be encountering network issues. More likely,
    however, it may be an issue with the client application not processing the data
    quickly enough. If the wait times for “async network io” are high, review the
    client application to see if large results sets are being sent to the client. If
    they are, work with the developers to understand if all the data is needed and
    reduce the size of result set if possible. Ensure that any data filtering is
    performed in SQL Server instead of the client by utilizing views or more
    specific where clauses. Use the ‘TOP n’ clause so that only the rows that the
    client needs are returned to the client. Investigate network issues, if client
    application tuning tips do not apply."

    http://www.sqlservercentral.com/Forums/Topic1141649-391-1.aspx

    • 已标记为答案 江城 2012年4月11日 5:43
    2012年4月10日 13:57

全部回复

  • 按照下面的步骤检查一下,

    "ASYNC Network IO
    The “async network io” (in SQL 2005/2008) and “networkio”
    (in SQL 2000) wait types can point to network related issues, but most often are
    caused by a client application that is not processing results from the SQL
    Server quickly enough. This will result in filling the network buffers so that
    SQL Server cannot send more data to the client. Therefore, the process executing
    the batch will need to wait for the ability to continue sending results to the
    client.
    Reducing Waits / Wait times:
    If there are significant wait times
    on “async network io’, review the client applications. Most often, client
    applications will process rows one at a time using fetches. This may cause the
    server process to wait on “async network io” when serving up many rows. If this
    is the issue, there is nothing you can do to improve the SQL Server process
    performance. Instead, the client application (or middle tier if a web
    application) may need to be modified to allow for more efficient retrieval of
    data. Review the following list for client application issues:
    • Some
    applications, such as Microsoft Access, will ask for large result sets
    (typically identified by select statements with no where clause or full table
    scans), and then further filter the data on the client. If this is causing
    significant wait time, see if it’s possible to create a view for the client
    application to use instead. This will significantly reduce the amount of data
    being pushed to client since all of the filtering will done on SQL Server.
    Another fix could be to add a ‘where clause’ or further restrict the query so
    that less data is being sent to the client.
    • Identify large result sets and
    verify with the application or developer team how the data is being consumed. If
    the application is querying large result sets but using only a few rows,
    consider only querying the rows that are needed or use ‘TOP n’ to reduce the
    number of rows returned.
    • If you are encountering high “async network io”
    wait times when performing data loads on the server, make sure the shared memory
    protocol is enabled for the SQL Server instance and the session is connected
    using net_transport = ‘Shared memory’. You can determine the net_transport for
    the connection by looking at the DMV – sys.dm_exec_connections.
    If the above
    tuning tips are reviewed and applied, but the server is still is encountering
    high “async network io” times, then ensure there aren’t any network related
    issues:
    • Review counters such as ‘Batch Requests per second’. Any values
    over 3000 for a 100MB network card are getting excessive. ‘Current Bandwidth’ on
    the Network Interface with values over 0.6 are getting excessive.
    • Check
    network adapter bandwidth - 1 Gigabit is better than 100 megabits and 100
    megabits is better than 10 megabits.
    • Look at your NIC configuration on the
    server to make sure there are no issues with the physical card. Also, check if
    autodetect is picking the fastest speed.
    • Validate all of the network
    components between the client application and the SQL Server instance (e.g.
    switches / routers).



    Conclusion
    When a session waits on the
    "async network io” event, it may be encountering network issues. More likely,
    however, it may be an issue with the client application not processing the data
    quickly enough. If the wait times for “async network io” are high, review the
    client application to see if large results sets are being sent to the client. If
    they are, work with the developers to understand if all the data is needed and
    reduce the size of result set if possible. Ensure that any data filtering is
    performed in SQL Server instead of the client by utilizing views or more
    specific where clauses. Use the ‘TOP n’ clause so that only the rows that the
    client needs are returned to the client. Investigate network issues, if client
    application tuning tips do not apply."

    http://www.sqlservercentral.com/Forums/Topic1141649-391-1.aspx

    • 已标记为答案 江城 2012年4月11日 5:43
    2012年4月10日 13:57
  • 建议检查下DB服务器网络和磁盘物理IO,如果都没大问题,可能是程序本身SQL的问题或者表的结构问题。可以用with (nolock)试看看。

    2012年4月11日 1:35
  • 有时候提示信息也不准确,只能分步排查

    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2012年4月11日 2:29