Integration Services 高效能策略

簡介

效能是資料整合解決方案裡關乎成敗的重要因素。效能不僅決定了是否能夠有效的滿足當前的資料整合需求，而且影響了解決方案的可擴充套件性，以及對未來商業需求變化的適應能力。例如，效能問題可以增加資料容量，減少批處理視窗，擴充套件資料複雜性，形成資料的質量標準。

儘管效能問題在每個專案中都扮演著至關重要的角色，諷刺的是，人們最容易忽視它，往往把它放在開發週期的最後環節才加以考慮。而到了這個時候，設計都已經基本定型了，交付日期也日益臨近，客戶越來越期盼獲得一個良好的產品，你已經沒有機會再去進行大的設計調整了。

對一個現有的設計進行效能最佳化當然也很有意義，但是你會發現，一旦設計細節確定了，你可以進行最佳化的餘地也就被限制了。儘管這些事後的最佳化工作是不可避免的，但你仍然可以透過在設計過程中就加入整體的效能策略，來儘量減少最後再進行效能最佳化的麻煩。這些效能策略可以幫助你在整個開發週期內做出更優的設計決策，在可管理的時間和計劃內提交出效能更好的解決方案，從而節省了專案的寶貴時間。

Microsoft® SQL Server™ 2005 Integration Services (SSIS)，是一個全功能的資料整合引擎和開發環境，用來建立高效能的資料解決方案。它提供了一個用於資料整合的效能策略，可以透過調整四項效能元素——操作、容量、應用和區域，幫助你設計更好的SSIS解決方案。

效能建模

影響特定資料整合解決方案的因素有很多，一個成功的效能策略需要能夠照顧到各種可能的情況，同時也要能夠提供可以具體執行的詳細資訊。這一點恰恰是SSIS效能策略的目標：提供了可以幫助訪問資料負載的指導，確定影響資料整合效能的主要因素，最大化各種效能最佳化技術的效果。以下是效能策略中關鍵的四個效能元素：

◆操作 – 確認潛在的最佳化可能性。首先，把你的整合解決方案分解成特定任務或者工作單元，然後對每個任務中所有的資料操作進行完全的定義。這些操作中的一部分很好確認，而另一些有關最佳效能最佳化機會的操作就不是很容易確認了。
◆容量 – 完全定義了這些操作後，接著確認流程中每一步程式預期的資料容量，明確這些容量是如何影響資料操作效能的。這也是確認資料容量如何隨時間進行預期變化的好機會。
◆應用 – 對操作和容量進行規格說明，選擇最適合的SQL Server應用或技術。永遠會有很多的方法可以完成特定的任務；關鍵是確認哪一個應用可以花費最小的代價，而為你提供足夠的功能。
◆區域 – 最後一個因素就是區域，指的是資料整合操作的執行時環境。Location排在最後是因為其它效能影響因素的引數會影響到你對執行時環境的最佳設定。

一個簡單的記憶方法，OVAL，可以幫助你記住這四個影響效能策略的元件。

下面的章節展示了這些方法所帶來的好處，你可以使用這些元件來最佳化一個簡單的資料整合方案。在這一情景下，你在使用SQL Server Integration Services來聚集一個5000000條記錄的普通文字檔案，並且把結果記錄讀入到目標的SQL Server RDBMS表中。

操作

操作是效能策略中關鍵的因素。特別強調的是需要理解所有的資料操作，這些操作是解決方案的一部分，你可以確認最佳化和調整的機會。這意味著超出了手邊顯而易見的任務，需要從資料來源到每一個資料整合操作，確認整個操作流程的順序，直到目標為止。

定義操作

示範操作如何工作，查閱表1中所列的各種操作。這一表格描述了當使用SSIS去讀取並往SQL Server 目的表中聚集普通文字檔案資料時，所包含的每一步操作步驟。

表1：操作讀取了一個普通文字檔案，將其聚合成了SQL Server資料表

最佳化操作

對於這一序列中的每一步操作，你都可以使用效能分析，來指導你尋找最佳化和提升整個過程的機會。例如，在操作2種，當SSIS從文字檔案中把資料讀入資料管道時，所有的欄位都被讀成字串型別，甚至當裡面包含整數時也是。如果你的目的是使用已命名，確定型別的列將這一檔案讀入到的SQL Server目標資料表中，資料轉換必須在資料讀取的某個過程中就進行了。

為了減少資料集的大小，提升整體資料讀取的效能，你可以在這一操作過程的早期就設定資料型別。在表1給出的例子中，對資料設定型別的最佳時間是操作2的過程中，當檔案被讀入到SSIS資料管道時。如果你不在這時設定資料型別，資料就將以其原始的字串狀態轉過所有的步驟，直到它被讀入SQL Server前才被完成了轉換。

今後再調整檔案解析，你也可以獲得操作2中SSIS快速解析模型的好處。這一模型對包含一般資料格式的簡單資料讀取使用快速的解析路由，而並不需要特定的現場解析，這一格式不包含特殊的字元，也不包含十六進位制字元。

資料型別最佳化並不僅僅適用於檔案資料來源。在SSIS資料讀取過程的早期適當的設定資料型別，能夠對所有的資料來源都帶來效能方面的好處。

這只是一個你可以最佳化資料讀取操作的例子。你可以將這一方法在整個過程中的每一步都實施，你就可以為每個效能考量確定出更好的設計方法。

容量

容量是第二個關鍵，重要的效能影響因素。高階別的容量會對系統的資源，如記憶體和CPU產生更大的影響。你越是理解容量是怎樣對你的資料操作產生影響的，你就越可以調整這些資源的應用，從而產生更有效率的操作。

定義容量

SSIS資料管道引擎使用一個快取導向的結構，可以在記憶體中高效的讀取和運算元據集。這一記憶體中處理過程的價值在於，對於資料讀取的每個步驟來說，資料都沒有必要被物理的複製和儲存。取而代之，資料管道引擎在將資料從源向目標轉移的過程中，使用快取來管理資料。

為了最佳化這個管道，你的目標是，在儘可能少的快取消耗中，傳送儘可能多的記錄，特別是在你處理大量資料容量的時候。為了達到這一目標，你必須首先對資料的長度（行數）和寬度（列數）進行分析。資料寬度基本上是各列資料型別的長度和，加上SSIS分配的固定長度的記憶體字首。資料型別越小，對記憶體的使用就越有效。

最佳化容量

當你最佳化資料容量時，你首先應該評估所有的列，這是資料整合操作的一部分。很多時候儘管只需要這些列中的一部分，卻還是將整個原始檔的資料都讀取了。不需要的列佔據了系統的資源，應當儘可能的排除掉。

另外一個要考慮的因素是，不管你的方案是否需要增量或完全的資料讀取，從容量的角度出發，完全的資料讀取是高消耗的。如果你可以用增量的資料讀取替代完全的資料讀取，你將極大的減少資料讀取操作的容量。這點在一個應用週期中容量不斷增大的情況下特別適用。

在SSIS內，你可以透過三個主要的方法來最佳化容量：收縮資料集的大小，最大化吞吐量，以及定義並行操作。

a.收縮資料集的大小 - 為了演示收縮資料集，設想下面的例子。假定這包含5000000條記錄的原始檔有100列，都是在1到999之間的數字。如果你對這些列都保留預設的字串資料型別，每個條記錄的每個列需要50Bytes。這意味著每一行計算後需要大約5000Bytes，再加上SSIS需要的記憶體字首。所以，對於一個5000000條記錄的容量，每行5000bytes，檔案的總大小約是23GB。如果你將這些列更精確的定義為2-byte大小的整型，再來考慮資料集大小間的區別。這將使每行的大小變為200bytes，總的大小就縮減為954MB，比字串資料型別節省了大約22GB。透過資料型別大小的簡單改變，你就可以大大減少要讀入記憶體的資料量。注意，以上例子也說明了資料型別之間的相對差別和收縮資料集大小節省的空間，這並不是一個定死的大小調整方針。

b.最大化吞吐能力 – 除了設定資料型別，你還能夠透過配置SSIS快取設定——如DefaultMaxBufferSize和DefaultMaxBufferRows，來最大化吞吐能力。這些設定控制了在資料讀取操作過程中建立快取的大小，以及每次傳遞的記錄行數。但是，對於調整這些引數的細節不在本白皮書內探討。

c.定義並行操作 – 最佳化容量的第三個方法是考慮如何對操作進行並行化處理，以獲得最大的效率。SSIS支援包，任務以及轉換的並行化處理。當你設計並行處理流程時，你要考慮對資料讀取操作實施怎樣的並行度。例如，如果你需要讀取並聚集多個檔案的資料到SQL Server中，你可以考慮對以下一個或多個操作並行處理：檔案讀取，資料聚集，目標資料表裝載。儘管SSIS支援所有這些操作的並行處理，你仍舊需要搞清楚系統的資源和它們並行支援的級別。你可能建立了一個高並行度的SSIS包，而隨後就意識到你的系統沒有足夠的CPU和記憶體來充分體現這些並行的優勢。

也許這個讀取5000000條源記錄的例子很簡單，這些相同的原則卻可以被直接應用到更大型和更復雜的資料讀取任務中，這個任務可以包含多個資料來源，需要複雜的資料轉換，而且必須被裝載到多個目的表中。

應用

根據定義好的一組資料整合操作以及對所需資料容量進行的評估，可以明確出應用因素，來幫助你決定那種資料整合應用最符合那些需求。

理解應用的使用

你可能會經常選擇不同的應用程式去解決資料整合問題。關鍵是要確定哪些應用可以在給你帶來足夠功能的同時，成本較低。

例如，你面臨著使用SQL Server Transact-SQL的BULK INSERT語句或BCP工具來往SQL Server中讀取文字檔案的工作，與SSIS相比，這樣可以在成本較低的情況下滿足資料讀取功能的需求。當你有較小的資料集和簡單的資料整合需求時，上述情況很可能發生，這時啟動SSIS操作的開銷就能抵消使用BCP或BULK INSERT所獲得的效能收益。

幫助你做出正確的選擇，我們把每一應用的主要功能元件列出來，以便你能夠根據主要的需求做出相應的評價。例如，以下的使用指導可以幫助你在BULK INSERT/BCP或SSIS中做出選擇。

◆BULK INSERT / BCP使用指導 – 當你的應用場景有以下的特徵時，應該使用BULK INSERT / BCP：
◆有單一的資料來源，並且是檔案形式
◆有單一的目標，並且是SQL Server
◆沒有資料轉換的需求，比如直接從源讀取到目標
◆沒有工作流程管理，注意流程管理並不適合BULK INSERT，因為它只是一個將資料讀取進SQL Server的單獨操作
◆SSIS使用指導 - 當你的應用場景有以下的特徵時，應該使用SSIS：
◆多個不同種類的資料來源和目標
◆有資料轉換需求：聚集，查詢，建立新列
◆與其他的管理任務（如郵件或FTP）相結合
◆透過工作流程管理控制多個任務和轉換的順序

評估選擇

根據使用指導，你可以更好的評估哪種應用選擇更符合你的資料需求。

在檔案讀取的例子中，你的需求不僅僅是往SQL Server中讀入5000000條記錄，還包括對這些記錄的資料聚集。考慮到這些需求，確認以下哪個方法可以完成這項任務。注意，這裡僅僅列出兩個可行的方法。

◆方案1 - BULK INSERT解決方案 – 你當然可以使用BULK INSERT去從原始檔讀取資料，並裝載到SQL Server中。但是，你還需要增加額外的操作，在把資料裝載到目標表前對其進行聚集。一種方法是使用BULK INSERT把資料放入一個暫時的表，然後使用Transact-SQL對暫時表中的資料進行聚集，再用Transact-SQL把資料讀入目標表中。
◆方案2 – SSIS解決方案– SSIS提供了一站式的解決方案，你可以在聚集資料的同時將其裝載到目標表中。並且，在SSIS中你還能新增工作流程來控制這些操作的順序，甚至對執行過程進行日誌，記錄操作執行的過程，以及進行出錯處理，找出不符合要求的記錄。

當你對比上述兩個方案時，SSIS解決方案可以不使用暫時表，並且把所有的資料讀取邏輯和工作流程裝入單一的SSIS包中。而對於方案1，你不僅需要使用Transact-SQL來對資料進行聚集，還需要把資料邏輯封裝在一個儲存過程中。所以，根據這一對比，SSIS解決方案是這一讀取-聚集案例最優的選擇，也就是可以提供足夠的功能，並且有能力集中管理工作流程。

區域

最後，你需要確認資料操作執行的區域。當你從源向目標讀取資料時，你可能會有足夠的靈活性去執行特定的操作。很有可能，你的決定會是基於一系列你必須權衡的因素，根據你特定的資料讀取需求。

定義區域選擇

一般來說，你有三個區域可以選擇：(1) 源資料伺服器，(2)目標資料伺服器，(3) 專門的解析，轉換和讀取（ETL）應用伺服器。現實環境下，你的選擇可能會受許可證的限制。但是，考慮到你的應用需求對各個區域的影響，你應該確定最適合你執行需求的區域，你甚至可能會發現有從新評估許可證策略的需要。

評估區域影響

為了說明對區域的影響，假定源資料檔案和資料讀取操作所在的伺服器，以及最後存入資料的目標SQL Server都不在一起：

1.目標資料伺服器 – 當你的目標是SQL Server時，在目標資料伺服器上執行SSIS操作會帶來很大的效能優勢。這種情況的最大好處就是可以在SSIS包中使用SQL Server Destination元件。這一元件可以最佳化在記憶體裡的資料讀取，比標準的OLE DB Destination元件獲得8%到15%的效能提升。要記住，SQL Server Destination元件不支援資料型別的轉換。最終，你需要在往目標表載入資料之前的SSIS步驟中，確認完成資料轉換的操作。這也就是，在記憶體中的資料型別和SQL Server的資料型別是直接相對的。

你當然也要了解SSIS與SQL Server在目標伺服器上是怎樣爭奪資源的。為了減少爭奪，你可以為SQL Server或者SSIS設定資源分配。例如，你可以降低SSIS併發執行緒的最大數目，以避免CPU的瓶頸。總體來說，如果你的資料讀取操作需要你找出最快的方法，把資料讀入到SQL Server，那麼在目標伺服器上執行SSIS就是比較好的選擇，可以透過SQL Server的記憶體資料讀取功能獲得效能的提升。

2.源資料伺服器 – 當你在源資料伺服器上執行SSIS操作時，你可以在轉換資料並透過網路向目標載入資料前，透過減小源資料集的大小來提高效能。例如，如果你需要聚集資料，那麼輸出記錄的條目數一般會小於輸入記錄的條目數。並且，你可以透過在先前的SSIS過程中恰當的設定資料型別來減小資料集的大小。

想要獲得這一好處，你必須接受一些折中處理。如果其他的應用程式執行在源伺服器上，SSIS可能會和這些應用爭奪機器資源。如果資源比較有限，你很可能會犧牲掉你所獲得的效能呢個提升。還有，如果你的目標伺服器是SQL Server，你將不能使用經過最佳化的SSIS SQL Server Destination元件的記憶體資料讀取功能。SQL Server Destination元件只有當SSIS資料操作在目標SQL Server伺服器上執行時才會發生作用。除此之外，你必須要使用標準的OLE DB destination元件，然而，它沒有記憶體資料讀取功能所帶來的好處。

考慮到這些犧牲，如果你能把大量資料記錄減少成為較小的資料集，當你關心網路效能瓶頸的時候，在源資料伺服器上執行SSIS操作是有好處的。

3.專門的SSIS伺服器 – 當你有多種不同型別的資料來源和目標，對較大量資料進行復雜轉換時，使用專門的SSIS伺服器是最佳的方案。在這一專門的伺服器上，SSIS可以使用全部機器資源而不必跟其他應用爭搶，這樣會獲得最佳的優勢。

也有不好的方面，你當然也無法使用SSIS SQL Server Destination元件。而且，從網路方面看，你必須要傳送資料兩次：從源伺服器到SSIS伺服器，然後再從SSIS伺服器到目標伺服器。如果你的網路貸款可以輕鬆的支援這些傳送，這一方案提供了最大的SSIS靈活性。它也適合於那些有複雜轉換需求的情況，這時SSIS不會反過來影響其他應用，或SQL Server本身。

總結

當你設計資料整合解決方案時，你可以把效能調整看成一種藝術，或科學，但你更關注的是結果。為了幫助你更好的完成效能調整的工作，本文中描述的效能策略提供了一個方法框架，使您可以建立出高效能的資料整合解決方案。

透過指導你對操作，容量，應用和區域進行評估，這一策略可以幫助你預見並最佳化各種大規模的資料讀取情景，無論資料簡單還是複雜。

希望瞭解更多資訊，請訪問以下網址：

關於作者

Elizabeth Vitt, Intellimentum

Elizabeth Vitt在商業智慧領域擁有超過十年的商業系統開發，專案管理，諮詢和培訓經驗。她的業界經歷包括為零售、製造和金融服務等行業實施商業智慧解決方案。她在資料倉儲，ETL和OLAP設計和實施方面還有著豐富的教學經驗。Vitt女士是微軟有關商業智慧產品官方課程內容的特約作者，由MSPress出版了《商業智慧：更快更好的做出決策》。伴隨著SQL Server 2005的正式發表，Vitt女士幫助早期的客戶成功的實施了SQL Server 2005方案。