共用方式為

數據流效能功能

本主題提供如何設計 Integration Services 套件以避免常見的效能問題的建議。 本主題也提供功能與工具的相關信息，可供您用來針對套件的效能進行疑難解答。

設定數據流

若要設定數據流工作以提升效能，您可以設定工作的屬性、調整緩衝區大小，以及設定封裝以進行平行執行。

設定數據流工作的屬性

備註

本節中討論的屬性必須針對封裝中的每個數據流工作個別設定。

您可以設定資料流工作的下列屬性，這一切都會影響效能：

  • 指定緩衝區數據暫存位置 （BufferTempStoragePath 屬性） 和包含二進位大型物件 （BLOB） 資料的數據行 （BLOBTempStoragePath 屬性） 的位置。 根據預設，這些屬性包含 TEMP 和 TMP 環境變數的值。 您可能想要指定其他資料夾，將暫存盤放在不同或更快的硬碟上，或將它們分散到多個磁碟驅動器上。 您可以使用分號分隔目錄名稱，以指定多個目錄。

  • 藉由設定 DefaultBufferSize 屬性，並藉由設定 DefaultBufferMaxRows 屬性來定義工作所使用的緩衝區預設大小，並藉由設定 DefaultBufferMaxRows 屬性來定義每個緩衝區中的數據列數目上限。 默認緩衝區大小為 10 MB，最大緩衝區大小為 100 MB。 默認的數據列數目上限為10,000。

  • 藉由設定 EngineThreads 屬性，設定工作在執行期間可以使用的線程數目。 這個屬性會提供數據流引擎關於要使用的線程數目的建議。 默認值為 10，最小值為 3。 不過，不論此屬性的值為何，引擎都不會使用比它所需的更多線程。 如有必要，引擎也可以使用比這個屬性中指定的更多線程，以避免並行問題。

  • 指出數據流工作是否以優化模式執行（RunInOptimizedMode 屬性）。 優化模式藉由從數據流中移除未使用的數據行、輸出和元件，以改善效能。

    備註

    具有相同名稱 RunInOptimizedMode 的屬性可以在 SQL Server Data Tools （SSDT） 的專案層級設定，以指出數據流工作在偵錯期間以優化模式執行。 這個項目屬性會在設計時期覆寫資料流程工作中的 RunInOptimizedMode 屬性。

調整緩衝區的大小

數據流引擎會藉由計算單一數據列的估計大小，開始調整其緩衝區大小的工作。 然後，它會將數據列的估計大小乘以 DefaultBufferMaxRows 的值，以取得緩衝區大小的初步工作值。

  • 如果結果大於 DefaultBufferSize 的值，引擎會減少數據列數目。

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

  • 如果結果在最小緩衝區大小和 DefaultBufferSize 的值之間，則引擎會將緩衝區大小調整至盡可能接近「估計的數據列大小乘以 DefaultBufferMaxRows 值」。

當您開始測試數據流工作的效能時，請使用DefaultBufferSize和DefaultBufferMaxRows的預設值。 啟用數據流工作的記錄，然後選取 BufferSizeTuning 事件，以查看每個緩衝區包含的數據列數目。

開始調整緩衝區的大小之前，最重要的改進是移除不需要的數據行，並適當地設定數據類型，以減少每個數據列的大小。

若要判斷最佳緩衝區數目及其大小，請實驗 DefaultBufferSize 和 DefaultBufferMaxRows 的值，同時監視效能和 BufferSizeTuning 事件所報告的資訊。

請勿將緩衝區大小增加到導致磁碟分頁開始的程度。 將內容分頁到磁碟比使用未經優化的緩衝區大小更會影響效能。 若要判斷是否發生分頁，請在 Microsoft Management Console （MMC） 的 [效能] 嵌入式管理單元中監視「緩衝區多任務緩衝處理」性能計數器。

設定封裝以進行平行執行

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

MaxConcurrentExcecutables 屬性

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

若要瞭解此屬性的運作方式，請考慮具有三個數據流工作的範例套件。 如果您設定為 MaxConcurrentExecutables 3，這三個數據流工作都可以同時執行。 不過，假設每個資料流任務都有10個來源到目的地的執行樹。 將設定 MaxConcurrentExecutables 為 3 不可確保每個資料流工作內的執行樹狀結構會平行執行。

EngineThreads 屬性

屬性 EngineThreads 是每個數據流工作的屬性。 這個屬性會定義數據流引擎可以平行建立和執行的線程數目。 屬性 EngineThreads 同樣適用於數據流引擎針對來源所建立的來源線程，以及引擎針對轉換和目的地所建立的背景工作線程。 因此，設定 EngineThreads 為 10 表示引擎最多可以建立十個來源線程和最多十個背景工作線程。

若要瞭解這個屬性的運作方式，請考慮使用三個數據流工作的範例套件。 每個資料流任務都包含十個來源到目的地的執行樹。 如果您在每個數據流工作上將 EngineThreads 設定為 10，則所有 30 個執行樹狀結構都可能會同時執行。

備註

線程的討論超出本主題的範圍。 不過，一般規則不會平行執行比可用處理器數目更多的線程。 由於線程之間頻繁的內容切換，執行線程數目超過可用處理器數目可能會阻礙效能。

設定個別數據流元件

若要設定個別數據流元件以提升效能，您可以遵循一些一般指導方針。 每種數據流元件類型也有特定的指導方針：來源、轉換和目的地。

一般指導方針

不論數據流元件為何，您都應該遵循兩個一般指導方針來改善效能：優化查詢並避免不必要的字串。

優化查詢

一些數據流元件會在從來源擷取數據時，或在查閱作業中使用查詢來建立參考數據表。 默認查詢會使用 SELECT * FROM <tableName> 語法。 這種類型的查詢會傳回源數據表中的所有數據行。 在設計時提供所有資料行，使您可以選擇任何資料行作為查閱、傳遞或來源資料行。 不過，在您選取要使用的數據行之後，您應該修改查詢，只包含那些選取的數據行。 拿掉多餘的數據行可讓封裝中的數據流更有效率，因為較少的數據行會建立較小的數據列。 較小的數據列表示更多數據列可以放入一個緩衝區中，而處理數據集中所有數據列的工作就越少。

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

備註

當您在 SQL Server Data Tools （SSDT） 中執行封裝時，SSIS 設計工具的 [進度] 索引卷標會列出警告。 這些警告包括識別來源提供給數據流的任何數據行，但後續不會由下游數據流元件使用。 您可以使用 RunInOptimizedMode 屬性自動移除這些資料列。

避免不必要的排序

排序本質上是緩慢的作業，避免不必要的排序可以增強封裝數據流的效能。

有時候，源數據在被下游元件使用之前就已經排序好了。 當 SELECT 查詢使用 ORDER BY 子句或資料以排序順序插入來源時，可能會發生這類預先排序。 針對這類預先排序的源數據，您可以提供數據已排序的提示，藉此避免使用排序轉換來滿足特定下游轉換的排序需求。 （例如，合併和合併聯結轉換需要已排序的輸入。若要提供資料排序的提示，您必須執行下列工作：

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

  • 指定用於排序的排序鍵欄位。

如需詳細資訊，請參閱 如何排序數據以進行合併和合併聯結轉換。

如果您必須排序數據流中的數據，您可以藉由設計數據流來盡可能少地使用排序作業來改善效能。 例如，數據流會使用多播轉換來複製數據集。 在多播轉換執行之前排序數據集一次，而不是在轉換之後排序多個輸出。

如需詳細資訊，請參閱 排序轉換、 合併轉換、 合併聯結轉換和 多播轉換。

來源

OLE DB 來源

當您使用 OLE DB 來源從檢視擷取數據時，請選取 [SQL 命令] 作為資料存取模式，然後輸入 SELECT 語句。 使用 SELECT 語句存取資料會比選取 [資料表或檢視表] 做為資料存取模式更好。

變化

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

彙總轉換

匯總轉換包含 Keys、 KeysScale、 CountDistinctKeys和 CountDistinctScale 屬性。 這些屬性可藉由讓轉換預先配置轉換對於轉換快取之數據所需的內存量，藉此改善效能。 如果您知道 「群組依據」 作業會產生的確切或近似群組數目，請分別設定 Keys 和 KeysScale 屬性。 如果您知道「Distinct count」作業預期產生的相異值的確切或近似數目，請分別設定 CountDistinctKeys 和 CountDistinctScale 屬性。

如果您需要在數據流中建立多個匯總，請考慮建立多個匯總，以使用一個匯總轉換，而不是建立多個轉換。 當某個匯總是另一個匯總的子集時，這個方法可改善效能，因為轉換可以優化內部記憶體，並且只掃描傳入數據一次。 例如，如果匯總使用 GROUP BY 子句和 AVG 匯總，將它們結合成一個轉換可以改善效能。 不過，在一個匯總轉換內執行多個匯總會串行化匯總作業，因此在必須獨立計算多個匯總時，可能無法改善效能。

模糊查找和模糊群組轉換

如需優化模糊查閱和模糊群組轉換效能的相關信息，請參閱 SQL Server Integration Services 2005 中的白皮書、模糊查閱和模糊群組。

查找轉換

輸入只查閱所需數據行的SELECT語句，將記憶體中的參考數據大小降到最低。 此選項的執行效能優於選取整個數據表或檢視表，這會傳回大量的不必要的數據。

合併聯結轉換

您不再需要設定 屬性的值 MaxBuffersPerInput ，因為Microsoft已進行變更，以減少合併聯結轉換會耗用過多記憶體的風險。 這個問題有時候會發生在合併聯結的多個輸入以不平均的速率產生資料時。

緩慢變動維度轉換

[緩時變維度精靈] 和 [緩時變維度] 轉換是符合大部分使用者需求的一般用途工具。 不過，精靈產生的數據流並未針對效能進行優化。

一般而言，緩慢變化維度轉換中最慢的元件是一次對單一資料列執行UPDATE的OLE DB命令轉換。 因此，改善緩時變維度轉換效能的最有效方式是取代 OLE DB 命令轉換。 您可以將這些轉換取代為目標元件，將所有待更新的資料列保存至暫存表。 然後，您可以新增一個執行 SQL 任務，針對所有行同時執行單一以集合為基礎的 Transact-SQL UPDATE。

進階使用者可以設計自定義數據流，以便緩時變更針對大型維度優化的維度處理。 如需有關此方法的討論和範例，請參閱白皮書《Project REAL：商業智慧ETL設計實務》中的一節〈獨特維度場景〉。

目的地

若要使用目的地達到更好的效能，請考慮使用 SQL Server 目的地並測試目的地的效能。

SQL Server 目的地

當封裝將數據載入相同電腦上的 SQL Server 實例時，請使用 SQL Server 目的地。 此目的地已針對高速大量負載進行優化。

測試目的地的效能

您可能會發現將資料儲存到目的地所需的時間比預期還要多。 若要識別是否因目的地無法快速處理資料而造成緩慢，您可以暫時將目的地替換為列數轉換。 如果輸送量大幅改善，載入數據的目的地可能會導致速度變慢。

檢閱 [進度] 索引標籤上的資訊

當您在 SQL Server Data Tools （SSDT） 中執行封裝時，SSIS 設計工具會提供控制流程和數據流的相關信息。 [ 進度 ] 索引標籤會依執行順序列出工作和容器，並包含每個工作和容器的開始和完成時間、警告和錯誤訊息，包括封裝本身。 它也會依執行順序列出數據流元件，並包含進度的相關信息、顯示為完成百分比，以及已處理的數據列數目。

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

相關內容

文章和部落格文章

影片

另請參閱

套件開發的疑難排解工具
封裝執行的疑難解答工具

  • Last updated on