We have a third-party Microsoft Access application that uses SQL Server 2012 as its data store. It is a well known issue that Access often does not tell SQL Server that it has received the results of a query, causing sessions to sit in ASYNC_NETWORK_IO wait for minutes or even hours although the application screen displays the result of the query almost instantly. In some cases, this can cause blocking. I want to find sessions that are in that state for a long period of time. Eventually, I may want to create a procedure that will automatically kill them.
For each session that is currently in an ASYNC_NETWORK_IO wait, I want to find out how long it has been waiting. However, dm_exec_requests.wait_time is never as high as 2,000 even though the difference between GETDATE() and last_request_end_time is much longer. last_request_end_time equals GETDATE() minus total_elapsed_time (CalculatedStartTime in the query below) if both are rounded to the nearest second. This is also true of dm_exec_sessions.start_time and dm_exec_requests.last_request_end_time.
In the results of this query:
SELECT SPID = s.session_id ,Program = s.[program_name] ,StartTime = r.start_time ,ElapsedTime = CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, r.start_time, GETDATE()), 0), 114) ,s.last_request_end_time ,r.wait_time ,CalculatedStartTime = DATEADD(MILLISECOND, -r.total_elapsed_time, GETDATE()) FROM sys.dm_exec_sessions s JOIN sys.dm_exec_requests r ON s.session_id = r.session_id WHERE s.open_transaction_count = 0 AND r.[status] = 'suspended' AND r.wait_type = 'ASYNC_NETWORK_IO' AND r.last_wait_type = 'ASYNC_NETWORK_IO' AND r.command = 'SELECT' AND r.blocking_session_id = 0 AND r.total_elapsed_time > 10000 -- 10 seconds ORDER BY r.wait_time DESC
I must be misunderstanding the definition of some of these DMV columns. Note that in the result, row 9 shows a wait_time of only 629 ms, yet the ElapsedTime (time since the last_request_end_time) is over 28 minutes:
How can I get the results I intend (a row for each session_id that shows how long it has been waiting on ASYNC_NETWORK_IO)?