sql server 效能調優 資源等待之網路I/O

標籤：執行個體   預設   協助   輸入   extern   資料打包   msdn   一個   解決   

原文:sql server 效能調優 資源等待之網路I/O

一.概述 

　　與網路I/O相關的等待的主要是ASYNC_NETWORK_IO，是指當sql server返回資料結果集給用戶端的時候，會先將結果集填充到輸出緩衝裡(ouput cache)，同時網路層會開始將輸出緩衝裡的資料打包，由用戶端接收。如果用戶端接收資料包慢，sql server沒有地方存放新資料結果時，這時任務進入ASYNC_NETWORK_IO等待狀態。

　　1. 從執行個體層級查看ASYNC_NETWORK_IO

 　　

　　 平均耗時： 46366950.0/43014737.0=1.077ms， 最大等待時間:~40秒。

　　2. 重現ASYNC_NETWORK_IO等待

 　　　　為了示範ASYNC_NETWORK_IO 現象，我們需要輸出一個大結果集。當sql server記憶體完全被使用後，大量的資料填充到緩衝裡，此時sql server沒有地方存放新資料結果，進入等待狀態。

-- 一次查詢100000條資料輸出到用戶端SELECT TOP 100000 * FROM PUB_Stock WITH(nolock)

　　監聽到的會話如下：

　　

　　使用dbcc inputbuffer 查詢64結果如下:

 　　　

　　3.分析與解決

　　　　這個等待出現的問題強調以下幾點:

　　　　(1) 用戶端沒有把資料及時取走，調整sqlserver 的配置一般情況下是不是有什麼大的協助。

　　　　(2) 網路層可能是問題的原因。  解決：1是減少對用戶端大量資料輸出。 2是加大sqlserver 的network packe size，從一定程度上最佳化網路轉輸的效能，但會增加記憶體的開銷（建議小於設定小於8kb）。

　　　　network packe size是用戶端與sqlserver通訊的每個資料包大小有關係。network packe size設定的資料包存放於記憶體功能組件的connection類別裡。預設是4kb設定，輸入輸出緩衝會放在buffer pool裡，如果改成了8kb 或更大，輸入輸出緩衝會放在multi-page裡 關於記憶體可查看sql server 記憶體初探。 設定network packe size 可以由sp_configure控制。用戶端應用程式可以覆蓋此值如在.net 裡配置如下。

Data Source=(local);Initial Catalog=AdventureWorks;"Integrated Security=SSPI;Packet Size=512

  　　示範將 net work packe size設定成6050位元組

USE AdventureWorks2012 ;  GO  EXEC sp_configure ‘show advanced options‘, 1;  GO  RECONFIGURE ;  GO  EXEC sp_configure ‘network packet size‘, 6500 ;  GO  RECONFIGURE;  GO 

 　　也可以能過介面來配置

　　

　　　　(3) 應用程式端效能問題，也會導致sql server裡的ASYNC_NETWORK_IO等待。

　　　　　　sqlserver 的網路層將結果集打包好發向用戶端以後，要等到用戶端確認收到，才會接著發下一個包。

　　　　(4) 分布式鎖

　　　　　　如果長時間看到ASYNC_NETWORK_IO，同時在sqlserver內部又造成了阻塞，並且該等待持續了很久，就該懷疑是否是分布式的死結。

　　總結：當遇到ASYNC_NETWORK_IO等待，需要檢查應用程式自己的健康情況，也要檢查應用是否有必要向sql server 申請這麼大的結果集返回，一般來講sqlserver 本身沒有什麼問題。

二. 其它網路I/O等待

　　這裡還有其它幾個NET_WAITFOR_PACKET，PROXY_NETWORK_IO，EXTERNAL_SCRIPT_NETWORK_IOF。
　　2.1 NET_WAITFOR_PACKET： 在msdn中解釋是 網路讀取過程中，串連正在等待網路資料包時出現。

　　　　實際級等待如所示：
　　　　
　　2.2 後面二個proxy_network_io，external_script_network_iof。在生產環境下沒有資料。在msdn中也沒有找到相應解釋。只能通過字面含義去解釋。

sql server 效能調優 資源等待之網路I/O