SQL Server整合服務最佳實務:語句最佳化

標籤：分析   企業   share   task   can   bin   padding   sql   style   

   

SQL Server整合服務(SQL Server Integration Services，SSIS)在其前輩DTS(Data Transformation Services，資料轉換服務)的根基上進步了不少，從可用性、效能和並行等方面來說，它已經成長為一個企業級ETL(Extraction, Transformation and Loading，抽取、轉換和載入)產品，除了是一個ETL產品外，它也供給了各種內建任務來管理SQL Server執行個體。雖然SSIS的內部架構已經被設計為供給極好的效能和平行處理能力，但如果遵守最佳實務，其效能還可進一步最佳化，在本系列文章中，我將討論SSIS的最佳實務，我會將我過去幾年學習和應用SSIS的經驗與大家分享。

　　正如上面所說的，SSIS是DTS(SQL Server 7/2000)的替代產品，如果你曾經應用過DTS，你會發現SSIS包和DTS包非常類似，但本色上已經發生了很大的變更，SSIS不是DTS的增強版本，而是從零開始構建的一個新產品，與DTS相比，SSIS供給了更好的效能和平行處理能力，並克服了DTS的許多限制。

　　SSIS 2008進一步增強了內部資料流管道引擎，供給了更好的效能，你可能已經看到了SSIS 2008創造的一個ETL世界記載，那就是在半小時內載入1TB資料。

　　SSIS的最大好處是它是SQL Server的一個組件，它可以隨SQL Server安裝而免費獲得，不再需要為它購買額外的許可，BI開發人員、資料庫開發人員和DBA都可以應用它轉換資料。

　　最佳實務1：抽取大宗量資料

　　最近我們從一個有3億條記載的大表中抽取資料，起初，當SSIS包啟動時一切正常，資料如預期的那樣在轉換，但效能開始逐漸下降，資料轉換速率直線下降。通過分析，我們發現目標表有一個主聚集鍵和兩個非聚集鍵，因為大量資料插入這個表，導致其索引片段水平達到了85%-90%。我們應用索引線上重建特徵重建/重組索引，但在載入期間，每過15-20分鐘，索引片段水平又回到90%，最終資料轉換和並行履行的線上索引重建曆程花了12-13個小時，遠遠越過了我們的預期。

　　我們想出了一個辦法，，當轉換開始前，我們將目標表的索引整個刪掉，轉換收場後又再重新建立索引，通過這樣處理後，全部轉換曆程花了3-4小時，完全符合我們的預期。

　　全部曆程我畫在下面的圖中了。因此我建議如果可能，在插入資料前，刪掉目標表上的所有索引，特別是插入大資料量時。

轉換資料前，刪除目標表上的所有索引，轉換完後，再重建索引

 

　　最佳實務2：避免應用select *

　　SSIS的資料流程工作(Data Flow Task，DFT)應用一個緩衝區作為資料轉送和轉換的中轉站，當資料從源表傳輸到目標表時，資料首先進入緩衝區，資料轉換是在緩衝區中完成的，轉換完畢後才會寫入到目標表中。

　　緩衝區的大小受伺服器硬體本身限制，它要估算行的大小，行大小是通過一行中所有列大小的最大值求和得出的，因此列數越多，意味著進入緩衝區的行就會越少，對緩衝區的需求就會越多，效能就會下降。因此轉換時最好明確指定需要轉換到目標表的列。即使你需要源表中的所有列，你也應該在select語句中明確指定列的名稱，如果你應用select *，它會繞到源表收集列的中繼資料，SQL語句履行光陰自然就會長一些。

　　如果你將目標表不需要的列也做了轉換，SSIS將會彈出警告提示資訊，如：

Code highlighting produced by Actipro CodeHighlighter (freeware)
-->　　[SSIS.Pipeline] Warning: The output column "SalariedFlag" (64) on output "OLE DB Source Output" (11) and component "OLE DB Source" (1) is not subsequently used in the Data Flow task.
　　Removing this unused output column can increase Data Flow task performance.
　　[SSIS.Pipeline] Warning: The output column "CurrentFlag" (73) on output "OLE DB Source Output" (11) and component "OLE DB Source" (1) is not subsequently used in the Data Flow task.
　　Removing this unused output column can increase Data Flow task performance.

　　當你在OLEDB源中應用“表或視圖”或“來自變數的表名或視圖名”資料造訪模式時要小心，它的行為和select *一樣，都會將所有列進行轉換，當你確鑿需要將源表中的所有列整個轉換到目標表中時，你可以應用這種法子。

　　最佳實務3：OLEDB目標設定的影響

　　下面是一組會影響資料轉換效能的OLEDB目標設定：

　　資料造訪模式：這個設定供給“快速載入”選項，它應用BULK INSERT語句將資料寫入目標表中，而不是簡單地應用INSERT語句(每次插入一行)，因此，除非你有特殊需求，否則不要更改這個快速載入預設選項。

　　維持一致性：預設設定是不會反省的，這意味著目標表(如果它有一個識別欄位)將會建立自己的標識值，如果你反省這個設定，資料流程引擎將會確保源標識值受到保護，會向目標表插入相同的值。

　　維持空值：預設設定也是不會反省的，這意味著來自源表中的空值將會插入到目標表中。

　　表鎖：預設設定是要反省的，建議維持預設設定，除非是同一時刻還有其它進程應用同一個表，指定一個表鎖將會取得全部表的造訪權，而不是表中多行的造訪權，這很可能會引發連鎖反應。

　　反省約束：預設設定是要反省的，如果你能確保寫入的資料不會違反目標表上的約束，建議不要反省，這個設定會指定資料流管道引擎驗證寫入到目標表的資料，如果不反省約束，效能會有很大提升，因為省去了反省的開銷。

　　最佳實務4：每批插入的行數以及最大插入大小設定的影響

　　每批插入的行數：這個設定的預設值是-1，意味著每個輸入行都被看做是一個批次，你可以改變這個預設行為，將所有行分成多個批次插入，值只允許正整數，它指定每一批次包孕的最大行數。

　　最大插入提交大小：這個設定的預設值是“2147483647”，它指定一次提交的最大行數，你可以改動這個值，注意，如果這個值設得太小，會導致提交次數增加，但這樣會釋放交易記錄和tempdb的壓力，因為大宗量插入資料時，對交易記錄和tempdb的壓力是非常大的。

　　上面兩個設定對於理解改良tempdb和交易記錄的效能是非常首要的，例如，如果你維持最大插入提交大小的預設值，在抽取期間交易記錄和tempdb會不斷變大，如果你傳輸大宗量資料，記憶體很快就會消費光，抽取就會失敗，因此最好基於你自身的環境為其設定一個合理的值。

　　注意：上面的建議得益於我多年的DTS和SSIS應用經驗，但如前所示，還有其它因素影響效能，如根基設施和網路環境，因此，當你將這些措施推向生產環境之前，最好做一次徹底的測試

SQL Server整合服務最佳實務:語句最佳化