共用方式為

  • 發行項

改善資料流程的效能

本主題提供有關如何設計 Integration Services 封裝以避免常見效能問題的建議。本主題同時也提供有關您可以用於疑難排解封裝效能之功能與工具的資訊。

設定資料流程

若要設定「資料流程」工作以獲得更好的效能，您可以設定工作的屬性、調整緩衝區大小，以及設定平行執行的封裝。

設定資料流程工作的屬性

[!附註]

您必須針對封裝中的每一個「資料流程」工作，個別設定本節中所討論的屬性。

您可以設定下列會影響效能的資料流程工作的屬性：

  • 指定緩衝區資料的暫時儲存位置 (BufferTempStoragePath 屬性)，以及指定包含二進位大型物件 (BLOB) 資料之資料行的暫時儲存位置 (BLOBTempStoragePath 屬性)。根據預設，這些屬性包含 TEMP 和 TMP 環境變數的值。您可能需要指定其他資料夾，以便將暫存檔放在不同或更快的硬碟上，或是將暫存檔分散到多個磁碟機。您可以使用分號分隔目錄名稱，以指定多個目錄。

  • 定義工作所使用之緩衝區的預設大小 (設定 DefaultBufferSize 屬性)，以及定義每個緩衝區中資料列的最大數目 (設定 DefaultBufferMaxRows 屬性)。預設緩衝區大小是 10 MB，最大緩衝區大小是 100 MB，預設最大資料列數目是 10,000。

  • 設定執行期間工作可以使用的執行緒數目 (設定 EngineThreads 屬性)。這個屬性為資料流程引擎提供可使用之執行緒數目的建議，預設值為 5，且最小值為 3。不過，無論這個屬性的值為何，引擎都不會使用超出它所需的執行緒數目。如果有需要，引擎也可能會使用超過這個屬性所指定的執行緒數目，以避免發生並行的問題。

  • 指示資料流程工作是否以最佳化模式執行 (RunInOptimizedMode 屬性)。最佳化模式可藉由從資料流程中移除未使用的資料行、輸出和元件，來提升效能。

    [!附註]

    您可以在 Business Intelligence Development Studio 中的專案層級，設定具有相同名稱的屬性 (RunInOptimizedMode)，以指示資料流程工作在偵錯期間以最佳化模式執行。這個專案屬性會覆寫資料流程工作在設計階段的 RunInOptimizedMode 屬性。

調整緩衝區大小

資料流程引擎是經由計算單一資料列的估計大小，開始設定緩衝區大小的工作，接著它會將資料列的估計大小乘以 DefaultBufferMaxRows 的值，以獲得初步可行的緩衝區大小值。

  • 如果得到的結果超出 DefaultBufferSize 的值，引擎會減少資料列的數目。

  • 如果得到的結果小於內部計算的最小緩衝區大小，引擎會增加資料列的數目。

  • 如果得到的結果介於最小緩衝區大小和 DefaultBufferSize 的值之間，引擎會盡可能將緩衝區的大小設為接近資料列的估計大小乘以 DefaultBufferMaxRows 的值。

當您開始測試資料流程工作的效能時，請使用 DefaultBufferSize 和 DefaultBufferMaxRows 的預設值。啟用資料流程工作的記錄功能，並選取 BufferSizeTuning 事件，以查看每一個緩衝區中包含多少個資料列。

在開始調整緩衝區大小之前，您可以進行的最重要改善就是移除不需要的資料行，以及適當地設定資料類型，以減少每一個資料列的大小。

如果有足夠的記憶體可用，您應該使用數目較少但比較大的緩衝區，而不是使用數目較多但比較小的緩衝區。換句話說，您可以減少裝載資料所需的緩衝區總數，並盡可能在緩衝區中塞入更多的資料列，藉此改善效能。若要判斷緩衝區的最佳數目與大小，請使用 DefaultBufferSize 和 DefaultBufferMaxRows 的值進行測試，同時監視效能及 BufferSizeTuning 事件所報告的資訊。

請勿增加發生分頁至磁碟之起始點的緩衝區大小。分頁至磁碟所妨礙的效能超過尚未經過最佳化的緩衝區大小。若要判斷是否發生分頁，請在 Microsoft Management Console (MMC) 的效能嵌入式管理單元中監視 "Buffers spooled" 效能計數器。 

設定平行執行的封裝

平行執行會改善具有多個實體或邏輯處理器之電腦的效能。為了在封裝中支援平行執行不同的工作，Integration Services 使用兩種屬性：MaxConcurrentExecutables 和 EngineThreads。

MaxConcurrentExcecutables 屬性

MaxConcurrentExecutables 屬性是封裝本身的屬性。此屬性會定義可以同時執行多少工作。預設值為 -1，表示實體或邏輯處理器的數目加上 2。

若要了解此屬性的運作方式，請考慮具有三個「資料流程」工作的範例封裝。如果您將 MaxConcurrentExecutables 設定為 3，全部三個「資料流程」工作都可以同時執行。不過，這是假設每個「資料流程」工作都有 10 的來源到目的地的執行樹狀結構。將 MaxConcurrentExecutables 設定為 3 不能確保每個「資料流程」工作內的執行樹狀結構都可以平行執行。

EngineThreads 屬性

EngineThreads 屬性是每個「資料流程」工作的屬性。此屬性會定義資料流程引擎可以平行建立並執行多少執行緒。EngineThreads 屬性同樣適用於資料流程引擎針對來源所建立的來源執行緒，以及該引擎針對轉換和目的地所建立的工作者執行緒。因此，將 EngineThreads 設定為 10 表示引擎最多可以建立 10 個來源執行緒與 10 個工作者執行緒。

若要了解此屬性的運作方式，請考慮具有三個「資料流程」工作的範例封裝。每個「資料流程」工作都包含 10 的來源到目的地的執行樹狀結構。如果您將「資料流程」工作上的 EngineThreads 設定為 10，全部 30 個執行樹狀結構可能會同時執行。

[!附註]

執行緒的討論超出本主題的範圍。不過，一般規則不會平行執行超過可用處理器數目的執行緒。執行超過可用處理器數目的執行緒可能會因為在執行緒之間進行經常性的內容切換而妨礙效能。

設定個別的資料流程元件

若要設定個別的資料流程元件以獲得較好的效能，有一些您可以遵循的一般指導方針。針對每種資料流程元件，也有特定的指導方針：來源、轉換和目的地。

一般指導方針

不管資料流程元件為何，都有兩個您應該遵循的一般指導方針來改善效能：最佳化查詢與避免不必要的字串。

最佳化查詢

某些資料流程元件會在從來源擷取資料時或在查閱作業中使用查詢，以建立參考資料表。預設查詢會使用 * FROM <tableName> 語法。這種類型的查詢會傳回來源資料表中的所有資料行。讓所有資料行都在設計階段可用，您便可以選擇任何資料行作為查閱、傳遞或來源資料行。不過，在選擇要使用的資料行後，您應該將查詢修訂為只包含選取的資料行。移除多餘的資料行可讓封裝中的資料流程更為有效，因為資料行越少，就會建立越小的資料列。資料列越小，表示有越多的資料列可以納入同一個緩衝區，而且處理資料集中全部資料列所需的工作也就越少。

若要建構查詢，您可以輸入查詢或使用「查詢產生器」。

[!附註]

當您在 Business Intelligence Development Studio 中執行封裝時，SSIS 設計師的 [進度] 索引標籤會列出警告。這些警告包括識別來源提供給資料流程使用，但是在下游資料流程元件後續作業中未使用的任何資料行。您可以使用 RunInOptimizedMode 屬性自動移除這些資料行。

避免不必要的排序

排序本質上就是很慢的作業，避免不必要的排序可以改善封裝資料流程的效能。

有時候來源資料在由下游元件使用前，就已經經過排序。這種預先排序會在 SELECT 查詢使用 ORDER BY 子句時，或在將資料以排序的順序插入來源時發生。對於這種預先排序的來源資料，您可以提供資料已排序的提示，因而避免使用「排序」轉換來滿足某些下游轉換的排序需求 (例如，「合併」和「合併聯結」轉換需要已排序的輸入)。若要提供資料已排序的提示，您必須執行下列工作：

  • 將上游資料流程元件之輸出的 IsSorted 屬性設定為 True。

  • 指定排序資料所依據的排序索引鍵資料行。

如需詳細資訊，請參閱＜如何：排序合併和合併聯結轉換的資料＞。

如果在資料流程中必須排序資料，您可以將資料流程設計為盡可能少使用排序作業，以改進效能。例如，資料流程使用「多點傳送」轉換來複製資料集。在「多點傳送」轉換執行前，對資料集進行一次排序，而不應在轉換後排序多個輸出。

如需詳細資訊，請參閱＜排序轉換＞、＜合併轉換＞、＜合併聯結轉換＞和＜多點傳送轉換＞。

來源

OLE DB 來源

當您使用 OLE DB 來源從檢視表中擷取資料時，選取 [SQL 命令] 當做資料存取模式，然後輸入 SELECT 陳述式。使用 SELECT 陳述式存取資料的效能比選取 [資料表或檢視表] 當做資料存取模式的效能更好。

轉換

使用本節中的建議來改善「彙總」、「模糊查閱」、「模糊群組」、「查閱」、「合併聯結」與「緩時變維度」轉換的效能。

彙總轉換

「彙總」轉換包括 Keys、KeysScale、CountDistinctKeys 和 CountDistinctScale 屬性。這些屬性會提升效能，其方式是讓轉換針對轉換所快取的資料來預先配置所需的記憶體數量。如果您知道預期要從 [群組依據] 作業產生的精確或大約群組數，請分別設定 Keys 和 KeysScale 屬性。如果您知道預期要從 [相異計數] 作業產生的精確或大約相異數，請分別設定 CountDistinctKeys 和 CountDistinctScale 屬性。

如果必須在資料流程中建立多個彙總，您應考慮使用一個「彙總」轉換來建立多個彙總，而不是建立多個轉換。當一個彙總就是其他彙總的子集時，這個方法能夠改善效能，因為轉換可以最佳化內部儲存體，並且只會掃描一次傳入的資料。例如，如果彙總使用 GROUP BY 子句和 AVG 彙總，則將它們組合成一個轉換可以改進效能。不過，在一個「彙總」轉換內執行多個彙總會序列化彙總作業，因此，當多個彙總必須個別計算時，可能不會改善效能。

如需詳細資訊，請參閱＜彙總轉換＞。

模糊查閱和模糊群組轉換

如需有關最佳化「模糊查閱」和「模糊群組」轉換的詳細資訊，請參閱 SQL Server Integration Services 2005 中的模糊查詢和模糊群組白皮書。

查閱轉換

輸入僅查閱所需資料行的 SELECT 陳述式可以將記憶體中的參考資料大小最小化。這個選項的效能比選取會傳回大量不必要資料的整個資料表或檢視表更好。

合併聯結轉換

合併聯結轉換包括 MaxBuffersPerInput 屬性，用以指定每一次輸入時所容許的使用中緩衝區數目上限。您可以使用這個屬性來微調緩衝區使用的記憶體數量，並進而微調轉換的效能。緩衝區的數目愈大，轉換使用的記憶體就愈多，效能也會愈高。MaxBuffersPerInput 的預設值 5 是多數案例中能夠達到最佳效果的緩衝區數目。若要微調效能，您可以嘗試使用稍有不同的緩衝區數目，例如 4 或 6。如果可能，您應該避免使用過小的緩衝區數目。例如，將 MaxBuffersPerInput 由 5 改設為 1 會對效能造成重大影響。同時，您不應將 MaxBuffersPerInput 設定為 0 (包含) 以下。這個值範圍表示不會進行調整，而且依據資料負荷量和可用的記憶體數量而定，封裝可能會不完整。

為了避免發生死結，合併聯結轉換可能會暫時增加其所使用的緩衝區數目，使其超過 MaxBuffersPerInput 的值。解決死結狀況之後，MaxBuffersPerInput 便會回復成原先的設定值。

如需詳細資訊，請參閱＜合併聯結轉換＞。

緩時變維度轉換

「緩時變維度精靈」和「緩時變維度精靈」轉換都是符合多數使用者需求的一般用途工具。不過，精靈所產生的資料流程不會針對效能進行最佳化。

「緩時變維度」轉換中最緩慢的元件通常是一次針對一個單一資料列執行 UPDATE 的「OLE DB 命令」轉換。因此，改善「緩時變維度」轉換效能最有效的方式就是取代「OLE DB 命令」轉換。您可以將這些轉換取代為將要更新的所有資料列儲存到臨時資料表的目的地元件。然後，您可以同時加入針對所有資料列執行以單一資料列集為基礎之 Transact-SQL UPDATE 的「執行 SQL」工作。

進階使用者可以針對緩時變維度處理，設計針對大維度進行最佳化的自訂資料流程。如需此方式的討論和範例，請參閱專案 REAL：Business Intelligence ETL 設計練習白皮書中的「唯一的維度狀況」一節。

目的地

為達成較佳的目的地效能，請考慮使用 SQL Server 目的地並測試目的地的效能。

SQL Server 目的地

當封裝將資料載入相同電腦上的 SQL Server 執行個體時，請使用 SQL Server 目的地。這個目的地最適合高速大量載入。

測試目的地的效能

您可能會發現將資料儲存至目的地所花費的時間超出預期。若要識別速度很慢是否是因為目的地無法夠快地處理資料，您可以暫時使用「資料列計數器」轉換來取代目的地。如果輸送量顯著提高，則很可能是正在載入資料的目的地導致速度變慢。

監視封裝效能

Integration Services 包含可用以監視封裝效能的工具與功能。例如，記錄功能會擷取有關封裝的執行階段資訊，而效能計數器可讓您監視資料流程引擎。請使用下列建議判斷封裝對效能影響最大的部分。 

檢視進度索引標籤上的資訊

「SSIS 設計師」會提供在 Business Intelligence Development Studio 中執行封裝時，有關控制流程和資料流程的資訊。[進度] 索引標籤會以執行順序列出工作和容器，並包含每個工作和容器 (包括封裝本身) 的開始與完成時間、警告及錯誤訊息。此外，還會以執行順序列出資料流程元件，並包含有關進度 (以完成百分比顯示) 以及已經處理的資料列數目等資訊。

若要啟用或停用 [進度] 索引標籤上的訊息顯示，請在 [SSIS] 功能表上切換 [偵錯進度報表] 選項。停用進度報告有助於在 BI Development Studio 中執行複雜封裝時改善效能。

設定封裝的記錄功能

Integration Services 包含各種記錄提供者，它們可讓封裝將執行階段的資訊記錄至不同類型的檔案或 SQL Server。您可以啟用封裝和個別封裝物件 (例如工作和容器) 的記錄項目。Integration Services 包含各種不同的工作和容器，每個工作和容器都有自己的一組描述性記錄項目。例如，包含執行 SQL 工作的封裝可以寫入記錄項目，而此記錄項目會列出工作執行的 SQL 陳述式，包括陳述式的參數值。

記錄項目包含諸如封裝和封裝物件的開始和結束時間等資訊，這樣就能識別執行速度較慢的工作和容器。如需詳細資訊，請參閱＜記錄封裝執行＞、＜在封裝中實作記錄＞和＜自訂訊息以進行記錄＞。

設定資料流程工作的記錄功能

資料流程工作提供許多可用以監視及調整效能的自訂記錄項目。例如，您可以監視可能會造成記憶體遺漏的元件，或是追蹤執行某特定元件所花費的時間。如需這些自訂記錄項目的清單以及記錄輸出範例，請參閱＜資料流程工作＞。

使用 PipelineComponentTime 事件

最有用的自訂記錄項目可能是 PipelineComponentTime 事件。這個記錄項目會報告每個元件在五個主要處理步驟的每個步驟中所花費的毫秒數。下表將描述這些處理步驟。Integration Services 開發人員會將這些步驟辨識成 PipelineComponent 的主要方法。

步驟

說明

Validate

元件會檢查有效的屬性值和組態設定。

PreExecute

元件會在開始處理資料列之前，執行一次處理。

PostExecute

元件會在已經處理所有資料列之後，執行一次處理。

ProcessInput

轉換或目的地元件會處理上游來源或轉換傳遞給它的傳入資料列。

PrimeOutput

來源或轉換元件會填入要傳遞給下游轉換或目的地元件的資料緩衝區。

當您啟用 PipelineComponentTime 事件時，Integration Services 就會針對每個元件所執行的每個處理步驟記錄一則訊息。下列記錄項目會顯示 Integration Services CalculatedColumns 封裝範例所記錄的訊息子集：

The component "Calculate LineItemTotalCost" (3522) spent 356 milliseconds in ProcessInput.

The component "Sum Quantity and LineItemTotalCost" (3619) spent 79 milliseconds in ProcessInput.

The component "Calculate Average Cost" (3662) spent 16 milliseconds in ProcessInput.

The component "Sort by ProductID" (3717) spent 125 milliseconds in ProcessInput.

The component "Load Data" (3773) spent 0 milliseconds in ProcessInput.

The component "Extract Data" (3869) spent 688 milliseconds in PrimeOutput filling buffers on output "OLE DB Source Output" (3879).

The component "Sum Quantity and LineItemTotalCost" (3619) spent 141 milliseconds in PrimeOutput filling buffers on output "Aggregate Output 1" (3621).

The component "Sort by ProductID" (3717) spent 16 milliseconds in PrimeOutput filling buffers on output "Sort Output" (3719).

這些記錄項目顯示資料流程工作將大部分時間花費在下列步驟上 (在此依遞減順序顯示)：

  • 名為 "Extract Data" 的 OLE DB 來源花費了 688 毫秒來載入資料。

  • 名為 "Calculate LineItemTotalCost" 的「衍生的資料行」轉換花費了 356 毫秒來計算傳入資料列。

  • 名為 "Sum Quantity and LineItemTotalCost" 的「彙總」轉換花費了結合的 220 毫秒 (141 毫秒在 PrimeOutput 而 79 毫秒在 ProcessInput) 來執行計算以及將資料傳遞給下一個轉換。

監視資料流程引擎的效能

Integration Services 包含一組監視資料流程引擎效能的效能計數器。例如，您可以追蹤所有緩衝區使用的記憶體總數 (以位元組為單位)，並檢查元件是否記憶體不足。緩衝區是元件用以儲存資料的記憶體區塊。如需詳細資訊，請參閱＜監視資料流程引擎的效能＞。

外部資源

Integration Services 圖示 (小) 掌握 Integration Services 的最新狀態

若要取得 Microsoft 的最新下載、文章、範例、影片，以及社群的精選解決方案，請瀏覽 MSDN 上的 Integration Services 頁面：


若要得到這些更新的自動通知，請訂閱該頁面上所提供的 RSS 摘要。