• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1971
  • Last Modified:

Slow queries excuted throug network. ASYNC_NETWORK_IO

When I execute any query from any sql-client (Sql Management Studio, Excel, etc) through the network it takes at least 50 seconds then I get a state suspend of type ASYNC_NETWORK_IO  in the activity monitor.
The same query takes less than 2 seconds executed from the server itself.

Thanks in advance.
  • 2
1 Solution
BLP -->

This wait type can indicate either a network latency issue or an issue of waiting for client input before finishing a task. Since you can recreate this with SQL Server Management Studio and the same behavior occurs with the same exact query (this is a correct assumption, right?) Then I am leaning on network latency.

What kind of data are you bringing back? Is it a large result set? Trying to minimize the needless columns and rows being sent over the network can help. If it is a stored procedure with a lto of "rows affected" messages SET NOCOUNT ON can help.

If it is a relatively small and simple result set, then I would look into network latency issues. What kind of ping responses do you get from the cilent to the server? Open a command prompt and PING servername. What kind of replies do you get?

How reliable is your network? You may have to talk with network administrators and improve the network performance between the client and server.

A couple more questions:

1.) What is the speed of your network and adapters?
2.) Can you recreate this from multiple clients?
3.) How is other network activity progressing?
BLPAuthor Commented:
Thanks for your response, I think is a network bandwidth because there is no latency in the network.
The problem is notorious in 5.000 result set drows and above.
The network is 100 Mbps.
The bandwidth measured 1.26 Mbps (using Simple Network Tester)
As additional  information I get
ASYNC_NETWORK_IO      10459.50      59.33      59.33
CXPACKET      3697.88      20.98      80.31
LCK_M_U      2064.75      11.71      92.02

Running the following query

WITH    Waits
          AS ( SELECT   wait_type
                      , wait_time_ms / 1000. AS wait_time_s
                      , 100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS per
                      , ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn
               FROM     sys.dm_os_wait_stats
               WHERE    wait_type NOT LIKE '%SLEEP%' -- no need to know all sleep events (not effecting System/hardware Performance)
                        AND wait_type != 'CLR_SEMAPHORE' --Occurs when a task is currently performing CLR execution and is waiting for a semaphore. (not effecting System/hardware Performance)
                        AND wait_type != 'RESOURCE_QUEUE' -- This is an ordinary  idle  state for background threads in SQL Server.
                        AND wait_type != 'Total' -- no need the total for the analysis
                        AND wait_type != 'WAITFOR' -- This waittype indicates that the SPID is sleeping because of a WAITFOR DELAY Transact-SQL statement. (not effecting System/hardware Performance)
                        AND wait_type != 'MISCELLANEOUS' -- Catch all wait type (not effecting System/hardware Performance)
                        AND wait_type != 'SQLTRACE_BUFFER_FLUSH'  -- If only default trace is on we can ignore it. Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.
                        AND wait_type != 'BROKER_RECEIVE_WAITFOR' -- Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.
    SELECT  W1.wait_type
          , CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
          , CAST(W1.per AS DECIMAL(12, 2)) AS percent_from_Total
          , CAST(SUM(W2.per) AS DECIMAL(12, 2)) AS running_per
    FROM    Waits AS W1
            JOIN Waits AS W2
                ON W2.rn <= W1.rn
    GROUP BY W1.rn
          , W1.wait_type
          , W1.wait_time_s
          , W1.per
    HAVING  SUM(W2.per) - W1.per < 90 -- percentage threshold
    ORDER BY W1.rn ;
I would say this looks like a case of network bandwidth then and suggest reducing rows, compressing data before sending out to clients, etc. Typically if an app server is involved with the actual application's use of the data you would put that in the server room with the SQL Server on a faster backbone and then the clients using the app would be just getting the "finished" product.

Not much you can do out of the box. There are some tools out there that offer compression/etc. but might be overkill. I would say look at the network or or look at reducing the final rows that come across the network.

The other wait types are CXPACKET which isn't necessarily bad, it happens during parallel operations. You might try and look at playing with the MAXDOP setting in SP_CONFIGURE, the default is 0 or unlimited, and it means use as many procs as SQL sees fit to execute a query in parallel. If you have especially troublesome queries that are running parallel on all procs this could cause CPU pressure and affect other queries. Generally a well tuned database with proper indexes should be okay according to a lot of folks but I find lowering the maxdop (sometimes to 1 for OLTP workloads) helps a lot of environments I work in.

The final wait type is from locks, data being blocked by other users of the data. Readers blocked by writers, writers blocked by readers or other writers/etc. If it becomes a major issue, look at the causes and try and make transactions and reads as quick as possible to avoid prolonging the lock duration.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now