優化操作

對于這一序列中的每一步操作，你都可以使用性能分析，來指導你尋找優化和提升整個過程的機會。例如，在操作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本身。

總結

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

通過指導你對操作，容量，應用和區域進行評估，這一策略可以幫助你預見并優化各種大規模的數據讀取情景，無論數據簡單還是復雜。

希望了解更多信息，請訪問以下網址：http://www.microsoft.com/sql/

關于作者

Elizabeth Vitt, Intellimentum

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