?
Solved

Slow queries excuted throug network. ASYNC_NETWORK_IO

Posted on 2010-01-12
3
Medium Priority
?
1,902 Views
Last Modified: 2012-05-08
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.
0
Comment
Question by:BLP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 2000 total points
ID: 26297540
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?
0
 

Author Comment

by:BLP
ID: 26305807
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 ;
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26306450
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.
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is in regards to the Cisco QSFP-4SFP10G-CU1M cables, which are designed to uplink/downlink 40GB ports to 10GB SFP ports. I recently experienced this and found very little configuration documentation on how these are supposed to be confi…
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question