Sql-server – Troubleshooting spid suspended ASYNC_NETWORK_IO

entity-frameworksql server

Recently we moved our database to a new server

The old server was running

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08
Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)

The new server is running

Microsoft SQL Server 2019 (RTM-CU2) (KB4536075) - 15.0.4013.40 (X64) Feb 3 2020 16:40:57
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Linux (CentOS Linux 8 (Core))

We have a C# Windows service that uses the database. It is intermittantly stopping or running extremely slow.

The windows service is using .Net Framework 4.7.2 and it calls a library written in .net standard 2.0 which also makes sql calls.

I found this question on why the status of a SPID is suspended and see that our Suspended Spids have wait_type ASYNC_NETWORK_IO

According to the docs this

Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.

Restarting the windows service does temporarily solve the problem. However this does not help me troubleshoot the cause of the problem.

Mostly I am using EntityFramework 6.4

[Update]

I have taken to running 

dbcc inputbuffer(sessionId)

to see the last running SQL

Best Answer

ASYNC_NETWORK_IO generally indicates that SQLServer has results ready to return to the client, but the client is not requesting them as fast as SQLServer can deliver them, or the network is going slow.

A common scenario with C# is if you are consuming results via an IEnumerable<> with a bunch of slow processing per row coming from the database. If you're not returning millions of rows, try putting a .ToList() or something in there to buffer all the results from the database prior to processing them.

Related Question