This is an interesting wait type. When you run Paul Randall or Glenn Berry’s query to capture top wait stats you can get a good picture of where is Sql Server experiencing the most waits. Since these are based on DMV’s they only give this since the last SQL was restarted. But this is a great way to capture what is going on under the hood.

Wait Stats Query:

[sourcecode language=”sql”]

;WITH Waits
AS
(
SELECT wait_type AS [Wait_Type]
, wait_time_ms / 1000.0 AS WaitS
, (wait_time_ms – signal_wait_time_ms) / 1000.0 AS ResourceS
, signal_wait_time_ms / 1000.0 AS SignalS
, waiting_tasks_count AS WaitCount
, 100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS Percentage
, ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ( ‘CLR_SEMAPHORE’, ‘LAZYWRITER_SLEEP’, ‘RESOURCE_QUEUE’, ‘SLEEP_TASK’, ‘SLEEP_SYSTEMTASK’
, ‘SQLTRACE_BUFFER_FLUSH’, ‘WAITFOR’, ‘LOGMGR_QUEUE’, ‘CHECKPOINT_QUEUE’, ‘REQUEST_FOR_DEADLOCK_SEARCH’
, ‘XE_TIMER_EVENT’, ‘BROKER_TO_FLUSH’, ‘BROKER_TASK_STOP’, ‘CLR_MANUAL_EVENT’, ‘CLR_AUTO_EVENT’
, ‘DISPATCHER_QUEUE_SEMAPHORE’, ‘FT_IFTS_SCHEDULER_IDLE_WAIT’, ‘XE_DISPATCHER_WAIT’, ‘XE_DISPATCHER_JOIN’, ‘BROKER_EVENTHANDLER’
, ‘TRACEWRITE’, ‘FT_IFTSHC_MUTEX’, ‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP’, ‘BROKER_RECEIVE_WAITFOR’, ‘ONDEMAND_TASK_QUEUE’
, ‘DBMIRROR_EVENTS_QUEUE’, ‘DBMIRRORING_CMD’, ‘BROKER_TRANSMITTER’, ‘SQLTRACE_WAIT_ENTRIES’, ‘SLEEP_BPOOL_FLUSH’
, ‘SQLTRACE_LOCK’
) — These are filtered out as these are usually benign
)
SELECT W1.wait_type AS WaitType
, CAST (W1.WaitS AS DECIMAL (14, 2)) AS Wait_S
, CAST (W1.ResourceS AS DECIMAL (14, 2)) AS Resource_S
, CAST (W1.SignalS AS DECIMAL (14, 2)) AS Signal_S
, W1.WaitCount AS WaitCount
, CAST (W1.Percentage AS DECIMAL (4, 2)) AS Percentage
, CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S
, CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S
, CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum
, W1.wait_type
, W1.WaitS
, W1.ResourceS
, W1.SignalS
, W1.WaitCount
, W1.Percentage
HAVING SUM(W2.Percentage) – W1.Percentage < 95; — percentage threshold

[/sourcecode]

When I run this query on my database server, I get the following result.

Today, I am interested in talking about “ASYNC_NETWORK_IO“. From MSDN, I see that this is “Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.” This is not very helpful, If you already know what it is, then it makes sense, if not MSDN only confuses you more (as it did me). But Paul Randall recently shed some light on this topic and this is my new understanding.

When a client application sends a query to Sql Server, it executes it in the most efficient way and returns the results set back to the client. Once the client receives the data set they send an ACK back to Sql Server confirming the receipt. This is where is problem is.

Sometimes, these client machines are not capable (or configured) to receive or process the entire data set in one go. So they only retrieve it piecemeal (aka. Row- by-row processing). Sql Server has completed its task and is just waiting for Client software to process the data set and confirm that it is done. Until the client software completes processing the entire data set, it will not send the ACK back, which means Sql Server has to wait until then. Hence the wait. It’s an asynchronous process;  Network related IO type wait; Hence the name “ASYNC_NETWORK_IO”. But a misleading one.

Unless you see a lot of this wait type, I would not jump to immediate action. I would capture the output over a period of time and depending on the trending, I would talk with the development team.

Hope this helps,
_Sqltimes