SQL Server wait type ASYNC_NETWORK_IO

 SQL Server wait type ASYNC_NETWORK_IO

可能造成的問題:

1.造成本身查詢緩慢，因為整體執行時間增加

2.造成無法釋放鎖定，形成封鎖Blocking，導致其他連線必須等待

常見原因

1.回傳大的結果集

解決方法: 改在SQL Server先過濾、彙總計算來減少回傳的資料量

2.應用程序獲取結果集的速度不夠快

例如:若使用ADO.NET的SqlDataReader可以控制每一筆資料的回傳後執行個總商業邏輯，例如 每一筆都要寫入實體檔案、每一筆要傳送到另一台主機或應用程式、或每一筆都要等待使用者輸入，都可能導致擷取速度下降

解決方法: 

使用WHILE/FOR loop快速將資料擷取放入記憶體後再處理商業邏輯

或

ADO.NET預設使用DataSet與DataTable會將資料擷取完成後資料才能使用

3.客戶端應用機器承受壓力(I/O, memory, or CPU)

For example:

• 100% CPU utilization
• Insufficient memory (all memory is consumed)
• Slow I/O (perhaps the application writes results or logs)

解決方法:

• Stop other applications from running.
• Fix any code issues in those applications.
• Upgrade the hardware on the system if the applications have been tuned fully.

4.網路介面卡或網路 (NIC/Network)

• Network delays are typically caused by the following issues:
• Network adapter driver issues
• Network filter drivers issues
• Misconfigured or faulty firewalls
• Routers issues
• Overloaded networks due to traffic (less common)

Resolution: To diagnose these issues, you can collect a network trace and look for packet resets and retransmits. You can then resolve the network-related issue to eliminate packet resets/retransmits.

Troubleshoot slow queries resulting from ASYNC_NETWORK_IO - SQL Server | Microsoft Learn