We have a few queries that get suspended from time to time.
I run this to see them:
,DB_NAME(req.database_id) AS DB_NAME
,req.total_elapsed_time / 1000.0 AS total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
JOIN sys.dm_exec_sessions ses
ON ses.session_id = req.session_id
WHERE req.wait_type IS NOT NULL
This Select query has been hung for almost 24 hours:
Notice the Wait Type is ASYNC_Network_IO
Does this indicate a network glitch caused the query to hang up, or something else?