當使用者在 Excel 打開的情況下鎖定電腦時 ASYNC_NETWORK_IO 高

我有一個使用者每天早上 8 點進來，下午 6 點離開，每天我都在 SolarWinds DPA 中看到相同的奇怪模式。

當使用者鎖定螢幕時，Excel 似乎正在刷新查詢表。我的問題是：

1. 有沒有辦法阻止 Excel 這樣做？我不能是唯一一個與此類問題作鬥爭的 DBA。
2. 有什麼辦法可以在我的機器上重現問題嗎？
3. 幫助查找 Google 關鍵字以搜尋已知問題…我嘗試過不成功

更新

我突然想到這裡可能還有其他因素在起作用，所以這裡是：

1. 該文件位於工作組共享的網路附加儲存驅動器上。
2. 我調查了可能導致 Excel 認為文件句柄失去的各種網路介面卡省電功能問題 (???)
3. 在連接屬性下選中“打開文件時刷新數據”
4. 這可能發生在虛擬機上。

我發現另一個有類似問題的使用者：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如果發送給客戶端的結果從未在任何地方假離線（例如，通過ORDER BY強制排序的子句tempdb），則可能導致數據庫爭用問題。

如果 Excel 出現此問題，則問題可能是由於在後台執行緒上刷新 Excel 數據庫連接，並且使用者在後台刷新期間同時鎖定了他們的電腦。當後台執行緒完成時，電腦被鎖定。問題是在 Win32 中，鎖定的電腦意味著使用者會話對像不可用。當使用者會話對像不可用時，後台執行緒無處可返回結果，它只是掛起，等待 Excel.exe 讀取結果。因為結果永遠不會在任何地方假離線，ASYNC_NETWORK_IO鎖會導致任何先前獲得的鎖上的活鎖*。本身ASYNC_NETWORK_IO是無害的。造成損壞的是鎖鏈*。

總結起來，這個問題可能在以下兩種情況下發生：

1. ASYNC_NETWORK_IO 由於讀取器速度慢而存在（在我的情況下，由於 Win32 使用者會話對像不可用，慢速讀取器被活鎖）
2. 結果永遠不會假離線到某個中間對象，隱式（例如，在基礎表上ORDER BY觸發排序tempdb，因為數據太大而無法在記憶體中完全排序）或顯式（INSERT結果到tempdb表中，SELECT在 tempdb 表上執行，這會釋放活鎖）。

兩種選擇：

1. 如果您無法控制客戶端的緩慢性？

一種。將查詢結果寫入臨時表。將臨時表返回給使用者。

灣。另外，不要忘記在儲存過程的頂部設置 NOCOUNT ON，以避免由於 DONE_IN_PROC TDS 消息被發送到客戶端而導致往返延遲。

2. 如果您確實可以控制客戶端的緩慢性？

一種。檢查 NIC 是否配置錯誤，或者客戶端是否使用 VM 並且 VM 的 NIC 是否配置錯誤。見：https ://www.reddit.com/r/sysadmin/comments/2k7jn5/after_2_years_i_have_finally_solved_my_slow/

灣。使用 Perfmon 進行測量。Microsoft 對 PerfMon 網路輸出隊列長度使用 10 的啟發式方法。請參閱：https ://technet.microsoft.com/en-us/library/aa997365(v=exchg.80).aspx

C。添加 Query BeforeRefresh 和 AfterRefresh 事件處理程序以檢測工作表中的日誌記錄線索，以確定使用者在做什麼。特別是，請參閱“XL2000：如何使用查詢 BeforeRefresh 和 AfterRefresh 事件”： https: //support.microsoft.com/en-us/help/213187/xl2000-how-to-use-the-query-beforerefresh-刷新後事件

d。編寫一個記憶體其 SQL 呼叫的 Excel 載入項。

2018 年 1 月 19 日更新

我仍在處理 Excel 使用者的高 ASYNC_NETWORK_IO 問題。我相信我已經找到了更多潛在的原因，但這些只是目前的理論：

1. 如果您在同一個電子表格中有多個表格，並且右側的最後一個表格的行數比左側的表格多，這似乎會導致 Excel 經常“閃爍”。相應地，在 SolarWinds Database Performance Analyzer 中，此電子表格上返回最多行和列的查詢也具有最高的 ASYNC_NETWORK_IO 問題。（對我來說，接下來的步驟是重現這個問題，但我不確定這是否值得我花時間，因為我說服了一位使用者他們現在根本不需要這麼大的結果集。）
2. 我在專家交流 ( https://www.experts-exchange.com/questions/25049176/Slow-queries-excuted-throug-network-ASYNC-NETWORK-IO.html )上發現了一條評論，其中有人說 5,000 行結果或更高因引起問題而臭名昭著。這似乎實際上是 Microsoft Dynamics NAV 應用程序內部提出的建議，該應用程序因高 ASYNC_NETWORK_IO 等待導致 SQL Server 不穩定而臭名昭著。應用程序引發的錯誤消息是，“表中的記錄數超過了最大數量 5000。設置過濾器以減少表中的記錄數。一次導出太多記錄會影響系統性能。” 但這只是一個猜測。
3. 對於 SSIS Excel 目標任務，如果使用者無權寫入 Temporary Internet Files 文件夾，則它可能會掛起：https ://stackoverflow.com/a/23523954/1040437 （同樣，我使用這個只是一個理論試圖將所有可能的理論整理到一個地方。）

引用自：https://dba.stackexchange.com/questions/179585