優(yōu)化操作

對(duì)于這一序列中的每一步操作，你都可以使用性能分析，來(lái)指導(dǎo)你尋找優(yōu)化和提升整個(gè)過(guò)程的機(jī)會(huì)。例如，在操作2種，當(dāng)SSIS從文本文件中把數(shù)據(jù)讀入數(shù)據(jù)管道時(shí)，所有的字段都被讀成字符串類型，甚至當(dāng)里面包含整數(shù)時(shí)也是。如果你的目的是使用已命名，確定類型的列將這一文件讀入到的SQL Server目標(biāo)數(shù)據(jù)表中，數(shù)據(jù)轉(zhuǎn)換必須在數(shù)據(jù)讀取的某個(gè)過(guò)程中就進(jìn)行了。

為了減少數(shù)據(jù)集的大小，提升整體數(shù)據(jù)讀取的性能，你可以在這一操作過(guò)程的早期就設(shè)置數(shù)據(jù)類型。在表1給出的例子中，對(duì)數(shù)據(jù)設(shè)置類型的最佳時(shí)間是操作2的過(guò)程中，當(dāng)文件被讀入到SSIS數(shù)據(jù)管道時(shí)。如果你不在這時(shí)設(shè)置數(shù)據(jù)類型，數(shù)據(jù)就將以其原始的字符串狀態(tài)轉(zhuǎn)過(guò)所有的步驟，直到它被讀入SQL Server前才被完成了轉(zhuǎn)換。

今后再調(diào)整文件解析，你也可以獲得操作2中SSIS快速解析模型的好處。這一模型對(duì)包含一般數(shù)據(jù)格式的簡(jiǎn)單數(shù)據(jù)讀取使用快速的解析路由，而并不需要特定的現(xiàn)場(chǎng)解析，這一格式不包含特殊的字符，也不包含十六進(jìn)制字符。

數(shù)據(jù)類型優(yōu)化并不僅僅適用于文件數(shù)據(jù)源。在SSIS數(shù)據(jù)讀取過(guò)程的早期適當(dāng)?shù)脑O(shè)置數(shù)據(jù)類型，能夠?qū)λ械臄?shù)據(jù)源都帶來(lái)性能方面的好處。

這只是一個(gè)你可以優(yōu)化數(shù)據(jù)讀取操作的例子。你可以將這一方法在整個(gè)過(guò)程中的每一步都實(shí)施，你就可以為每個(gè)性能考量確定出更好的設(shè)計(jì)方法。

容量

容量是第二個(gè)關(guān)鍵，重要的性能影響因素。高級(jí)別的容量會(huì)對(duì)系統(tǒng)的資源，如內(nèi)存和CPU產(chǎn)生更大的影響。你越是理解容量是怎樣對(duì)你的數(shù)據(jù)操作產(chǎn)生影響的，你就越可以調(diào)整這些資源的應(yīng)用，從而產(chǎn)生更有效率的操作。

定義容量

SSIS數(shù)據(jù)管道引擎使用一個(gè)緩存導(dǎo)向的結(jié)構(gòu)，可以在內(nèi)存中高效的讀取和操作數(shù)據(jù)集。這一內(nèi)存中處理過(guò)程的價(jià)值在于，對(duì)于數(shù)據(jù)讀取的每個(gè)步驟來(lái)說(shuō)，數(shù)據(jù)都沒(méi)有必要被物理的拷貝和存儲(chǔ)。取而代之，數(shù)據(jù)管道引擎在將數(shù)據(jù)從源向目標(biāo)轉(zhuǎn)移的過(guò)程中，使用緩存來(lái)管理數(shù)據(jù)。

為了優(yōu)化這個(gè)管道，你的目標(biāo)是，在盡可能少的緩存消耗中，傳送盡可能多的記錄，特別是在你處理大量數(shù)據(jù)容量的時(shí)候。為了達(dá)到這一目標(biāo)，你必須首先對(duì)數(shù)據(jù)的長(zhǎng)度（行數(shù)）和寬度（列數(shù)）進(jìn)行分析。數(shù)據(jù)寬度基本上是各列數(shù)據(jù)類型的長(zhǎng)度和，加上SSIS分配的固定長(zhǎng)度的內(nèi)存前綴。數(shù)據(jù)類型越小，對(duì)內(nèi)存的使用就越有效。

優(yōu)化容量

當(dāng)你優(yōu)化數(shù)據(jù)容量時(shí)，你首先應(yīng)該評(píng)估所有的列，這是數(shù)據(jù)整合操作的一部分。很多時(shí)候盡管只需要這些列中的一部分，卻還是將整個(gè)源文件的數(shù)據(jù)都讀取了。不需要的列占據(jù)了系統(tǒng)的資源，應(yīng)當(dāng)盡可能的排除掉。

另外一個(gè)要考慮的因素是，不管你的方案是否需要增量或完全的數(shù)據(jù)讀取，從容量的角度出發(fā)，完全的數(shù)據(jù)讀取是高消耗的。如果你可以用增量的數(shù)據(jù)讀取替代完全的數(shù)據(jù)讀取，你將極大的減少數(shù)據(jù)讀取操作的容量。這點(diǎn)在一個(gè)應(yīng)用周期中容量不斷增大的情況下特別適用。

在SSIS內(nèi)，你可以通過(guò)三個(gè)主要的方法來(lái)優(yōu)化容量：收縮數(shù)據(jù)集的大小，最大化吞吐量，以及定義并行操作。

a.收縮數(shù)據(jù)集的大小 - 為了演示收縮數(shù)據(jù)集，設(shè)想下面的例子。假定這包含5000000條記錄的源文件有100列，都是在1到999之間的數(shù)字。如果你對(duì)這些列都保留默認(rèn)的字符串?dāng)?shù)據(jù)類型，每個(gè)條記錄的每個(gè)列需要50Bytes。這意味著每一行計(jì)算后需要大約5000Bytes，再加上SSIS需要的內(nèi)存前綴。所以，對(duì)于一個(gè)5000000條記錄的容量，每行5000bytes，文件的總大小約是23GB。如果你將這些列更精確的定義為2-byte大小的整型，再來(lái)考慮數(shù)據(jù)集大小間的區(qū)別。這將使每行的大小變?yōu)?00bytes，總的大小就縮減為954MB，比字符串?dāng)?shù)據(jù)類型節(jié)省了大約22GB。通過(guò)數(shù)據(jù)類型大小的簡(jiǎn)單改變，你就可以大大減少要讀入內(nèi)存的數(shù)據(jù)量。注意，以上例子也說(shuō)明了數(shù)據(jù)類型之間的相對(duì)差別和收縮數(shù)據(jù)集大小節(jié)省的空間，這并不是一個(gè)定死的大小調(diào)整方針。

b.最大化吞吐能力 – 除了設(shè)置數(shù)據(jù)類型，你還能夠通過(guò)配置SSIS緩存設(shè)置——如DefaultMaxBufferSize和DefaultMaxBufferRows，來(lái)最大化吞吐能力。這些設(shè)置控制了在數(shù)據(jù)讀取操作過(guò)程中創(chuàng)建緩存的大小，以及每次傳遞的記錄行數(shù)。但是，對(duì)于調(diào)整這些參數(shù)的細(xì)節(jié)不在本白皮書內(nèi)探討。

c.定義并行操作 – 優(yōu)化容量的第三個(gè)方法是考慮如何對(duì)操作進(jìn)行并行化處理，以獲得最大的效率。SSIS支持包，任務(wù)以及轉(zhuǎn)換的并行化處理。當(dāng)你設(shè)計(jì)并行處理流程時(shí)，你要考慮對(duì)數(shù)據(jù)讀取操作實(shí)施怎樣的并行度。例如，如果你需要讀取并聚集多個(gè)文件的數(shù)據(jù)到SQL Server中，你可以考慮對(duì)以下一個(gè)或多個(gè)操作并行處理：文件讀取，數(shù)據(jù)聚集，目標(biāo)數(shù)據(jù)表裝載。盡管SSIS支持所有這些操作的并行處理，你仍舊需要搞清楚系統(tǒng)的資源和它們并行支持的級(jí)別。你可能創(chuàng)建了一個(gè)高并行度的SSIS包，而隨后就意識(shí)到你的系統(tǒng)沒(méi)有足夠的CPU和內(nèi)存來(lái)充分體現(xiàn)這些并行的優(yōu)勢(shì)。

也許這個(gè)讀取5000000條源記錄的例子很簡(jiǎn)單，這些相同的原則卻可以被直接應(yīng)用到更大型和更復(fù)雜的數(shù)據(jù)讀取任務(wù)中，這個(gè)任務(wù)可以包含多個(gè)數(shù)據(jù)源，需要復(fù)雜的數(shù)據(jù)轉(zhuǎn)換，而且必須被裝載到多個(gè)目的表中。

應(yīng)用

根據(jù)定義好的一組數(shù)據(jù)整合操作以及對(duì)所需數(shù)據(jù)容量進(jìn)行的評(píng)估，可以明確出應(yīng)用因素，來(lái)幫助你決定那種數(shù)據(jù)整合應(yīng)用最符合那些需求。

理解應(yīng)用的使用

你可能會(huì)經(jīng)常選擇不同的應(yīng)用程序去解決數(shù)據(jù)整合問(wèn)題。關(guān)鍵是要確定哪些應(yīng)用可以在給你帶來(lái)足夠功能的同時(shí)，成本較低。

例如，你面臨著使用SQL Server Transact-SQL的BULK INSERT語(yǔ)句或BCP工具來(lái)往SQL Server中讀取文本文件的工作，與SSIS相比，這樣可以在成本較低的情況下滿足數(shù)據(jù)讀取功能的需求。當(dāng)你有較小的數(shù)據(jù)集和簡(jiǎn)單的數(shù)據(jù)整合需求時(shí)，上述情況很可能發(fā)生，這時(shí)啟動(dòng)SSIS操作的開(kāi)銷就能抵消使用BCP或BULK INSERT所獲得的性能收益。

幫助你做出正確的選擇，我們把每一應(yīng)用的主要功能組件列出來(lái)，以便你能夠根據(jù)主要的需求做出相應(yīng)的評(píng)價(jià)。例如，以下的使用指導(dǎo)可以幫助你在BULK INSERT/BCP或SSIS中做出選擇。

◆BULK INSERT / BCP使用指導(dǎo) – 當(dāng)你的應(yīng)用場(chǎng)景有以下的特征時(shí)，應(yīng)該使用BULK INSERT / BCP：

◆有單一的數(shù)據(jù)源，并且是文件形式

◆有單一的目標(biāo)，并且是SQL Server

◆沒(méi)有數(shù)據(jù)轉(zhuǎn)換的需求，比如直接從源讀取到目標(biāo)

◆沒(méi)有工作流程管理，注意流程管理并不適合BULK INSERT，因?yàn)樗皇且粋€(gè)將數(shù)據(jù)讀取進(jìn)SQL Server的單獨(dú)操作

◆SSIS使用指導(dǎo) - 當(dāng)你的應(yīng)用場(chǎng)景有以下的特征時(shí)，應(yīng)該使用SSIS：

◆多個(gè)不同種類的數(shù)據(jù)源和目標(biāo)

◆有數(shù)據(jù)轉(zhuǎn)換需求：聚集，查詢，創(chuàng)建新列

◆與其他的管理任務(wù)（如郵件或FTP）相結(jié)合

◆通過(guò)工作流程管理控制多個(gè)任務(wù)和轉(zhuǎn)換的順序

評(píng)估選擇

根據(jù)使用指導(dǎo)，你可以更好的評(píng)估哪種應(yīng)用選擇更符合你的數(shù)據(jù)需求。

在文件讀取的例子中，你的需求不僅僅是往SQL Server中讀入5000000條記錄，還包括對(duì)這些記錄的數(shù)據(jù)聚集?？紤]到這些需求，確認(rèn)以下哪個(gè)方法可以完成這項(xiàng)任務(wù)。注意，這里僅僅列出兩個(gè)可行的方法。

◆方案1 - BULK INSERT解決方案 – 你當(dāng)然可以使用BULK INSERT去從源文件讀取數(shù)據(jù)，并裝載到SQL Server中。但是，你還需要增加額外的操作，在把數(shù)據(jù)裝載到目標(biāo)表前對(duì)其進(jìn)行聚集。一種方法是使用BULK INSERT把數(shù)據(jù)放入一個(gè)暫時(shí)的表，然后使用Transact-SQL對(duì)暫時(shí)表中的數(shù)據(jù)進(jìn)行聚集，再用Transact-SQL把數(shù)據(jù)讀入目標(biāo)表中。

◆方案2 – SSIS解決方案– SSIS提供了一站式的解決方案，你可以在聚集數(shù)據(jù)的同時(shí)將其裝載到目標(biāo)表中。并且，在SSIS中你還能添加工作流程來(lái)控制這些操作的順序，甚至對(duì)執(zhí)行過(guò)程進(jìn)行日志，記錄操作執(zhí)行的過(guò)程，以及進(jìn)行出錯(cuò)處理，找出不符合要求的記錄。

當(dāng)你對(duì)比上述兩個(gè)方案時(shí)，SSIS解決方案可以不使用暫時(shí)表，并且把所有的數(shù)據(jù)讀取邏輯和工作流程裝入單一的SSIS包中。而對(duì)于方案1，你不僅需要使用Transact-SQL來(lái)對(duì)數(shù)據(jù)進(jìn)行聚集，還需要把數(shù)據(jù)邏輯封裝在一個(gè)存儲(chǔ)過(guò)程中。所以，根據(jù)這一對(duì)比，SSIS解決方案是這一讀取-聚集案例最優(yōu)的選擇，也就是可以提供足夠的功能，并且有能力集中管理工作流程。

區(qū)域

最后，你需要確認(rèn)數(shù)據(jù)操作執(zhí)行的區(qū)域。當(dāng)你從源向目標(biāo)讀取數(shù)據(jù)時(shí)，你可能會(huì)有足夠的靈活性去執(zhí)行特定的操作。很有可能，你的決定會(huì)是基于一系列你必須權(quán)衡的因素，根據(jù)你特定的數(shù)據(jù)讀取需求。

定義區(qū)域選擇

一般來(lái)說(shuō)，你有三個(gè)區(qū)域可以選擇：(1) 源數(shù)據(jù)服務(wù)器，(2)目標(biāo)數(shù)據(jù)服務(wù)器，(3) 專門的解析，轉(zhuǎn)換和讀?。‥TL）應(yīng)用服務(wù)器?，F(xiàn)實(shí)環(huán)境下，你的選擇可能會(huì)受許可證的限制。但是，考慮到你的應(yīng)用需求對(duì)各個(gè)區(qū)域的影響，你應(yīng)該確定最適合你執(zhí)行需求的區(qū)域，你甚至可能會(huì)發(fā)現(xiàn)有從新評(píng)估許可證策略的需要。

評(píng)估區(qū)域影響

為了說(shuō)明對(duì)區(qū)域的影響，假定源數(shù)據(jù)文件和數(shù)據(jù)讀取操作所在的服務(wù)器，以及最后存入數(shù)據(jù)的目標(biāo)SQL Server都不在一起：

1.目標(biāo)數(shù)據(jù)服務(wù)器 – 當(dāng)你的目標(biāo)是SQL Server時(shí)，在目標(biāo)數(shù)據(jù)服務(wù)器上執(zhí)行SSIS操作會(huì)帶來(lái)很大的性能優(yōu)勢(shì)。這種情況的最大好處就是可以在SSIS包中使用SQL Server Destination組件。這一組件可以優(yōu)化在內(nèi)存里的數(shù)據(jù)讀取，比標(biāo)準(zhǔn)的OLE DB Destination組件獲得8%到15%的性能提升。要記住，SQL Server Destination組件不支持?jǐn)?shù)據(jù)類型的轉(zhuǎn)換。最終，你需要在往目標(biāo)表加載數(shù)據(jù)之前的SSIS步驟中，確認(rèn)完成數(shù)據(jù)轉(zhuǎn)換的操作。這也就是，在內(nèi)存中的數(shù)據(jù)類型和SQL Server的數(shù)據(jù)類型是直接相對(duì)的。

你當(dāng)然也要了解SSIS與SQL Server在目標(biāo)服務(wù)器上是怎樣爭(zhēng)奪資源的。為了減少爭(zhēng)奪，你可以為SQL Server或者SSIS設(shè)置資源分配。例如，你可以降低SSIS并發(fā)線程的最大數(shù)目，以避免CPU的瓶頸?？傮w來(lái)說(shuō)，如果你的數(shù)據(jù)讀取操作需要你找出最快的方法，把數(shù)據(jù)讀入到SQL Server，那么在目標(biāo)服務(wù)器上運(yùn)行SSIS就是比較好的選擇，可以通過(guò)SQL Server的內(nèi)存數(shù)據(jù)讀取功能獲得性能的提升。

2.源數(shù)據(jù)服務(wù)器 – 當(dāng)你在源數(shù)據(jù)服務(wù)器上運(yùn)行SSIS操作時(shí)，你可以在轉(zhuǎn)換數(shù)據(jù)并通過(guò)網(wǎng)絡(luò)向目標(biāo)加載數(shù)據(jù)前，通過(guò)減小源數(shù)據(jù)集的大小來(lái)提高性能。例如，如果你需要聚集數(shù)據(jù)，那么輸出記錄的條目數(shù)一般會(huì)小于輸入記錄的條目數(shù)。并且，你可以通過(guò)在先前的SSIS過(guò)程中恰當(dāng)?shù)脑O(shè)定數(shù)據(jù)類型來(lái)減小數(shù)據(jù)集的大小。

想要獲得這一好處，你必須接受一些折中處理。如果其他的應(yīng)用程序運(yùn)行在源服務(wù)器上，SSIS可能會(huì)和這些應(yīng)用爭(zhēng)奪機(jī)器資源。如果資源比較有限，你很可能會(huì)犧牲掉你所獲得的性能呢個(gè)提升。還有，如果你的目標(biāo)服務(wù)器是SQL Server，你將不能使用經(jīng)過(guò)優(yōu)化的SSIS SQL Server Destination組件的內(nèi)存數(shù)據(jù)讀取功能。SQL Server Destination組件只有當(dāng)SSIS數(shù)據(jù)操作在目標(biāo)SQL Server服務(wù)器上執(zhí)行時(shí)才會(huì)發(fā)生作用。除此之外，你必須要使用標(biāo)準(zhǔn)的OLE DB destination組件，然而，它沒(méi)有內(nèi)存數(shù)據(jù)讀取功能所帶來(lái)的好處。

考慮到這些犧牲，如果你能把大量數(shù)據(jù)記錄減少成為較小的數(shù)據(jù)集，當(dāng)你關(guān)心網(wǎng)絡(luò)性能瓶頸的時(shí)候，在源數(shù)據(jù)服務(wù)器上執(zhí)行SSIS操作是有好處的。

3.專門的SSIS服務(wù)器 – 當(dāng)你有多種不同類型的數(shù)據(jù)源和目標(biāo)，對(duì)較大量數(shù)據(jù)進(jìn)行復(fù)雜轉(zhuǎn)換時(shí)，使用專門的SSIS服務(wù)器是最佳的方案。在這一專門的服務(wù)器上，SSIS可以使用全部機(jī)器資源而不必跟其他應(yīng)用爭(zhēng)搶，這樣會(huì)獲得最佳的優(yōu)勢(shì)。

也有不好的方面，你當(dāng)然也無(wú)法使用SSIS SQL Server Destination組件。而且，從網(wǎng)絡(luò)方面看，你必須要傳送數(shù)據(jù)兩次：從源服務(wù)器到SSIS服務(wù)器，然后再?gòu)腟SIS服務(wù)器到目標(biāo)服務(wù)器。如果你的網(wǎng)絡(luò)貸款可以輕松的支持這些傳送，這一方案提供了最大的SSIS靈活性。它也適合于那些有復(fù)雜轉(zhuǎn)換需求的情況，這時(shí)SSIS不會(huì)反過(guò)來(lái)影響其他應(yīng)用，或SQL Server本身。

總結(jié)

當(dāng)你設(shè)計(jì)數(shù)據(jù)整合解決方案時(shí)，你可以把性能調(diào)整看成一種藝術(shù)，或科學(xué)，但你更關(guān)注的是結(jié)果。為了幫助你更好的完成性能調(diào)整的工作，本文中描述的性能策略提供了一個(gè)方法框架，使您可以創(chuàng)建出高性能的數(shù)據(jù)整合解決方案。

通過(guò)指導(dǎo)你對(duì)操作，容量，應(yīng)用和區(qū)域進(jìn)行評(píng)估，這一策略可以幫助你預(yù)見(jiàn)并優(yōu)化各種大規(guī)模的數(shù)據(jù)讀取情景，無(wú)論數(shù)據(jù)簡(jiǎn)單還是復(fù)雜。

希望了解更多信息，請(qǐng)?jiān)L問(wèn)以下網(wǎng)址：http://www.microsoft.com/sql/

關(guān)于作者

Elizabeth Vitt, Intellimentum

Elizabeth Vitt在商業(yè)智能領(lǐng)域擁有超過(guò)十年的商業(yè)系統(tǒng)開(kāi)發(fā)，項(xiàng)目管理，咨詢和培訓(xùn)經(jīng)驗(yàn)。她的業(yè)界經(jīng)歷包括為零售、制造和金融服務(wù)等行業(yè)實(shí)施商業(yè)智能解決方案。她在數(shù)據(jù)倉(cāng)庫(kù)，ETL和OLAP設(shè)計(jì)和實(shí)施方面還有著豐富的教學(xué)經(jīng)驗(yàn)。Vitt女士是微軟有關(guān)商業(yè)智能產(chǎn)品官方課程內(nèi)容的特約作者，由MSPress出版了《商業(yè)智能：更快更好的做出決策》。伴隨著SQL Server 2005的正式發(fā)表，Vitt女士幫助早期的客戶成功的實(shí)施了SQL Server 2005方案。