Integration Services 效能微調技巧

發佈日期: 2006 年 1 月 16 日

作者:Elizabeth Vitt,Intellimentum and Hitachi Corporation

參與人:Donald Farmer (Microsoft Corporation)、Ashvini Sharma (Microsoft Corporation)、Stacia Misner (Hitachi Consulting)

適用於:SQL Server 2005

摘要:這份白皮書說明適用於 SQL Server Integration Services (SSIS) 資料整合解決方案的一般效能微調技巧。

本頁內容

簡介
SSIS 引擎概觀
緩衝區的使用方式
執行樹狀結構
評估設計替代方案
緩衝區大小調整
平行處理
效能的衡量
結論
關於作者

簡介

打造資料整合解決方案時,您的設計決策不僅左右了解決方案是否能成功地迎合功能需求,亦決定了解決方案能否符合效能需求。為了打造出適切的效能設計決策,您必須對資料整合工具的效能架構有所瞭解,亦需要熟悉一些工具的使用技巧,才能徹底的利用系統資源 (如記憶體和 CPU)。

Microsoft® SQL Server™ 2005 Integration Services (SSIS) 提供功能面面俱到的資料整合與工作流程引擎,具備豐富的開發環境,可建立高效能資料整合解決方案。SSIS 能以各種最佳化管道徹底使用資源,同時迎合您所需的特定資料整合情況。

SSIS 引擎概觀

著手使用 SSIS 套件中具體的微調技巧之前,務必要先熟悉 SSIS 的架構。這個架構是由兩個主要的元件組成:執行階段引擎與資料流程引擎。

執行階段引擎:

執行階段引擎是高度平行的控制流程引擎,可協調 SSIS 中的工作或工作單位的執行,並管理執行這些工作的引擎執行緒。大部分的情況下,執行階段引擎的效能主要取決於網路頻寬等 SSIS 的外在條件,以及與資料伺服器、FTP 伺服器或電子郵件伺服器等外部系統的互動。舉例來說,SSIS 執行 Execute SQL Task 時,會傳送呼叫給目標資料庫,待收到來自資料庫伺服器的回應後才會再繼續執行。在本例中,Execute SQL Task 的效能主要取決於查詢執行的效能,而非 SSIS 執行階段引擎。

資料流程引擎

使用 SSIS 進行資料整合時,您不僅需要執行階段引擎,也要搭配資料流程引擎來管理資料管線。資料流程引擎是由 SSIS 中一個名為「資料流程」的特定工作所叫用。「資料流程」工作執行時,SSIS 資料流程引擎會從一或多個資料流程擷取資料,於其中執行任何必要的轉換後,再將資料傳送至一或多個目的地。

資料整合解決方案中,微調效能的重點通常是資料流程引擎。資料流程引擎一如執行時期引擎,也會受到外在條件的影響,但是您可以根據資料整和運作的方式,微調各種設定來提昇資料流程引擎的效能。

緩衝區的使用方式

資料流程引擎在幕後採用一種以緩衝區為導向的架構,有效地載入並操縱記憶體內的資料集。這種在記憶體內處理的方式有其優點:在資料整合的每一個步驟中替您免除實際複製和預備資料的麻煩,因為資料流程引擎會在資料從來源傳輸到目的地之間操縱資料。

因為這個資料會流經管線,SSIS 會在有額外作業時儘量重複利用前一個緩衝區。至於緩衝區如何使用和重複使用,要看管線中的轉換類型而定。

  • 資料列轉換:資料列轉換是使用資料列中既有的資料,來操縱資料或建立新的欄位。能夠執行資料轉換的 SSIS 元件包括衍生資料行 (Derived Column)、資料轉換 (Data Conversion)、多點傳送 (Multicast) 和查閱 (Lookup) 等元件。這些元件可以建立新的資料行,但是資料列轉換無法建立任何額外的記錄,因為每一個輸出資料列與輸入資料列有著 1:1 的關係,以即同步轉換。資料列轉換的好處是能夠再利用既有的緩衝區,無須將資料複製至新的緩衝區,即可完成轉換。

  • 部分封鎖轉換:部分封鎖轉換經常應用於合併資料集。這種轉換經常含有多項資料輸入,因此其輸出結果的記錄筆數比起輸入記錄可能為相同、更多或更少。由於輸入記錄與輸出記錄的筆數可能不相符,這種轉換亦稱非同步轉換。SSIS 中能夠執行部分封鎖轉換的元件包括合併 (Merge)、合併聯結 (Merge Join) 和聯集全部 (Union All)。部分封鎖轉換中,轉換的輸出內容會被複製到新的緩衝區,資料流程中會納入新的執行緒。

  • 封鎖轉換:封鎖轉換得先讀取並處理所有的輸入記錄,才能建立輸出記錄。在所有的轉換類型中,這類的轉換最常見,對於可用資源的影響也最大。SSIS 中的這類元件包括彙總 (Aggregate) 和排序 (Sort)。一如部分封鎖轉換,封鎖轉換也算是非同步轉換,而且資料流程中出現封鎖轉換時,也會建立新的緩衝區,以便輸出和新的執行緒能納入資料流程中。

當然轉換不是唯一可歸類為同步或非同步的元件,來源也是一種特別的非同步元件。舉例來說,RDBMS 來源元件會建立兩種緩衝區:一種用作成功 (Success) 輸出,另一則作為錯誤 (Error) 輸出。反之亦然,目的地亦是一種特別的同步元件。您只要瞧瞧封裝的執行樹狀結構 (Execution Tree),就會看出來源和目的地元件的互動。

執行樹狀結構

執行樹狀結構代表了您的封裝如何使用緩衝區和執行緒。在執行階段期間,資料流程引擎會將「資料流程」工作細分為數個執行樹狀結構,用以指定緩衝區和執行緒在封裝內的分配方式。每一個樹狀結構會建立新的緩衝區,且可能會在不同的執行緒上執行。每當部分封鎖或封鎖轉換添加至管道時,會建立新的緩衝區,也需要額外的記憶體來處理資料轉換。請注意,每一個新的樹狀結構也意味著額外的工作執行緒。

以 [圖 1] 和 [表 1] 的執行樹狀結構為例,兩個 Employee 資料集合而為一,匯總後載入一個共用的目的地資料表。

Dd159900.ssispt01(zh-tw,TechNet.10).gif

[圖 1]:範例封裝

附註:執行樹狀結構以執行順序列在表中。

[表 1]:執行樹狀結構的定義

執行樹狀結構列舉

說明

begin execution tree 2

   output "OLE DB Source Output" (27)

   input "Derived Column Input" (172)

   output "Derived Column Output" (173)

   input "Union All Input 1" (411)

   output "Derived Column Error Output" (174)

end execution tree 2

執行樹狀結構 2 中,SSIS 從 Employee OLE DB 來源將資料讀入管線,「衍生資料行」轉換添加另一個資料行,然後 SSIS 將資料傳入「聯集全部」轉換。這次執行樹狀結構中的所有運作均使用同一個緩衝區,資料讀入 OLE DB 來源輸出後即不再複製。

begin execution tree 3

   output "OLE DB Source Error Output" (28)

   input "OLE DB Destination Input" (2603)

   output "OLE DB Destination Error Output" (2604)

end execution tree 3

執行樹狀結構 3 中,SSIS 建立了一個緩衝區,以便容納來自非同步 Employee OLE DB 來源的錯誤記錄,然後才將該記錄載入目的地錯誤資料表。

begin execution tree 4

   output "Flat File Source Output" (2363)

   input "Union All Input 3" (2098)

end execution tree 4

執行樹狀結構 4 中,SSIS 讀取來自 Employee 一般檔案來源的資料,然後再傳給「聯集全部」。這兩項操作使用同一個緩衝區。

begin execution tree 5

   output "Flat File Source Error Output" (2364)

   input "OLE DB Destination Input" (3818)

   output "OLE DB Destination Error Output" (3819)

end execution tree 5

執行樹狀結構 5 中,建立了一個緩衝區,以便容納來自非同步 Employee 一般檔案來源的錯誤記錄,然後才將該記錄載入目的地錯誤資料表。

begin execution tree 0

   output "Union All Output 1" (412)

   input "Aggregate Input 1" (2472)

end execution tree 0

執行樹狀結構 0 中,執行部分封鎖聯集轉換,建立了一個緩衝區,以便存放匯總資料並計算匯總。

begin execution tree 1

   output "Aggregate Output 1" (2473)

   input "OLE DB Destination Input" (150)

   output "OLE DB Destination Error Output" (151)

end execution tree 1

執行樹狀結構 1 中,完成完全封鎖匯總轉換後,來自「匯總」操作的輸出結果會被複製至新的緩衝區,然後資料會被載入 OLE DB 目的地。

本範例說明如何透過執行樹狀結構,瞭解一般 SSIS 封裝中緩衝區的使用方式。範例中亦解說為何「聯集全部」等部分封鎖轉換,以及「彙總」等完全封鎖轉換能建立新的緩衝區和執行緒,而「衍生資料行」等資料列轉換卻作不到這一點。

執行樹狀結構對於瞭解緩衝區使用方式相當有助益。您可以替自己封裝展現執行樹狀結構,方法是開啟封裝記錄以便記錄「資料流程」工作,然後選取「管線執行樹狀結構」事件。請注意。您必須先執行封裝才會看見執行樹狀結構。執行封裝後,執行樹狀結構會出現在 Business Intelligence (BI) Development Studio 中的「記錄事件」視窗中。

評估設計替代方案

熟悉了各種轉換類型與封裝執行的互動後,就能夠對效能設計做出更明智的選擇。[圖 2] 說明下列兩個不同的設計對於同一個資料整合案例的效能有何影響。

Dd159900.ssispt02(zh-tw,TechNet.10).gif

[圖 2]:設計替代方案

[表 2]:評估設計替代方案

設計描述

本設計中,「指令碼元件」首次經過查閱時產生 100,000,000 筆資料列。如果找不到來源值使得查閱失敗,就會傳送一筆錯誤記錄給「衍生資料行」轉換,並將預設值指派給錯誤記錄。錯誤處理完畢後,錯誤資料列會被合併至原始資料集,才會將所有資料列載入目的地。

一如第一個設計替代方案,這項設計使用同一套「指令碼元件」在經過查閱時產生 100,000,000 筆資料列。

但是它不會把查閱失敗當成錯誤記錄處理,而是忽略所有的查閱失敗,改採「衍生資料行」轉換,將值指派給查閱資料行為 NULL 的資料行。

效能影響

本例中的這兩個執行樹狀結構中,最大的瓶頸在於必須替「部分封鎖聯集全部」轉換建立的記憶體額外複製資料。

這種解決方案的效能比第一個設計替代方案高出 21%,因為它只有一個執行樹狀結構,各項操作經過匯總,避免了將資料複製至新的緩衝區所帶來的額外負擔。

緩衝區大小調整

除了儘量使用資料列轉換,以限制所建立和使用的緩衝區數量,您還能在 SSIS 中操縱緩衝區大小,也就是讀入緩衝區的記錄筆數。您的首要目標就是儘量將許多筆記錄傳入單一個緩衝區,有效地利用記憶體。

影響緩衝區大小

執行期間,SSIS 會在從來源讀取資料前,根據一系列的輸入參數先自動微調緩衝區大小,以便使記憶體獲得最徹底的使用。為了使 SSIS 能夠最有效地微調緩衝區,您必須瞭解下面幾個輸入參數:

  • 估計的資料列大小:[估計的資料列大小] 並非 SSIS 特有的設定,而是 SSIS 在設計階段中,根據所收集的資料來源中繼資料所計算而來。您可以及早在資料流程中識別出所有資料行最小的資料型別,藉此縮小資料行的大小。這對於一般檔案資料來源尤其重要,因為除非您特別設定資料行的資料型別,否則 SSIS 會自動把每一個資料行以字串資料型別讀入。

  • DefaultMaxBufferRows: DefaultMaxBufferRows 是 SSIS「資料流程」工作中可調整的設定,自動設定值為 10,000 筆記錄。SSIS 會將「估計的資料列大小」與 DefaultMaxBufferRows 相乘,約略估計出每 10,000 筆記錄您的資料集有多大。若您不瞭解 DefaultMaxBufferSize,請勿修改這項設定。

  • DefaultMaxBufferSize: DefaultMaxBufferSize 是 SSIS「資料流程」工作的另一個可調整設定。依預設 DefaultMaxBufferSize 的設定為 10 MB。您在調整這項設定時,請記住它的上限範圍受限於一個內部 SSIS 參數 MaxBufferSize,該參數設為 100 MB 且無法變更。

  • MinBufferSize:雖然 MinBufferSize 不可設定,但仍值得瞭解,因為 SSIS 會使用這個內部參數來計算您是否將 DefaultMaxBufferSize 設得太低。MinBufferSize 的細微程度是根據您作業系統的虛擬記憶體配置功能而定義,通常是 65,536 位元,但是每台電腦不同。

以下面各種分析藍本為例,SSIS 會根據您對這些輸入參數的設定值,於執行時期微調緩衝區大小:

  • 分析藍本 1:估計的資料列大小 * DefaultMaxBufferRows 若超過 MaxBufferSize,SSIS 會調降特定緩衝區可儲存的資料列筆數,以便管理記憶體的支配。

    舉例來說,如果 SSIS 計算「估計的資料列大小」為每筆記錄 15,000 位元,那麼估計的緩衝區大小應該為 15,000 位元/每筆記錄 * 10,000 筆記錄,結果約為 143 MB,是 DefaultMaxBufferSize 100 MB 的 1.5 倍。由於預估大小超出 DefaultMaxBufferSize,SSIS 會將每一個緩衝區的記錄筆數調降為原值除以 1.5,取得低於 100 MB 的閾值。本例中,每一個緩衝區大小約為 6,600 筆記錄。請注意,進行這項調整作業時,SSIS 並不知道您的來源檔案中有多少筆記錄。而實際處理資料時,SSIS 會視情況儘量替緩衝區建立多個執行案例,以便容納來源資料集。繼續以方才的例子說明,若您有 200,000 筆來源資料集,SSIS 會替該緩衝區型別建立約 30 個緩衝區執行案例。所謂緩衝區型別指的是緩衝區的單欄式結構。舉例來說,資料行 A、B、C 的緩衝區在 SSIS 看來就是型別為 A、B、C 的緩衝區。實際上 SSIS 會更進一步替每一個緩衝區型別指派一個數值。

  • 分析藍本 2:估計的資料列大小 * DefaultMaxBufferRows 若低於 MaxBufferSize,SSIS 會調升特定緩衝區可儲存的資料列筆數,以便使記憶體得到最佳利用。

    舉例來說,如果「估計的資料列大小」為每筆記錄 5 位元 (比上一個例子小多了) ,那麼 5 位元/每筆記錄 * 10,000 筆記錄,結果約為 48 MB,比 MinBufferSize 的 64 MB 還小。由於預估大小低於 MinBufferSize,SSIS 會稍微調升記錄筆數以達到 64 KB 的閾值。

  • 分析藍本 3:估計的資料列大小 * DefaultMaxBufferRows 若介於 MinBufferSizeDefaultMaxBufferSize,SSIS 會利用 MinBufferSize 的倍數微調緩衝區大小,儘量使它接近「估計的資料列大小 * DefaultMaxBufferRows」的結果,以便使記憶體得到最佳利用。

    舉例來說,如果「估計的資料列大小」為每筆記錄 500 位元。500 位元/每筆記錄 * 10,000 筆記錄,結果約為 4.8 MB,比 DefaultMaxBufferSize 的 10 MB 來得小,但是大於 MinBufferSize 的 64 KB。本例中,SSIS 會微調緩衝區大小,使它儘量接近 4.8 MB。

緩衝區的使用原則

實際操作時,您必須在自己的環境中測試這些設定。您可以從下面幾項原則著手。

  • 儘量降低「估計的資料列大小」,移除不必要的資料行並對資料型別作正確的設定。在操作執行前儘量降低來源資料集的大小,節省記憶體資源。

  • DefaultMaxBufferRowsDefaultMaxBufferSize 採用 SSIS 的預設設定,以此為出發點。啟用 BufferSizeTuning 屬性,啟用封裝記錄功能,這個屬性會將資訊添加至記錄檔,顯示 SSIS 在哪裡調整了緩衝區大小。您會看見類似下面的項目。

  • 若您的資料整合類似「分析藍本 1」,您會看見:緩衝區型別為 0 的資料列會使得緩衝區大小超過設定的最大值。這種緩衝區裡面只有 383 筆資料列。

  • 若您的資料整合類似「分析藍本 2」,您會看見:緩衝區型別為 3 的資料列會使得緩衝區大小低於設定的最小值 65536 位元。這種緩衝區裡面只有 1365 筆資料列。請注意,65536 位元是這個封裝執行的機器具有的 MinBufferSize。

  • 調整 DefaultMaxBufferRowsDefaultMaxBufferSize 的值,儘量將多筆記錄納入緩衝區。如果這些值設定過低,會造成 SSIS 建立數量多但容量小的緩衝區,而非建立數量少但容量大的緩衝區,後者在記憶體足夠時比較理想。

  • 您在調整 DefaultMaxBufferRowsDefaultMaxBufferSize 時會注意到一點:一旦超出 MaxBufferSize,那麼 MaxNumberofRows 的設定就不再重要,因為 SSIS 一向會調降每一個緩衝區的記錄筆數,使記憶體得到最徹底的利用。

  • 附帶一提,DefaultMaxBufferRowsDefaultMaxBufferSize 是在每一個「資料流程」工作中分別設定的。使用「資料流程」工作將數個資料來源的資料加以整合時,這兩項設定只會影響該工作的資料來源元件和轉換。請注意,您在判斷每一個緩衝區的資料行數目時,要注意緩衝區的型別。

平行處理

平行處理是一項相當有效的技巧,可以改善資料整合作業的效能。SSIS 本身即支援封裝、工作和轉換的平行處理。而平行處理要能成功,關鍵就在於根據您系統資源本身的限制來設定作業。

可調整的設定

在 SSIS 中,每一個封裝的控制流程都受制於一個叫做 MaxConcurrentExecutables 的設定,它可以指定每一個封裝可平行處理的 SSIS 執行緒數目上限。這個值預設為 -1,也就是邏輯機器處理器的數目加上 2。

如果 SSIS 在專用伺服器上執行,而且有許多需平行處理的作業,只要有些作業得等待許久,外部系統才會回應,您就應該調升這項設定。反之亦然,如果沒有 SSIS 專屬的機器,而整合應用程式與好幾個其他的應用程式一併執行,您應該調降這項設定避免資源衝突。

設計方式

您在設計適於平行處理的封裝時,應該決定封裝中的應用程式要全部或部分採平行處理。一如緩衝區大小,您在設計平行處理時也應該以可用的系統資源為首要考量。

以不同的設計方式將平行處理套用至可從來源資料庫讀取資料的配套步驟,以四種不同方式匯總資料,然後將每種匯總資料集載入不同的目的地資料表,這其中各方面的取捨要仔細考量。

附註:這些資訊是在 Unisys Center of Excellence 進行的 64 位元效能研究收集得來,研究環境為數台 Unisys ES7000 伺服器構成。如需該項研究的詳細資訊,請參閱 http://www.unisys.com/eprise/main/admin/corporate/doc/ELTSQL.pdf 中的《ETL Performance》白皮書 (英文)。目前另有一項以 AMD 64 位元核心機器的類似研究正在進行,最新資訊請參閱 AMD 網站:http://www.amd.com/us-en/ (英文)

  • 平行處理目的地作業:[圖 3] 的設計方法中,資料自來源讀入後,通過一個匯總轉換,依作業分成四組並載入四個目的地資料夾。這份設計中,唯一的平行作業是從匯總輸出載入四個目的地資料表。剖析來源檔案與匯總計算並非平行操作。

    Dd159900.ssispt03(zh-tw,TechNet.10).gif

    [圖 3]:平行處理目的地作業

    若您的機器上有數顆 CPU,那麼上述的方法並不能幫助您有效地利用這些資源。因為這種設計方式最適於記憶體有限的電腦,而且您有多個彼此衍生的多個匯總。匯總轉換會以最低的細微程度建立匯總,然後從它衍生出所有相關的匯總。舉例來說,如果您有兩項匯總:(1) 以年匯總的銷售資料 (2) 以年和地區匯總的銷售資料,匯總轉換會自動建立依年和地區匯總的銷售資料,然後從它衍生出年度摘要。

  • 部分平行處理作業:[圖 4] 的設計方法中,資料自來源讀入後,通過一個多點傳送轉換,建立四個類似的結果集並傳至四個不同的匯總轉換,然後載入四個目的地資料夾。

    Dd159900.ssispt04(zh-tw,TechNet.10).gif

    [圖 4]:部分平行處理作業

    不要驚訝,本例中唯一一個平行處理只有載入目的地資料表。而讀取資料來源、執行多點傳送、匯總資料,這些都是在同一個執行樹狀結構中進行,因此共享同一個記憶體和執行緒。

    若您希望在本例中以平行處裡執行匯總,可以在「多點傳送」後插入「聯集全部」轉換,以便建立新的執行樹狀結構。請記住,「聯集全部」屬於部分封鎖轉換,因此一定會建立新的執行樹狀結構。引入「聯集全部」後,資料會被複製到額外的緩衝區,您也會獲得額外的執行緒以便平行處理匯總。

  • 平行處理所有作業:[圖 5] 的設計方法中,四組不同的作業各自從來源讀入資料後,進入匯總,然後將資料載入特定的目的地。

    Dd159900.ssispt05(zh-tw,TechNet.10).gif

    [圖 5]:平行處理所有作業

    這個分析藍本中,所有的運作採平行作業:來源目的擷取、匯總和插入資料庫。如果您的伺服器不受記憶體的限制,並具有數顆 CPU,這種方式可達到高效能;然而這種作法使資料各自被讀取四次,而且以統一模式對待每一項作業,會浪費資源。

  • 最佳化最慢的作業:這可以作為統一平行處理的替代方案。您可以利用如 [圖 6] 所示的有目標設計,瞄準封裝中速度最慢且能從平行處理獲益的元件。這種設計手法從四個匯總作業中找出最慢的一個,然後將它細分為不同的資料流。

    Dd159900.ssispt06(zh-tw,TechNet.10).gif

    [圖 6]:最佳化最慢的作業

    為了提昇新資料流的效能,匯總已經由條件式分割為兩個「部分」,以鍵值範圍區分來源資料。每個匯總經過計算後,以「聯集全部」轉換合併資料集。結果資料會被載入目的地。

    如上述綜合設計所示,您可以將平行處理應用在效能提昇最明顯的作業上,無須浪費電腦的資源。

效能的衡量

成功的效能微調最關鍵的一點就是能否衡量並監督長期的效能表現。調整 SSIS 封裝的效能之餘,您要有管道測量這些修改是否帶來正面效果。

疑難排解

要瞭解效能不佳癥結所在,最好的方法就是將封裝各部分的執行加以隔離,找出最慢的作業。將數個作業的執行隔離後,就可以建立一個可長期追蹤的基準。隔離 SSIS 作業的步驟如下:

  1. 訂定封裝整體執行速度:執行封裝時的整體執行速度指的是從開始到結束的總時間。

    總體執行速度 = 來源速度 + 轉換速度 + 目的地速度

    舉例來說,若您執行封裝的時間從頭到尾共費時 5 分鐘,這就是「總體執行速度」。

  2. 隔離來源和轉換速度:您可以藉著下列步驟,找出 SSIS 從癌原讀取並執行轉換的速度。

    1. 建立原始封裝的副本。

    2. 移除與來源關聯的目的地。若您有超過一個目的地,請一次只隔離一個。

    3. 以 RowCount 轉換替代目的地。

    4. 測量從「來源」、「轉換」以至「RowCount」的執行效能。

    測量出的結果就是「來源和轉換速度」。如果這樣太慢,而且您有額外資源 (如 CPU),可以採用上面介紹過的一些技巧 (例如分割或引入新的執行樹狀結構) 來提昇輸送量。

    找出問題所在期間,您會在封裝的幾個部分使用 RowCount 轉換,隔離出效能上的瓶頸。RowCount 轉換只會計算通過管線的記錄筆數,並不會對效能帶來什麼負擔。

    舉例來說,執行這些步驟後您瞭解到從來源讀取資料和執行轉換共需 4 分鐘。與「總體執行速度」的 5 分鐘相較,80% 的執行時間用於讀取和轉換資料。只要簡單的運算,就可以得出目的地的速度。

  3. 計算目的地的速度:一旦您知道「總體速度」和「累計來源與轉換速度」,即可著手計算出載入目的地的時間:

    目的地速度 = 總體速度 – (累計來源和目的地速度)

    舉例來說,從步驟 1 和 2 的測量結果,您可以得出「目的地速度」如下:

    目的地速度 = 5 分鐘– (4 分鐘) = 1 分鐘

  4. 隔離來源速度:步驟 2 中我們計算出累計來源和轉換速度。由於這是一個累計數字,我們還不知道讀取資料和轉換資料各花了多少時間。若要計算出 SSIS 從資料來源讀取資料的速度,請執行下列步驟:

    1. 建立原始封裝的副本。

    2. 移除所有與來源關聯的轉換和目的地。

    3. 以 RowCount 轉換替代它們。

    4. 測量從「來源」以至「RowCount」的執行效能。

    測量出的結果就是「來源速度」。理論上來說這是資料流程最快的運轉速度。如果太慢,您可能需要把重點放在提昇來源配接器,好讓它能更快地傳回資料列。舉例來說,執行該流程後您發現從檔案資料來源讀取資料共需 15 分鐘。與「總體執行速度」的 5 分鐘相較,6% 的執行時間用於讀取資料。現在只要簡單的運算,就能輕鬆盼對出轉換資料實際花費的時間。

  5. 計算轉換速度:您可以藉著隔離「來源速度」和「累計來源和轉換速度」,找出「總體轉換速度」,如下所示:

    轉換速度 = (累計來源和目的地速度) - 來源速度

    舉例來說,從步驟 1 到 3 的測量結果,您可以得出「轉換速度」如下:

    轉換地速度 = 4 分鐘– (15 秒) = 3.75 分鐘

    現在您已經計算出各個主要的速度參數,如 [表 2] 中所示。

    [表 2]:效能摘要

    ?

    執行時間

    來源速度

    15 秒

    + 轉換速度

    3.75 分鐘

    = 累計來源和轉換速度

    4?分鐘

    + 目的地速度

    1 分鐘

    = 封裝整體執行速度

    5?分鐘

  6. 隔離個別轉換:如果您想要更深入瞭解特定轉換的效能,進一步排解疑難,請遵照下列步驟:

    1. 替您在步驟 4 中使用的封裝建立一份副本,隔離「來源和轉換速度」。

    2. 移除一個轉換,

    3. 以 RowCount 轉換取而代之,而不動到其他的轉換和目的地。

    4. 衡量執行效能。

    5. 將執行效能與原先的「來源和轉換」速度相比。所得的差就是該轉換的效能。

    舉例來說,由於超過 60% 的時間用於轉換,您可以將重點放在找出是哪一個轉換花最多時間。移除查閱轉換後,您發現「累計來源和轉換速度」從原先的 4 分鐘降至 2.5 分鐘。比較這兩種情況後,您可以計算出查閱轉換費時 1.50 分鐘。[表 3] 列出隔離查閱後得出的效能摘要。

    [表 3]:隔離查閱效能摘要

    原先包含查閱的執行時間

    疑難排解不包含查閱的執行時間

    得出的查閱時間

    來源速度

    15 秒

    15 秒

    0 (沒有變更)

    + 轉換速度

    3.75 分鐘

    2.25 分鐘

    1.5 分鐘

    = 累計來源和轉換速度

    4?分鐘

    2.5?分鐘

    1.5?分鐘

    您可以根據這項資訊微調查閱轉換的快取,將效能從 1.5 分鐘改善為 30 秒,使執行時間縮短 20%,如 [表 4] 所示。

    [表 4]:最佳化的效能摘要

    原先的執行時間

    最佳化後的設計執行時間

    來源速度

    15 秒

    15 秒

    + 轉換速度

    3.75 分鐘

    2.75 分鐘

    微調過後的查閱轉換

    1.5 分鐘

    30 秒

    其他轉換

    2.25 分鐘

    2.25 分鐘

    + 目的地速度

    1 分鐘

    1 分鐘

    = 封裝整體執行速度

    5?分鐘

    4?分鐘

這個範例顯示如何用簡單的方式隔離封裝的作業,您也可以將同樣的技巧應用於更複雜的封裝,更快地找出需要微調的元件。

看得更透徹

現在您已經學會如何調整並改善設計,接下來探討如何利用其中的功能來監督並記錄封裝執行的中繼資料。

  • SSIS 記錄:SSIS 可讓您將工作和封裝記錄至各種記錄提供者,例如 XML、SQL Server 或文字檔案。記錄可讓您檢視一段時間內的封裝效能,並追蹤電腦資源變更和資料量提昇後的效能表現。在此一提,SSIS 提供強大的記錄支援,如果要全面使用佔用的資源不少。請檢視可用的記錄事件,並只記錄所需的事件。

  • SSIS 效能計數器:SSIS 提供數種效能計數器,可幫助您更透徹地瞭解封裝執行期間資源是如何使用。舉例來說,您可以檢視封裝執行期間所讀取的資料列筆數以及使用的緩衝區數目。最有用的效能計數器要算多工緩衝 (Buffers Spooled)。如果 Microsoft Windows® 在執行封裝時耗盡所有的實體記憶體,或是處理在執行封裝期間耗盡虛擬記憶體,SSIS 會開始以多工緩衝處理檔案。一旦發生這種情況,效能會大幅滑落,因此有必要監督這項設定,確保您的作業有足夠的記憶體空間可用。

  • SQL Server Profiler:從 SQL Server 擷取資料,或將資料載入 SQL Server 時,可以使用 SQL Server Profiler 檢視 SQL Server 背景中進行的作業和查詢計畫。監督資料庫活動時,您可能會發現 SQL Server RDBMS 中有需要微調改進的地方,例如需要調整索引配置。

結論

效能微調是一項持續進行的工作,使設計決策和可用資源間取得微妙的平衡。為了方便您管理平衡的工作,SSIS 提供了有彈性的資料架構,可用於建構高效能的資料整合解決方案。深入瞭解 SSIS 效能架構如何使用記憶體和 CPU 後,您能夠做出更明智的決策,利用各項效能機轉,使您的系統資源獲得最有效的利用。隨著解決方案的發展,您亦能更確切地瞭解調整需求。

其他相關資訊:

http://www.microsoft.com/taiwan/technet/prodtechnol/sql/default.mspx

關於作者

Elizabeth Vitt,Intellimentum

Elizabeth Vitt 在商業開發、專案管理、諮詢和商業智慧的訓練方面具有十年的經驗。業界經驗包括將 BI 導入零售、製造和金融服務業。她在教授資料倉儲、ETL,以及 OLAP 的設計與實作方面有獨到經驗,曾替 Microsoft Business Intelligence 產品撰寫 Microsoft Official Curricula 課程,並替 MSPress 的《Business Intelligence: Making Better Decisions Faster》一文執筆。為迎接 SQL Server 2005,筆者亦成功地為搶先採用的客戶成功導入 SQL Server 2005。

Hitachi Consulting

Hitachi Consulting 是 Hitachi, Ltd. (NYSE:HIT) 的全球顧問公司,領導業界,為跨足多項產業的 Global 2000 大企業提供嚴謹的商業和 IT 解決方案。Hitachi Consulting 集結數十年來在商業流程、特定行業、尖端科技等方面累積的經驗,深入瞭解每一家企業獨特的商業需求。為部署應用程式開發商業策略方面,Hitachi 的顧問致力於協助客戶快速瞭解有形的商業價值,實現持續的投資效益。

Hitachi Consulting 是 Microsoft 在 Business Intelligence 的黃金級策略夥伴,獨家提供 Microsoft SQL Server 2005 Business Intelligence Ascend 訓練課程教材和指導人員。Hitachi Consulting 在系統整合方面也有豐富的經驗,成功地將 SQL Server 2005 BI 導入參與 Microsoft Technology Adoption Program (TAP) 的企業。

Hitachi Consulting 以客為尊,互動合作,透過每一次的接觸傳授知識。如需詳細資訊,請參閱 www.hitachiconsulting.com (英文) 。Hitachi Consulting – Inspiring your next success®

下载

Dd159900.icon_Word(zh-tw,TechNet.10).gif下載整份《Integration Services:效能微調技巧》白皮書

顯示: