效能調整 - network packet size 設定討論與調整效益

本篇我們來討論關於SQL Server上的Network Packet Size的設定值，主因由於我的上一篇文章中發現此值的調整會影響到系統的運作，所以我們藉由本篇來說明並探討此設定值。

此設定值預設值為4096(Byte)，如同下列的說明，在傳送或接收大量的文字與影像資料時，可以設定大於預設值的封包，將有助於改善效能，因為可以減少網路封包的傳遞，但大部份的情況下預設值已可符合。


下列文字來自MSDN：

1. 這個選項是進階選項，只有有經驗的資料庫管理員或通過認證的 SQL Server 技術人員才可變更。
2. 如果應用程式進行大量複製作業，或是傳送或接收大量的文字或影像資料，則使用大於預設值的封包有助於改善效能，因為這樣可以減少網路讀取與寫入的作業。 如果應用程式傳送與接收的資訊量很少，可以將封包大小設定為 512 位元組，這對大部分資料傳輸而言已經足夠。
3. 在使用不同網路通訊協定的系統上，請針對最常用的通訊協定設定 network packet size。 當網路通訊協定支援大型封包時，network packet size 選項可以改善網路效能。 用戶端應用程式可以覆寫此值。
4. 您也可以呼叫 OLE DB、開放式資料庫連接 (ODBC) 及 DB-Library 函數來要求變更封包大小。 如果伺服器無法支援要求的封包大小，Database Engine 將會傳送警告訊息給用戶端。 在某些情況下，變更封包大小可能會導致通訊連結失敗，例如以下狀況：
   
   Native Error: 233, no process is on the other end of the pipe.

實驗一：
我們透過簡單的Ping的指令來嘗試確認如果透過大封包的傳遞時，是否有效的改善效能。

1、ping www.microsoft.com -l 32767 -> 平均約要34ms。
2、ping www.microsoft.com -l 32767 -f -> 由於指定不切割封包，所以無法正常傳遞。
3、ping www.microsoft.com -> 平均時間約要8ms的時間即可。

此篇測試你可以看到在少量資料進行傳遞時，設定大封包時，並不會有所幫助。


實驗二：
我們透過SQL Server Management Studio來進行查詢的測試，我們在連線前，先在網路封包大小的部份份別設定成4096(Byte)與32767(Byte)來進行比較。

測試語法，我們透過DMV查詢並依CPU使用量最高進行排序，詳細的語法可以透過我的另一篇文章進行。

SQL Server - 如何透過DMV進行效能評估與監控
http://caryhsu.blogspot.tw/2012/11/sql-server-dmv.html

1、透過預設值4096(Byte)進行查詢

 2、將設定值更改至32767進行

從二張圖我們可以看出在傳輸的資料量約在7129K左右，但在將封包的傳輸量設定至較大時，在傳遞次數上就從原先的1746降低至219，而總執行時間也從216(ms)降至187(ms)，所以在傳輸較大量的資料時，更改此設定值的確可以提升些許效能。


總結：
從上述的二個實驗來看，在大量資料量的傳輸上，更改網路封包大小的設定值，的確是可以些許的提升效能，但是我們要怎麼動態的調整此設定值，根據官方的文件上說明，我們可以從Application Level進行(這也是官方推薦的方式)，只需要在連線字串上加入Packet Size的關鍵字設定即可，範例如下：

ConnectionString：
Server=myServerAddress;Database=myDataBase;User ID=myUsername;
Password=myPassword;Trusted_Connection=False;Packet Size=4096;



參考連結：

• SQL Server連線通訊問題處理(Communication link failure)
  http://caryhsu.blogspot.tw/2015/06/sql-servercommunication-link-failure.html
• Tabular Data Stream Protocol
  https://msdn.microsoft.com/en-us/library/aa174503(SQL.80).aspx
• SQL Server TDS or Network Packet Size
  https://sqlconsultant.wordpress.com/2010/08/15/sql-server-tds-or-network-packet-size/
• 設定 network packet size 伺服器組態選項
  https://msdn.microsoft.com/zh-tw/library/ms177437.aspx
• Network Packet Size: to Fiddle With or Not to Fiddle With
  http://www.sqlsoldier.com/wp/sqlserver/networkpacketsizetofiddlewithornottofiddlewith
• Performance Best Practice: Network Packet Size
  http://microsoft-ssis.blogspot.tw/2013/04/performance-best-practice-network.html
• SqlConnection.ConnectionString Property
  https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

關鍵字：Network Packet Size、Performance Best Practice