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
SET XACT_ABORT ON
to the stored procedure to have the transaction rolled back on client timeout.