SQL Server Integration Services的10大最佳實踐

原文出處：https://blogs.msdn.microsoft.com/sqlcat/2013/09/16/top-10-sql-server-integration-services-best-practices/ 譯：多少人聽說過“SQL Server Integration Servic ...

原文出處：https://blogs.msdn.microsoft.com/sqlcat/2013/09/16/top-10-sql-server-integration-services-best-practices/

譯：

多少人聽說過“SQL Server Integration Services(譯註：以下簡稱SSIS)不能擴展”的傳說？我們要反問的是“你的系統是否有必要擴展到每秒能夠處理超過450萬行交易記錄？”SSIS是可以適應最極端環境的高性能ETL平臺。並且正如在SSIS ETL性能世界記錄所記載，SSIS可以處理每秒450萬規模的銷售交易記錄。

1.SSIS是基於記憶體的數據管道，因此請確保所有的轉換髮生在記憶體中。

在SQL Server的功能中加入SSIS的目的是提供一個有彈性且穩定的數據管道，以便於在記憶體中高效地執行逐行計算和數據校驗。

SSIS本身的數據轉換應該在記憶體中處理，然而數據抽取和載入階段卻會涉及磁碟(會產生相應的讀寫)。一旦數據轉換操作溢出到磁碟上，ETL性能將會有顯著下降。為了確保所有的轉換都能夠在記憶體中處理，請仔細構建包以分割並過濾數據。

確認包是否駐留在記憶體中的一個好辦法是檢查SSIS緩存池相關的性能計數器，它的初始值是0，超過0就意味著SSIS引擎開始切換到磁碟上。更多詳情，請參考SSIS性能計數器二三事。

2.根據資源使用的情況規劃容量

SSIS的設計目標是在記憶體中高速逐行處理大量數據。因此，理解資源使用情況是非常重要的，比如CPU，記憶體，I/O，以及網路消耗。

CPU限制

當SSIS正在運行的時候，請設法搞清楚SSIS用了多少CPU，SQL Server總共用了多少CPU。如果SQL Server和SSIS安裝在同一臺機器上，那麼後者就顯得十分重要。因為一旦兩者之間存在資源競爭，SQL Server往往會勝出——這會導致SSIS的數據轉換操作溢出到磁碟上，而這會降低數據轉換性能。

你感興趣的性能計數器應該是Process / % Processor Time (Total)。衡量sqlservr.exe和dtexec.exe的這個計數器值。如果SSIS無法驅使CPU達到100%負載，這可能表明：

應用程式競爭：例如，SQL Server占用了更多CPU資源，使CPU不能為SSIS所用。
硬體競爭：一個常見的場景是你的磁碟I/O不夠好或者沒有足夠的記憶體，無法應付要處理的數據量。
設計上的限制：你的SSIS包沒有利用到並行設計，並且/或者包使用了太多單線程任務。

網路限制

SSIS會盡你網路所能地傳輸數據。因此，搞清楚你的網路拓撲結構是很重要的，同樣重要的是確保從源到目標之間的路徑具有低延遲和高吞吐。

以下性能計數器可以幫你調整你的網路拓撲結構：

Network Interface / Current Bandwidth: 這個計數器會估算當前帶寬。
Network Interface / Bytes Total / sec：這是每個網路適配器上發送和接收的位元組數。
Network Interface / Transfers / sec：顯示每秒發生多少網路傳輸操作。如果這個值接近每秒40000IO，那麼請考慮增加網卡併在網卡間使用teaming技術。

這些計數器可以讓你分析你的系統是否充分利用帶寬。理解這點可以讓你通過使用千兆網卡，增加網卡數量，或者新增網路地址以恰當地規劃網路容量。

I/O限制

如果你能確保SSIS已經最小化磁碟寫入操作，SSIS只會在從源讀取數據以及寫入數據時接觸到磁碟。而如果你的I/O性能太慢，讀寫(特別是寫入)操作將成為性能瓶頸。

由於優化I/O超出了本文的範疇，請參考前期部署最佳實踐。請註意，I/O系統的規格不只有它的大小(例如“我需要10TB”)——也包括了它的速度(例如“我需要達到每秒20000IO”)。

記憶體限制

你需要搞清楚一個很重要的問題“我的包到底需要多少記憶體？”

對此，關鍵的性能計數器有：

Process / Private Bytes (DTEXEC.exe)：當前SSIS使用的記憶體大小。這部分記憶體不能與其它進程共用。
Process / Working Set (DTEXEC.exe)：SSIS分配的總記憶體大小。
SQL Server: Memory Manager / Total Server Memory：SQL Server分配的總記憶體大小。由於SQL Server可以通過AWE API分配記憶體，只有這個性能計數器可以準確反映SQL Server使用的記憶體大小。要更好地理解SQL Server記憶體分配機制，請參考Slava Ok的博客。
Memory / Page Reads / sec：這代表系統的記憶體壓力。如果這個指標持續在500以上，說明系統存在記憶體壓力。

3.源系統抽取速度基線

你得搞清楚從源系統抽取數據的速度。畢竟，SSIS不可能優化得比源系統還快——比如說，你不可能比讀取數據更快地進行數據轉換。

你可以通過創建一個帶“Row Count”數據流組件的包來測量源系統的速度。

你可以從命令行(DTEXEC)執行這個包並測量它的完成時間。也可以用SSIS日誌輸出來準確計算執行時間。你需要的速度，就是每秒傳輸了多少行：

Rows / sec = Rows Count / Time

根據這個基準速度，我們就知道可以多快地從源抽取數據——這也是你轉換數據的速度極限。要計算出這個速度，你可以嘗試以下方法：

升級驅動和驅動配置：針對網路，數據源，和磁碟I/O，請確保你的驅動已經升級到最新版本。通常你的伺服器預設網路驅動並未針對網路進行過精確的優化，當吞吐量大的時候這會導致性能下降。要註意的是，對於64位系統，你可能在設計時用的卻是32位的驅動。請確保在運行時你用的是64位驅動。
多重連接：為了剋服驅動的限制，你可以嘗試利用多重連接從數據源抽取數據。正如數據源可以處理併發連接一樣，同時進行多重抽取可以提高吞量。如果併發導致了鎖定或者阻塞問題，可以考慮分割數據源，並使你的包同時從多個分區抽取以分攤負載。
多網卡：如果網路是性能性能瓶頸，而你已採用了千兆網卡和路由，那麼一個可能的解決方案就是在每個伺服器上都使用多塊網卡。要註意的是多網卡環境需要仔細配置，否則有可能會發生網路衝突。

4.優化SQL Data Source，Lookup transformations，和Destination。

當你在SSIS中執行SQL語句(正如上圖所示的Data access mode對話框)，是否在查詢數據源時實現Lookup邏輯，或者修改某些表，一些標準的優化手段可以顯著地提高性能表現:

使用NOLOCK或者TABLOCK表提示以避免鎖定開銷。
為了優化記憶體消耗，只SELECT你確實需要的列。如果你SELECT一張表的所有列(例如，SELECT * FROM)你可能會浪費記憶體和帶寬以存儲和接收那些多餘的列。
儘量在源資料庫或者目標資料庫進行時間日期類型的轉換，因為在SSIS實現的話成本太高。
SQL Server 2008 Integration Servcies有個新功能Shared Lookup Cache。當用到並行數據管道時(參見下文第8點和第10點)，這個功能可以提供高速的共用緩存。
如果SSIS和SQL Server運行在同一臺伺服器上，建議選擇SQL Server Destination而不是OLE DB Destination以提高性能。
Commit Size為0的話在堆上進行批量操作是最快的，因為只提交了一個事務。如果不能為0，儘量提高這個值以減少多批次寫入的開銷。將數據插入平衡樹結構時Commit Size為0則不是個好主意——因為所有新進的行都必須一次性排序以插入目標平衡樹——並且如果你的記憶體有限，很有可能溢出到硬碟上。將數據插入堆時Batch Size為0是個比較理想的值。對於一個有索引的目標(表)，我建議測試一下將Batch Size設在100000到1000000之間。
插入數據時將Commit Size設為小於5000可以避免鎖升級。要註意的是在SQL Server 2008現在你可以在對象級別啟用/禁用鎖升級，不過要靈活調整這個設置。
對堆進行插入往往比聚集索引快。這意味著當目標表發生大量更改的時候你可能會刪除並重建索引，你可以驗證一下保留索引和刪除並重建索引兩種方法的插入性能。
採用Partition和分區的SWITCH命令。例如，將數據載入只有單個分區的工作表，重建索引和約束後再將它SWITCH到主(目標)表。
請參考SQL性能團隊的另一個建議用Lookup優化性能。

5.調整網路

一個關鍵的屬性是連接的網路包大小。預設情況下這個值是4096位元組。這意味著每4KB數據必須封裝為一個新的網路包。正如SqlConnection.PacketSize Property中所述在.Net Framework類庫中，增加網路包大小可以提高性能，因為傳輸大數據集的時候這意味著更少的網路讀寫操作。

如果你的系統天生就是事務性的，有很多小數據量的讀/寫，那麼減小這個值會提高性能。

既然SSIS主要用於移動大量數據，你可能希望最小化網路開銷。這意味著32767是最快的選項。也可以在伺服器層面用sp_configure配置網路包大小，然而你不應該這麼做。將網路包配置為32767以外的值，DBA可能有他們的考慮。作為替代方案，你可以在Connection Manager里如下覆蓋伺服器設置。

另一個網路優化技巧是在操作系統層面採用Network Affinity。在高吞量的情況下，有時你可以通過這個辦法提高性能。

單從網路來看，你可能需要與你的網路管理員協作啟用Jumbo Frames(巨型幀)以便將幀負載能力從1500位元組提高到9000位元組。採用這種方法，將極大地減少移動大數據集所需的網路操作。

6.數據類型——沒錯，回到數據類型！——理性點。

在本文的10點建議中，這可能是最顯而易見的。然而，這是如此重要所以需要專門作為一點建議。請遵循以下準則：

使數據類型儘可能小以節約記憶體。
切勿執行代價高昂的類型轉換——這隻會降低性能。匹配源和目標的數據類型並顯式指定必要的類型轉換。
當你用到money，float，或者decimal類型時留意精度問題。同時，要註意money比decimal更快，而且在精度方面money也比float更讓人放心。

7.改變設計

SSIS比較擅長一些事情，而其它事情用別的工具更方便。你必須基於效率和對問題的瞭解來選擇ETL工具。為輔助你的選擇，請註意以下幾點：

除非必要，否則切勿在SSIS中排序。為了進行排序，SSIS會分配足以容納整個(需要移動的)數據集大小的記憶體。請儘量在數據進入數據流之前就提前排好序。相對於在SSIS中排序，不如用帶ORDER BY子句的SQL語句去排序大型數據集——然後將Data Source的輸出標記為Sorted(有序)即可。
有些時候用T-SQL操作會比在SSIS中處理數據更快。作為一般性原則，任何基於集合的操作在T-SQL中總是比較快，因為問題可以轉化為SQL Server擅長解決的關係代數函數。並且，SQL Server查詢優化器對基於集合的操作自動採用了高並行處理和記憶體管理機制——在SSIS中你需要手工去實現。典型的集合操作包括：
- 基於集合的UPDATE語句——比逐行處理的OLE DB調用更高效。
- 像GROUP BY和SUM這樣的聚合計算。這用T-SQL計算通常會比SSIS在記憶體中的計算更快。
分支檢測是一種更改已有行而不是刷新整張表的技巧。為實現分支檢測，你可以採用更改檢測機制，例如SQL Server 2008的Change Data Capture(CDC)功能。如果無法採用這樣的功能，你不得不比對源數據與目標數據以實現分支檢測。這可能是個代價高昂的操作，需要維護特定的索引並計算校驗和。通常，刷新整張表會比較快。一個經驗是一旦某個表超過10%的行需要更改，那麼通常簡單地刷新這張表會比實現分支檢測更快。

8.分割問題

可伸縮計算的一個原則就是分而治之。這使你可以更容易地處理問題的規模，利用並行處理以更快地解決問題。

對ETL設計來說，你可能會將源數據分割成大小相同的小數據塊。這一點很重要，因為如果你的數據塊大小有差異，最後你總得等待某個進程完成。比如說，如下圖，當4個進程分別運行在大小相同的數據塊，4個進程會同時完成2008年1月份數據的處理並開始處理2008年2月份的數據。但對於大小有差異的分割方式，前面的3個進程將同時完成數據處理然後等待第4個進程，這得花費更多時間。總的運行時間將取決於最大的數據塊。

為創建相同大小的分區範圍，可以考慮用時間區間和/或其它維度(例如地理信息)作為分割機制。如果你的主鍵是個自增列或者其它自增值，你可以用餘函數。如果你沒有比較合適的列用於分區，可以為每一行計算一個哈希值然後基於這個哈希值進行分區。

一些關於分區的提示：

在目標表應用分區。這樣你可以並行地運行一個包的多個版本，以便將數據插入同一張表的不同分區。用了分區，SWITH就是你的好朋友。它不僅增高並行載入的速度，也使你可以更快地傳輸數據。請參考SQL Server線上文檔使用分區切換高效傳輸數據。
如上文所暗示的，你的包需要接受一個參數以指定它要抽取哪個分區。這樣一來，你可以以不同的參數同時並行執行同一個包，那麼你可以從並行執行中獲得更快的速度。
從命令行，你可以用“START”命令多次執行包。你可以在SQL Server最佳實踐找到一個簡單的代碼示例。

9.最小化日誌操作

當你插入數據到SQL Server資料庫，儘量最小化日誌操作。當數據插入到完整日誌模式的資料庫，由於每一行都會先插入日誌，日誌會快速增長。

因此，設計SSIS包時，考慮以下幾點：

嘗試以批量模式而不是逐行模式執行數據流。通過批量導入模式，你可以最小化插入日誌的操作。這個調整也可以優化其它插入操作的底層磁碟I/O，並最小化日誌寫入造成的性能瓶頸。
如果你需要執行刪除操作，將你的數據構造成可以TRUNCATE的形式而不是DELETE。後者會在日誌中為刪除的每一行放一條記錄。而前者會簡單地移除全部數據同時在日誌做很小的記錄表明TRUNCATE發生過。與一般的觀點相對地，TRUNCATE語句可以包含在事務中。
利用SWITCH和分區。如果分區需要被移除，你可以用SWITCH語句(移入一個新分區或者移除最老的分區)，這也是最小化日誌操作的語句。
使用DML語句的時候要謹慎，如果你將DML語句混在INSERT語句中，無法最小化日誌操作。

10.正確地計劃與分發

當你的問題被分割成大小可控的數據塊之後，你得考慮一下什麼時候以及在哪裡抽取這些數據塊。這是為了避免一個長時間運行的任務影響ETL數據流的總運行時間。

解決執行順序的一個好辦法就是創建一個優先順序隊列然後執行一個包的多個實例(分別帶有不同的參數)。這個隊列甚至可以簡單到就是SQL Server的一張表。每個包都得在控制流包含一個迴圈模板：

1．從隊列中找出一個要抽取的數據塊：