ASYNC_NETWORK_IO Wait Type Explained

ASYNC_NETWORK_IO waits occur within SQL Server's network protocol layer when the server has filled its send buffers but cannot continue transmitting because the client application hasn't consumed the previously sent data. The SQL Server scheduler places the worker thread in a suspended state until the client's TCP window size increases, indicating buffer space is available.

This wait type manifests in the protocol stack between the SQL Server engine and the network interface. When a client executes a query returning large result sets, SQL Server writes data to network buffers managed by the Windows TCP/IP stack. If the client application processes results slower than SQL Server generates them, the TCP receive window on the client side fills up. The client's operating system then reduces the TCP window size in acknowledgment packets back to SQL Server, causing SQL Server's send operations to block.

The underlying mechanism involves SQL Server's network libraries (SNI layer) interfacing with Windows Winsock APIs. When a send operation would block due to full client buffers, the worker thread registers for an asynchronous completion event and yields the scheduler. The thread remains in ASYNC_NETWORK_IO state until either the client consumes enough data to free buffer space or a timeout occurs.

SQL Server 2016 introduced improvements in the SNI layer that better handle network congestion scenarios. SQL Server 2019 enhanced telemetry around network waits, providing more granular wait statistics. SQL Server 2022 includes additional optimizations in the protocol layer for cloud scenarios where network latency varies significantly.

-- Check current ASYNC_NETWORK_IO waits by session
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    t.text AS current_sql,
    s.reads,
    s.writes,
    s.logical_reads
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'ASYNC_NETWORK_IO'
    AND s.is_user_process = 1;

-- Analyze historical ASYNC_NETWORK_IO patterns
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms,
    (wait_time_ms - signal_wait_time_ms) / NULLIF(waiting_tasks_count, 0) AS avg_resource_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'ASYNC_NETWORK_IO';

-- Identify sessions with high network activity
SELECT TOP 20
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.reads + s.writes AS total_io,
    s.logical_reads,
    s.row_count,
    DATEDIFF(second, s.login_time, GETDATE()) AS session_duration_sec,
    s.last_request_start_time,
    s.status
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1
    AND (s.reads + s.writes) > 1000
ORDER BY (s.reads + s.writes) DESC;

-- Check for blocked send operations and client response times
SELECT 
    c.session_id,
    c.connect_time,
    c.net_transport,
    c.protocol_type,
    c.client_net_address,
    c.local_net_address,
    c.client_tcp_port,
    c.local_tcp_port,
    s.host_name,
    s.program_name,
    CASE 
        WHEN r.wait_type = 'ASYNC_NETWORK_IO' THEN 'Currently waiting on client'
        ELSE 'Not waiting'
    END AS network_status
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.is_user_process = 1
ORDER BY c.session_id;

Identify and terminate long-running network waits This script identifies sessions stuck in ASYNC_NETWORK_IO for extended periods and provides kill commands for sessions that may have abandoned connections.

-- Identify sessions to potentially terminate
-- TEST IN DEVELOPMENT FIRST - This generates KILL statements
SELECT 
    'KILL ' + CAST(s.session_id AS VARCHAR(10)) + ';' AS kill_command,
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.wait_time / 1000 AS wait_seconds,
    s.last_request_start_time,
    t.text AS current_sql
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'ASYNC_NETWORK_IO'
    AND r.wait_time > 300000  -- 5 minutes
    AND s.is_user_process = 1
    AND s.session_id > 50;  -- Avoid system sessions

-- Execute individual KILL commands only after verifying the session should be terminated

Configure network packet size optimization Adjusting network packet size can reduce the frequency of network round trips and improve throughput for large result sets.

-- Check current network packet size setting
SELECT 
    name,
    value,
    value_in_use,
    description
FROM sys.configurations
WHERE name = 'network packet size';

-- Increase network packet size for better throughput
-- Default is 4096, maximum is 32767
-- Test impact on memory usage and network infrastructure
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'network packet size', 8192;  -- 8KB packets
RECONFIGURE;

-- Restart SQL Server service for this change to take effect

Enable result set caching for Azure Synapse Analytics This reduces network traffic for large result sets by caching results. This feature is available in Azure Synapse Analytics only, not in on-premises SQL Server.

-- Enable result set caching at database level (Azure Synapse Analytics only)
-- This reduces network bandwidth usage by caching query results
ALTER DATABASE SCOPED CONFIGURATION SET RESULT_SET_CACHING = ON;

Configure appropriate network packet sizes based on your network infrastructure. Increase the default 4096-byte packet size to 8192 or 16384 bytes for applications that regularly transfer large result sets, but test thoroughly as larger packets increase memory usage and may cause issues with network equipment that doesn't support jumbo frames.

Implement client-side best practices including using connection pooling, processing results immediately rather than accumulating them in memory, implementing appropriate timeout values, and utilizing server-side filtering instead of retrieving large datasets for client-side filtering. Applications should use streaming APIs like SqlDataReader in .NET rather than loading entire result sets into DataTables.

Monitor network infrastructure for latency, packet loss, and bandwidth utilization. Configure network adapters with appropriate buffer sizes and disable features like TCP chimney offload and receive side scaling if they cause compatibility issues. Implement Quality of Service (QoS) policies to prioritize SQL Server traffic during peak network usage periods.

Set up proactive monitoring for ASYNC_NETWORK_IO wait times exceeding reasonable thresholds (typically 1-5 seconds depending on your environment). Create alerts when the cumulative wait time for this wait type represents more than 10% of total wait time, indicating systemic client or network issues requiring investigation.

Architect applications to minimize result set sizes through proper indexing, query optimization, and pagination strategies. Consider implementing result set caching for frequently requested data and use techniques like snapshot isolation to prevent clients from holding shared locks while slowly consuming data.