SPIDs stuck indefinitely in suspended state

Question

I have a few application servers that all connect to a central database server. The applications execute stored procedures (that get XML) on the database.

Every once in awhile a transaction will get "stuck" and stop processing. The spid is in the suspended state with the wait type - ASYNC_NETWORK_IO. The last reads and writes in sys.dm_exec_connections are not current. Basically, it's just sitting there indefinitely. The stored procedure is using TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Tops waits on the server are:

wait_type           wait_time_s     pct     running_pct
CXPACKET            16737173.38     56.60       56.60
ASYNC_NETWORK_IO    3628438.90      12.27       68.87

These "stuck" spids can block index rebuilding, specifically on a table that is being used in a select in the stored procedure. I have isolated this section in the store procedure.

Why does this spid just sit there if it's not doing anything? We are thinking that the application times out when processing large amounts of XML. If this is the case then is there anything I can do on the SQL side to close this connection or should this be handled on the application side?

The application developers are basically saying that it's a SQL issue because it's a stuck transaction.

The only way to get rid of these now is to set up a job that would check to see any non current spid and kill it or kill it manually. Neither is really an acceptable solution. How can I handle these SPIDs?

Running SQL Server 2008 R2 (SP2) - 10.50.4000.0

Answer 1

If you've got SPIDs sitting with the wait type of ASYNC_NETWORK_IO it's either row by row processing within the application, the application is calling huge recordsets which is can't process fast enough, or there's a network capacity problem.