3

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:

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

High Async Network IO from Excel

UPDATE

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

  1. The file is on a Network-Attached Storage Drive shared among a work group.
  2. I looked into various Network Interface Card powersaving feature issues that could be causing Excel to think the file handle is lost (???)
  3. "Refresh data when opening the file" is checked under Connection Properties
  4. 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

Improve this question
10
  • Wow, interesting. You're positive it isn't actually doing any work overnight (macros or something)? Can you convince the user to close the spreadsheet when they leave? If not, I'd be tempted to just manually kill their spid at 6pm ("oops, not sure what happened"), then (if they complain) ask them to manually refresh when they come in in the morning. That assumes Excel won't retry on its own, which it just might, depending on workbook settings. Is this a spreadsheet you have a copy of? Or can get a copy of? Changing data connection settings might fix it.
    – BradC
    Commented Jul 11, 2017 at 18:16
  • "Wow, interesting" - my thoughts exactly. The user typically is good about closing down for a day, because they don't want to look at stale data the next day (implications costing him millions of dollars if making decisions on wrong data...). I'm trying to find a more permanent solution. The other half of the problem is it's a direct query on a DB table... I am thinking if I put it in a sproc and intermediately dump to a temp table and return that, the lock will go away.
    – John Zabroski
    Commented Jul 11, 2017 at 18:48
  • You can put your database in a single user with rollback immediate and then put in in multi user on a sceduled base, for example, at 7 PM for sure
    – sepupic
    Commented Jul 11, 2017 at 19:39
  • The only downtime window is Saturdays
    – John Zabroski
    Commented Jul 11, 2017 at 20:01
  • 2
    Is it actually causing a problem? normally that wait type indicates that SQL has finished processing everything and is just waiting for the client to read all the results. See: sqlskills.com/help/waits/async_network_io. If it's not actually causing any problems then I'd be inclined to just ignore it
    – Greg
    Commented Jul 25, 2017 at 22:17

1 Answer 1

Reset to default
3

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:

  1. 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)
  2. 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:

  1. 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.

  1. 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 "XL2000: How to Use the Query BeforeRefresh and AfterRefresh Events": 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:

  1. 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 "blink" 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.)

  2. 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, "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." But this is just a guess.

  3. 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.)

Improve this answer
1
  • I ran into a real world issue where I saw, using Solarwinds Database Performance Analyzer, a blocking chain with an ASYNC_NETWORK_IO at the head of the chain, all due to Excel.
    – John Zabroski
    Commented Nov 23, 2018 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.