Two Easy ASYNC_NETWORK_IO Demos

There are lots of ways to do dumb stuff with SQL Server. Fun, right? If you’re familiar with ASYNC_NETWORK_IO, then you’re likely aware of the dumbness that causes it.

Otherwise, here’s a quick rundown. Client programs ask for data from the database. If SQL Server has data ready to give, but the client isn’t asking for it yet, worker threads stand around tapping their feet, whistling the Jeopardy tune, and producing ASYNC_NETWORK_IO waits.

There are two common reasons for the client to be slower at receiving data than SQL Server is in providing it. The first is a SELECT * kind of situation, where the client is asking for a large (for certain values of large) amount of data. The other reason is when the client slowly processes data as it’s received, commonly row-by-row.

Sound like an “application issue”? Yeah, sure is. Which means you might need some extra proof for your well informed and highly talented developers. Here, have a PowerShell demo.

$qry ="SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY (SELECT 'joe')), REPLICATE('a',8000) FROM master.dbo.spt_values"
Invoke-Sqlcmd -server "." -query $qry | ForEach{Start-Sleep -Seconds 1; $_}

Oh, and in case you don’t have your own, here’s a simple script to grab waits in 20s. Make sure to start it before the PS script.

DROP TABLE IF EXISTS #waits

SELECT *
INTO #waits
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('LOGMGR_QUEUE','CHECKPOINT_QUEUE','LAZYWRITER_SLEEP','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','BROKER_TO_FLUSH','SLEEP_TASK','SP_SERVER_DIAGNOSTICS_SLEEP','BROKER_TASK_STOP','HADR_WORK_QUEUE','HADR_LOGCAPTURE_WAIT',
'BROKER_TRANSMITTER','HADR_CLUSAPI_CALL','QDS_ASYNC_QUEUE','REDO_THREAD_PENDING_WORK','HADR_TIMER_TASK')

WAITFOR DELAY '00:00:20'

SELECT ws.wait_type, ws.wait_time_ms-w.wait_time_ms AS wait_ms, ws.waiting_tasks_count-w.waiting_tasks_count AS wait_count
FROM sys.dm_os_wait_stats ws
JOIN #waits w ON ws.wait_type = w.wait_type
WHERE ws.waiting_tasks_count > w.waiting_tasks_count
ORDER BY wait_ms DESC

Maybe you want something a little closer to the application code that’s actually causing the problem. Guess what, you can call .NET objects in PowerShell!

$conn = New-Object -TypeName System.Data.SqlClient.SqlConnection("Server = .;Integrated Security = True")
$qry = "SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY (SELECT 'joe')), REPLICATE('abcd',2000), REPLICATE('FROM',2000), REPLICATE('TSQL',2000) FROM master.dbo.spt_values"
$cmd = New-Object System.Data.SqlClient.SqlCommand

$conn.Open()

$cmd.CommandText = $qry
$cmd.Connection = $conn

$reader = $cmd.ExecuteReader()

while ($reader.Read()) {$reader[0];Start-Sleep -Milliseconds 5}

$conn.Close()

You probably noticed that my queries include large extra columns. If you run the script without them…

$qry ="SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY (SELECT 'joe')) FROM master.dbo.spt_values"
Invoke-Sqlcmd -server "." -query $qry | ForEach{Start-Sleep -Seconds 1; $_}

…the ASYNC_NETWORK_IO waits disappear, even though the row-by-row processing remains! There’s a reason for this: SQL Server doesn’t actually send results row-at-a-time, but stuffs as many as it can into a packet before shipping it off. Without the fluff, all 10 integers arrive at once, and the script no longer leaves the database waiting.

Now for a conversation with those skilled and deeply knowledgeable developers…