High ASYNC_NETWORK_IO when user locks computer with Excel open I have a user who gets in at 8AM and leaves at 6PM every day, and every day I see the same strange pattern in SolarWinds DPA.

It seems that Excel is refreshing the Query Table when the user locks their screen.  My questions are:


Is there any way to stop Excel from doing that?  I can't be the only DBA who has fought such problems.
Is there any way I can reproduce the problem on my machine?
Help finding Google keywords to search for a known issue... I've tried unsuccessfully




UPDATE

It occurred to me there could be other factors at play here, so here goes:


The file is on a Network-Attached Storage Drive shared among a work group.
I looked into various Network Interface Card powersaving feature issues that could be causing Excel to think the file handle is lost (???)
"Refresh data when opening the file" is checked under Connection Properties
This may be occurring on a Virtual Machine.


I have found one other user with a similar problem: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bcc35121-1575-4a7f-b82f-1c20f6fed58d/process-blocked-by-asyncnetworkio-status-blocks-other-queries?forum=sqldatabaseengine
ASYNC_NETWORK_IO can cause database contention issues if the results being sent to the client never spool anywhere (e.g., via an ORDER BY clause forcing a sort in tempdb).
If this issue is happening with Excel, then the problem is likely due to an Excel Database Connection refreshing on the background thread and the user simultaneously locking their computer during the background refresh.  When the background thread is complete, the computer is locked.  The issue is that in Win32, a locked computer means the user session object is not available.  When the user session object is not available, there is nowhere for the background thread to return results to, and it just hangs, waiting for Excel.exe to read the results.  Because the results never spool anywhere, the ASYNC_NETWORK_IO lock causes a livelock on any previously acquired locks.  In itself, ASYNC_NETWORK_IO is harmless.  It's the lock chain that is doing the damage.
To sum it up, this problem can happen under the following two conditions:

ASYNC_NETWORK_IO is present due to a slow reader (in my case, the slow reader is livelocked due to Win32 user session object unavailable)
The results are never spooled to some intermediary object, implicitly (e.g. ORDER BY triggering a sort in tempdb on underlying table, because the data is too big to sort completely in memory) or explicitly (INSERT results into a tempdb table, do SELECT on the tempdb table, which releases the livelock).

Two options:

If you don't have control over the client's slowness?

a. Write the query results to a temp table. Return the temp table to the user.
b. Also, don't forget to SET NOCOUNT ON at the top of the stored procedure, to avoid roundtrip delays due to DONE_IN_PROC TDS messages being sent to the client.

If you do have control over the client's slowness?

a. Check if the NIC is misconfigured or if the client is using a VM and the VM's NIC is miconfigured. See: https://www.reddit.com/r/sysadmin/comments/2k7jn5/after_2_years_i_have_finally_solved_my_slow/
b. Measure using Perfmon. Microsoft uses a heuristic of 10 for PerfMon Network Output Queue Length. See: https://technet.microsoft.com/en-us/library/aa997365(v=exchg.80).aspx
c. Add Query BeforeRefresh and AfterRefresh event handlers to instrument logging clues in the Worksheet to determine what the user is doing. In particular, see &quot;XL2000: How to Use the Query BeforeRefresh and AfterRefresh Events&quot;: https://support.microsoft.com/en-us/help/213187/xl2000-how-to-use-the-query-beforerefresh-and-afterrefresh-events
d. Write an Excel add-in that caches their SQL calls.
UPDATE 1/19/2018
I'm still dealing with high ASYNC_NETWORK_IO issues with Excel users.  I believe I have found more potential causes of this, but these are just theories at this point:

If you have multiple tables in the same spreadsheet, and the last table to the right has more rows than the tables to the left, this seems to cause Excel to &quot;blink&quot; a lot.  Correspondingly, in SolarWinds Database Performance Analyzer, queries on this spreadsheet that return the most rows and columns also have the highest ASYNC_NETWORK_IO problems. (Next steps for me are to reproduce this issue, but I'm not sure it's worth my time as I persuaded one user that they didn't need that huge result set at all for now.)

I found a comment on Experts Exchange ( https://www.experts-exchange.com/questions/25049176/Slow-queries-excuted-throug-network-ASYNC-NETWORK-IO.html ) where someone said 5,000 row results or higher are notorious for causing problems.  This appears to actually be a recommendation raised internally by Microsoft Dynamics NAV application, which is notorious for causing SQL Server instability due to high ASYNC_NETWORK_IO waits.  The error message the application raises is, &quot;The number of records in the table exceeds the maximum number of 5000. Set a filter to decrease the number of records in the table. Exporting too many records at once can affect system performance.&quot;  But this is just a guess.

For SSIS Excel Destination tasks, if the user does not have permission to write to Temporary Internet Files folder, then it might just hang: https://stackoverflow.com/a/23523954/1040437  (Again, just a theory as I use this to try to collate all possible theories into one place.)