在SQL Server中的關係型數據倉庫分區策略(1)

  • http://developer.51cto.com  2007-02-27 17:01  Gandhi Swaminathan  51CTO.com  我要評論(0)

  • 分區，在Microsoft SQL Server中，便於對高可用關係型數據倉庫進行有效的管理。本白皮書討論了在此環境中影響分區策略以及設計，實現，管理等需要考慮事項的幾個因素。

  •  
    我們推薦本文的讀者已經閲讀並且理解以下的文章：
    ◆使用一個Microsoft SQL Server 2000數據倉庫中的分區- http://msdn.microsoft.com/library/default.asp?URL=/library/techart/PartitionsInDW.htm◆SQL Server 2000 步進的批量導入案例學習 - http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx◆Kimberly L. Tripp 所作的SQL Server 2005 分區表和索引 -
    http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm本白皮書關注的重點是關係型數據倉庫和表分區。它的目標讀者是：
    ◆通過使用Microsoft SQL Server中的分區視圖實現了分區的開發人員和數據庫管理員。這類讀者將會得益於SQL Server 2005中的分區優勢以及關於滑動窗口實現和策略的部份。
    ◆在未來計劃使用分區的開發人員和數據庫管理員將會通過詳細閲讀本白皮書而獲益非淺。
    數據庫和系統管理員將會得益於有關存儲域網絡管理以及優化I/O利用策略的部份。
    內容列表
    對一個關係型數據倉庫進行分區 
    關於關係型數據倉庫 
    分區的好處 
    在 SQL Server 7.0/2000中的分區技術 
    在SQL Server 2005中的分區技術 
    在SQL Server 2005中分區的優勢 
    標識一個查詢計劃中的 Demand Parallelism 
    從SQL Server 2000的分區視圖遷移到 SQL Server 2005 分區表/索引 
    影響關係型數據倉庫分區的因素 
    數據量 
    數據導入 
    索引 
    數據老化 
    數據存檔 
    查詢性能 
    滑動窗口實現 
    交換分區的最佳實踐 
    將數據存儲到一個性價比高I/O子系統的技術 
    關係型數據倉庫的分區策略 
    策略 I – 將一個分區綁定到它自己的文件組 
    策略Strategy II – 將兩個或更多分區綁定到同樣的文件組 
    哪個策略更好? 
    結論 
    附錄 A: 性能數值 
    批量插入性能 
    轉換性能 
    索引構建性能 
    數據庫備份性能 
    老化數據到ATA 磁盤 
    附錄 B: 平台列表 
    Microsoft 軟件 
    服務器平台 
    存儲 
    主機總線適配卡 
    存儲管理軟件 
    附錄 C: 服務器體系結構 
    附錄 D: EMC CLARiiON 存儲     
    拓樸 
    附錄 E: 存儲隔離 配置你的存儲 
    附錄 F: 腳本 
    對一個關係型數據倉庫進行分區
    以下的部份將會簡要的解釋關係型數據倉庫的概念，為關係型數據倉庫進行分區的好處，以及遷移到Microsoft® SQL Server™ 2005分區的好處。
    關於關係型數據倉庫
    關係型數據倉庫提供了一個廣泛的數據來源以及一個用來構建業務智能（BI）解決方案的體系結構。另外，關係型數據倉庫可以為報表應用程序以及複雜且專用的SQL查詢所用。
    一個典型的關係型數據倉庫是由維度表以及事實表組成的。維度表通常會比事實表小一些並且其中提供了關於解釋事實的屬性的詳細信息。一個維度的例子是貨物，商店和時間。事實表提供了對商業記錄的描述，比如在所有商店中貨物銷售的信息。事實表通過最近收集到的數據進行不斷的更新。
    一個成功的關係型數據倉庫解決方案的實現包括細緻而長期的規劃。以下列出了在構建一個關係型數據倉庫時要考慮的要素：
    ◆數據量
    ◆數據導入窗口
    ◆索引維護窗口
    ◆工作負載特徵
    ◆數據老化策略
    ◆存檔和備份策略
    ◆硬件特徵
    這個文檔後面的部份將會有對以上要素的詳細討論。
    一個關係型數據倉庫在實現時可以採用分區的方法或者一個（巨大）事實表的方法。對於使用分區還是不分區方式的設計選擇主要依賴於前面列出的各個要素。關係型數據倉庫可以從數據分區中獲益。以下部份着重談到了分區為關係型數據倉庫帶來的好處。

分區的好處

當組織中的數據庫向上擴展並且包含了大量的數據時，非常關鍵的是保持其高可用性並同時適應對小的數據庫維護窗口的需要。這些需求使得分區成為對於超大型數據庫而言的一個量身定製的技術。分區技術所強調的關鍵問題是——通過將非常大的表分解成相對較小的分區從而使諸如數據導入，老化以及存檔等重要任務的管理更易於進行。Microsoft SQL Server通過在SQL Server 7.0/2000中的分區視圖以及在SQL Server 2005中添加的對分區表的支持提供了分區技術。

在 SQL Server 7.0/2000中的分區技術

SQL Server 7.0通過分區視圖引入了對分區技術的支持。在SQL Server 2000中，這一功能進行了增強支持了可更新的分區視圖。當事實表可以被自然的分割或者根據數據範圍劃分成單獨的表時，對於關係型數據倉庫而言分區視圖技術是再合適不過的了。分區視圖的基表可以被UNION來表示成一個統一的數據集。分區視圖大大降低成本應用程序的複雜性，原因是物理實現被從應用程序數據訪問方式中抽象了出來。

在SQL Server 2000中，分區視圖可以被擴展到包括分佈式分區視圖，從而啓用跨多個服務器/實例的數據庫聯合。有關分佈式分區視圖的討論超出了本文的範圍。對此更詳細的討論，請參閲微軟開發人員網絡（MSDN）上的“分佈式分區視圖”，地址是：http://www.microsoft.com/sql/evaluation/features/distpart.asp!href(http://www.microsoft.com/sql/evaluation/features/distpart.asp.

在SQL Server 2005中的分區技術

在SQL Server 2005中通過使用表和索引的分區，可以降低在使用分區視圖管理非常大的數據庫時的複雜性。SQL Server 2005提供了用數據行作為最小的分區單位的水平範圍分區功能。可以被分區的對象有：

◆基表
◆索引（聚簇和非聚簇的）
◆索引視圖

範圍分區是通過自定義的數據範圍定義的表的分區。用户通過分界值，一個使用文件組映射的分區架構，以及映射到分區架構的表來定義分區函數。一個分區函數決定了一個表或索引中特定的一行所屬於的分區。每個分區都是用一個通過一個分區架構映射到某個存儲位置（文件組）的分區函數來定義的。對於在SQL Server 2005中實現分區的全面討論，請參閲MSDN中的"SQL Server 2005 分區表和索引"。

以下部份闡述了SQL Server 2005中分區功能的優勢並提供了將分區表遷移到SQL Server 2005的策略。

在SQL Server 2005中分區的優勢

在SQL Server 2005中的表和索引的分區功能通過將其分解為更易管理的分區大大方便了對超大型數據庫的管理。這一部份涉及了一些在針對關係型數據倉庫的使用中分區表相對於分區視圖的優勢。

管理

一個使用分區視圖的缺點是當你使用它的時候，數據庫操作必須對單個的對象執行而不是對視圖本身。舉個例子，如果一個現存的索引必須被刪除並且要創建一個新的索引，這些操作必須在每個相關的基表上執行。

在SQL Server 2005中，諸如索引維護這樣的數據庫操作是對分區表本身而不是底層的相關分區上進行的，因而在管理索引過程中大大減輕了負擔。

更好的Parallelism機制

在SQL Server 2000中，操作是在單個表上執行的並且數據在一個分區視圖的級別進行聚合。來自於基表中的行通過使用串聯運算符進行彙集並顯示視圖。然後再在結果集數據上執行聚合。

在SQL Server 2005中，對分區表執行的查詢使用了一個被稱為demand parallelism的新的運算符。Demand parallelism受到系統資源和MAXDOP設置的影響。

使用分區表的查詢將會比使用分區視圖進行的同樣查詢更快的進行編譯。當使用分區視圖時查詢的編譯時間是與分區的數量成正比的，而使用分區表時查詢的編譯時間不會受到分區數量的影響。

在某些情況下，對分區視圖進行查詢可能會更好一些。以下描述了這樣的情況：

◆當優化器選擇使用demand parallelism時，parallelism的最小單位是一個分區。在SQL Server 2005中對一個分區表中的單個分區進行查詢的性能可能不太好，原因是parallelism的級數被限制到了1。同樣的查詢如果對一個分區視圖進行可能會好一些，原因是在一個分區內更好的parallelism
◆當分區的數量少於處理器的數量時使用分區視圖會更好一些，原因是通過parallelism可以更好的使用處理器資源。當分區的數量大於處理器數量，而數據並不是在分區間平均分佈時，對分區表查詢的性能可能仍舊不太好
◆當分區中的數據分佈不均時使用對分區視圖的查詢也會更好一些

標識一個查詢計劃中的 Demand Parallelism

下面是一個查詢計劃的示例，它是由一個加法聚合查詢產生的。

劃了紅圈的部份標明瞭在查詢計劃中出現的demand parallelism。嵌套循環運算符左邊的子demand parallelism是用分區ID來表示的。嵌套循環運算符右邊的子demand parallelism是用分區表自身來表示的。在這張圖表中，對於由左邊的子demand parallelism所返回的每一個分區ID，一個並行的索引查找運算符對來自對應的分區中的行進行反覆掃描。所有在嵌套循環運算符上進行的操作也受到由demand parallelism所建立的並行線程的數量的影響。左邊的子demand parallelism表示了僅當分區剪切生效時，也就是當查詢通過分區篩選結果時，被查詢所影響的分區ID。

　　圖表1：標識 demand parallelism

　　從SQL Server 2000的分區視圖遷移到 SQL Server 2005 分區表/索引

 

從SQL Server 2000的分區視圖遷移到 SQL Server 2005 分區表/索引

一個現存的基於單個巨表或者分區視圖的應用程序可以被重構或者遷移到一個基於分區的SQL Server 2005解決方案。要作出是重構還是遷移應用程序的決定，必須詳細分析在性能，可管理性，以及可用性方面存在的需求。

一個將SQL Server 2000的分區視圖遷移到SQL Server 2005分區表的簡單路徑將包括以下步驟：

◆創建一個分區函數和架構以確定每個分區的分界點和物理存儲位置。分界點應當和分區視圖的基表的差不多
◆在新建的分區架構上創建一個分區表。該表應當指定與分區視圖的基表同樣的物理結構，包括索引
◆將分區視圖的每個基表交換為新建的分區事實表的一個分區。分區架構所關聯的文件組必須與被交換進來的表所屬的文件組相匹配。另外，要遷移的表必須符合交換提示的要求。舉個例子，目標表不能是一個與架構綁定的視圖的部件。關於交換提示的要求列表，請參閲SQL Server 2005聯機叢書中的“使用分區交換有效的傳遞數據”

影響關係型數據倉庫分區的因素

對於一個分區的關係型數據倉庫的成功實現而言，包括了對數據庫增長和易管理性的規劃。接下來的部份闡述了影響關係型數據倉庫分區的因素以及滑動窗口實現的詳細信息。

數據量

當事實表的大小比較小時，分區只會添加更多的管理複雜性而不會帶來更多的價值。事實表的大小是基於應用程序的特點並且由每一種實現方式所決定的。通常用户需要事實表在他們實施分區之前至少有100 GB。

數據導入

數據導入是一個數據倉庫的核心部份。幾乎所有的數據倉庫都會週期性的處理最近收集的數據。是否成功的管理數據倉庫取決於批量導入進程的效率以及導入過程中現有的數據能否繼續使用。

在構建你的事實表時有兩個選擇：

◆建立一個巨大的表，或者
◆使用分區的方式

使用單個巨表這種方式與使用分區相比會導致較低的可用性，原因是在典型的關係型數據倉庫環境中批量導入操作是步進執行的。例如，步進式的批量導入會從對目標表的鎖定中獲得巨大的好處。當使用單個表時，這樣做就會阻止所有其它的用户在表導入的過程中訪問它。對於步進導入數據的最佳工作方式是使用一個規劃維護窗口。對於使用單個巨表這種方式中批量導入的全面討論請參閲在http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx 上的“SQL Server 2000步進批量導入案例學習”

分區方式讓數據被批量導入到單個的分段表中，每一段都代表了一個確定的分區範圍。分段表隨後被添加到分區視圖當中或者被當作一個新的分區交換到分區表中。由於每一個分區邏輯上都是由一個單獨的分段表來代表的，因此步進的批量導入不會對任何針對現有數據的查詢造成可用性和性能上的影響。

一個典型的數據倉庫解決方案應當包括在批量導入數據的同時進行數據轉換的功能。轉換包括對源數據的清除和/或者聚合以產生目標庫。

一個轉換典型情況下是通過使用象微軟系統集成服務這樣的工具來完成的。如果過程中不需要一個複雜的工作流，用户可以選擇使用SELECT/INTO來完成轉換。

索引

在把數據導入到一個關係型數據倉庫後，一般就要創建索引來為用户查詢提供支持。在對關係型數據倉庫體系結構造成影響的各個要素中創建和維護索引扮演了主要角色。

在沒有索引時對事實表的查詢性能通常比較差。對於使用單個巨大事實表的情況，一個最佳的解決方案是刪除所有的索引，導入數據，然後重建索引。這種方法導致可用性的降低並且有一個不斷增長的維護窗口，當表的大小增長到一定程度時這種方法可能就不太現實了。

在SQL Server 2000中當在基表上創建索引時，分區視圖有效的處理了這個問題。SQL Server 2005支持在單獨的分區上重建和重組索引，因而便於更好的管理分區索引。

數據老化

老化的數據被訪問的頻率比新的數據低一些。與日俱增的法律和規定需要業務保證老化的數據在線並能夠被立即訪問到。因而，在維護現有的數據的高可用性以及方便快速導入新的數據的同時有效的管理老化的數據對於一個企業是非常關鍵的。數據老化可以通過一個滑動窗口來有效的處理。如果數據被分區了，一個滑動窗口的實現就成為了可能。要查看更多的細節，請參閲本文後面的“滑動窗口實現”

數據存檔

對於一個幾T規模的數據倉庫的成功實現並不以構建一個良好性能以及線性擴展的系統而結束。它也依賴於對一個高度可用的系統的維護。

如果數據被分區了，在SQL Server中的零散備份就可以實現。在SQL Server中的零散備份以及還原操作為分區的管理提供了更大的靈活性。零散備份備份意味着單個的分區，在被限制到它們自己的文件組時，可以被單獨的備份和還原而不會影響整個數據庫。在一個簡單恢復模型中為了零散備份可以工作文件組必須設置為只讀模式。在使用大容量日誌恢復模型或者完全恢復模型的情況下，有必要備份事務日誌-這樣做主要是為了成功的還原文件組。關於這些限制的更多細節，請參閲在SQL Server 聯機叢書中的“備份(Transact-SQL)”

查詢性能

專用的查詢是關係型數據倉庫解決方案的一個主要部份。應用程序的特點以及查詢所產生的結果的性質對於關係型數據倉庫的分區有着極大的影響。例如，如果查詢中有一個對應到分區鍵的篩選鍵，與對單個巨表進行的同樣查詢相比有更快的響應速度。這是因為對分區的使用促進了並行操作的使用並且在查詢中的分區鍵意味着對數據的剪切更容易了。

滑動窗口實現

滑動窗口是影響對關係型數據倉庫分區的一個關鍵要素之一因而值得用上一個單獨的部份對其具體實現的細節進行討論。

滑動窗口的場景包括將新的分區滑動進去以及將老化的分區從分區表或者視圖滑動出來。當老化的數據存檔時新的數據就可以用來讓用户查詢了。在滑動分區時的關鍵是最小化down機時間。

老化的數據可以被存檔並且可以通過還原相應的備份在必要時取回，或者它也可以被移動到一個更低持久性，但更大I/O承受能力的始終可用以進行用户查詢的子系統。

下面的圖表展示了一個來自我們的測試場景的滑動窗口具體實現。在我們的測試場景中，從分佈在全國的商店那裏收集與消費者相關的銷售數據。數據被導入，純化，以及聚合來為商業決策提供支持。在我們的測試場景中，一個分區邏輯的代表了一週的有效數據。當前，八週的有效數據被標識為活動的。活動的數據比老化的數據的查詢頻率高很多。當有新的數據進來，老化的數據就會移出。這兒有一條業務規則表明老化的數據應當保持在線但是應當存儲在一個經濟有效的I/O子系統中。

 

圖表2：滑動窗口方案

在SQL Server 2000中，滑動窗口可以使用分區視圖來實現。缺點是分區視圖必須被重新綁定來包括進在UNION視圖中的新生成的數據。重新綁定需要一個元數據鎖並且可能會被任何對現存視圖或者基表的訪問所阻塞。

通過對使用Transact-SQL語句將分區交換進和交換出的支持SQL Server 2005提供了一個對滑動窗口方案的更佳實現。交換分區需要在分區表上放置一個架構鎖。當沒有其它的進程在分區表上獲取了一個表級別的鎖時分區可以被交換進和交換出。如果分區被其它的進程使用或者如果其它的進程已經在分區表上獲取了一個表級別的鎖，交換分區的構建進程將會等待直到其它進程已經釋放了鎖。分區交換是一個對元數據的操作因而非常快速。

下面的步驟可以用來在SQL Server 2005中使用分區表實現一個滑動窗口方案：

◆創建分區函數，架構以及帶有適當分界點和關聯文件組的表。然後按照下面描述的四個步驟來執行初始導入
◆創建代表單個分區的表
◆分別地填充表
◆向表中添加約束檢查來將數據值綁定到對應的範圍上並且創建適當的索引。SQL Server 2005在創建分區表後創建初始索引時提供了一個額外的選項
◆將新近填充好的表交換進分區表的每一個分區
◆在初始導入之後，在一個表中任何新導入和交換進來的數據都不會成為分區表的一部份。一旦數據已經就緒，在設置好適當的分界點後表就可以被交換進分區表
◆類似的，老化數據可以被移動到更經濟有效的I/O子系統但是仍然在線並保持可用狀態

接下來的部份涉及了一些針對分割分區表以及將分區交換進分區表的最佳實踐。

交換分區的最佳實踐

只有當目標表或者分區是空的時滑動窗口方案才工作。例如，如果一個分區“P”屬於分區表“PT”，它必須被交換出到表“T”，隨後目標表“T”必須被清空。類似的，當將表“T”交換進分區表“PT”的分區“P”，目標分區“P”應當是空的。

當跨分區的數據移動被最小化時，滑動窗口方案工作最佳。下面的代碼定義了分區函數和分區架構。當在這個分區架構上創建一個表時，分區表將會包括三個分區。第一個分區將會包含帶有<=200401的鍵值的數據；第二個帶有>200401 且 <=200403的鍵值；第三個帶有>200403的鍵值。


CREATE PARTITION FUNCTION SALES_MONTHLY_PARTITION_FUNCTION (INT)
AS RANGE LEFT FOR VALUES ( 200401, 200403 )
GO
CREATE PARTITION SCHEME SALES_MONTHLY_PARTITION_SCHEME AS PARTITION SALES_MONTHLY_PARTITION_FUNCTION ALL TO ([PRIMARY])
GO
CREATE TABLE t
(
col1   INT
)ON SALES_MONTHLY_PARTITION_SCHEME(col1)
GO

當使用ALTER PARTITION函數的分割功能添加一個值為200402的新分界時，行會在相應的分區間移動。


ALTER PARTITION FUNCTION PARTITION_FUNCTION()
SPLIT RANGE (200402)
GO

通過在原始的位置刪除行以及在新的位置添加行，行可以在分區間移動。和此次交換有關的分區在這一期間是不可訪問的。在這個例子中，新建的第二個分區將會擁有在鍵值範圍>200401 且 <=200402內的數據。帶有對應鍵值的數據被從第二個分區中刪除並且插入到新的分區。新的分區(>200401 且 <=200402)以及第三個分區(>200402 且 <=200403)在這一期間是無法訪問的。

在我們的消費者場景中，新的數據是通過在活動的結尾處分割分區函數來添加的。老的數據是通過在老化的結尾處合併分區函數來刪除的。這種實現滑動窗口的方式消除了在交換進和交換出分區時的跨分區的數據移動，也就是説，新的數據被批量的導入到一個表中並且隨後通過在活動的結尾處分割它來交換進分區表，具體如下所示：


ALTER TABLE NEW_PARTITION SWITCH TO PARTITIONED_TABLE PARTITION 
$partition.WEEK_PARTITION_FUNCTION (200404)
GO

對於更多的信息，請參閲在SQL Server聯機叢書中的“設計分區來管理數據子集”

將數據存儲到一個性價比高I/O子系統的技術

一個滑動窗口的具體實現可以通過將老化的數據滑動到一個性價比高的I/O子系統中來進行擴展。例如，在我們的測試場景中，我們將老化的數據從一個高性能的I/O子系統滑出到了一個沒有同等高的性能但較為經濟的I/O子系統中。特殊的滑動窗口實現可能無法通過在SQL Server中的備份與還原操作來完成。有一些替換的方式可以實現這樣一個策略：

◆如果源文件是可用的，將數據導入到位於性價比高的I/O子系統的其它表中。重建索引。刪除老的分區並且將新近導入的表添加到分區表中。無論你的數據集的大小，down機時間將會是交換分區所需要的時間，可以忽略不計。
◆如果導入的過程中包含了轉換，在性價比高的I/O子系統上，通過使用一個SELECT/INTO查詢從老化的分區提取數據來創建新表並重建索引應當更為有效。down機時間將會是交換分區所需要的時間。

關係型數據倉庫的分區策略

接下來的部份闡述了對你的關係型數據倉庫進行分區的兩個主要策略。該部份將會討論這些策略是如何作用於影響分區的要素的。

策略 I – 將一個分區綁定到它自己的文件組

一個分區可以使用以下的步驟被邏輯的綁定到一個文件組：

◆創建帶有多個文件組的數據庫，每一個文件組將會邏輯的對應一個分區
◆每個文件組中有一個文件。文件組可以包含一個或多個物理文件，並且這些文件可以在一個或多個邏輯卷/物理磁盤上創建
◆創建分區函數並將分界點映射到相應的文件組，使用分區架構來創建一個在文件組和分區之間的一一對應的關係

關於如何基於這個策略來實現分區的示例代碼請參閲附錄D

 

圖表3：將分區映射到其自身的文件組

策略Strategy II – 將兩個或更多分區綁定到同樣的文件組

第二個策略是將一個或者多個分區映射到同樣的文件組。文件組可以由一個（或者多個）分佈在跨越一個（或者多個）邏輯卷/物理磁盤的物理文件來組成。對於基於這個策略實現分區的示例代碼，請參閲附錄D

 

圖表4：將兩個或更多的分區映射到同一個文件組

哪個策略更好?

分區可以通過使用這兩個策略之一或者將兩個策略有效的結合到一起來實現。關於策略 I 和 策略 II可以參考下面的表格，其中解釋了這兩個策略對影響關係型數據倉庫分區因素的作用。

策略 I

策略 II

數據導入

·         在SELECT/INTO SQL語句中不能明確的指定文件組。由於這一限制，在使用一個SELECT/INTO 語句時無法採用並行方式來完成初始轉換

·         步進的數據導入不會被分區策略所影響

·         當所有的分區映射到同一個文件組時轉換可以採用並行的方式完成

·         步進的數據導入不會被分區策略所影響

備份/還原

·         在文件組和分區之間創建一個一一對應的關係讓在分區級別的零散備份和還原操作得以進行

·         在備份數據庫之前確保分區被標記了只讀狀態。如果沒有，當還原數據庫時事務日誌必須被前滾

·         如果所有的分區被映射到了同一個文件組，分區表作為一個整體可以使用單個的命令進行備份和還原

·         此策略在單個分區的粒度下不提供零散備份的靈活性

 

查詢性能

·         如果文件組只包含一個文件，並且表會採用一個接一個的串行方式來進行填充，對這樣的對象的擴展盤區的分配是連續的。這意味着SQL Server對於一個連續的掃描可以提供多達256 KB的 I/O (4個擴展盤區)速率

·         因為數據是連續的，因此對於大量的連續掃描可以提供更好的工作負荷

·         如果文件組是由多個文件組成的，SQL Server使用按比例填充機制，這樣會導致擴展盤區出現碎片

·         類似的，在並行操作比如並行數據導入操作期間為對象/分區分配的擴展盤區無法保證是連續的

·         當為對象分配的擴展盤區不連續時，對於連續掃描SQL Server可以提供差不多64 KB的 I/O（1個擴展盤區）速率

·         因為數據分佈在許多的物理磁盤上，因此它有助於在進行大量的併發隨機I/O操作時提供更好的工作負荷性能

作為一個替換的方案，可以在啓動SQL Server時加上-E開關。當在啓動時指定了-E開關時，SQL Server可以分配4個而不是1個擴展盤區。因而-E開關讓SQL Server可以提供多達256 KB 的I/O速率，即使存在因使用了按比例填充機制而產生的擴展盤區碎片

結論

本白皮書討論了影響分區的因素，以及對於設計分區可使用的兩個主要策略的正反兩面的對比。這裏所提供的信息可能會對通過分區更有效的管理你的關係型數據倉庫有所幫助。

有關的更多信息，請訪問:http://www.microsoft.com/sql/

本文檔展示了SQL Server 2005的一些與關係型數據倉庫分區的相關特殊的功能。需要更多的信息，請參閲：

◆SQL Server 2005聯機叢書提供了一些關於這個主題的有價值的信息，並且它可以作為使用SQL Server 2005實現數據分區的一個不錯的起點。
◆CLARiiON CX600 Web站點: http://www.emc.com/products/systems/clariion_cx246.jsp

附錄 A: 性能數值

所有在這一部份展示的數值都是在我們使用SQL Server 2005的測試過程中得到的。該測試是在附錄B，C以及D中記錄的硬件平台上進行的。

批量插入性能

在我們的測試中，我們可以在SQL Server 2005中使用剛剛超過一個小時的時間導入25億行。事實表的架構中包含了九個整型列，一個日期類型列，以及一個字符型列。

在這裏展示的性能數值是在所有的線程以並行的方式執行並且分區架構基於策略I的環境下捕捉的。

# 並行線程

執行時間

(在所有的線程執行時)

處理器使用率 (跨8個處理器)

# 行插入

批量拷貝吞吐量/秒

批量拷貝行/秒

磁盤吞吐量

8

52 分鐘

88 %

2550835652

44.77 MB/秒

6,53,339

41.17 MB/秒 跨 8 個LUNs on CLARiiON

在這裏展示的性能數值是在所有的線程以並行的方式執行並且分區架構基於策略II的環境下捕捉的。

#並行線程

執行時間

(在所有的線程執行時)

處理器使用率 (跨8個處理器)

#行插入

批量拷貝吞吐量/秒

批量拷貝行/秒

磁盤吞吐量

8

52分鐘

92.625%

2,550,835,652

46.42 MB/秒

677285

44.29 MB/秒 跨 8 個LUNs on CLARiiON

為數據倉庫分區所選擇的策略並不影響批量導入的吞吐量。

轉換性能

在我們的測試中，批量導入的過程後面跟着一個轉換過程。轉換過程包括將源數據和維度表聯接在一起，其目的是為了使用提取的維度鍵值來填充目標倉庫。下面是一段在我們的測試場景中使用的示例代碼：


SELECT fact.fact_data_1, 
…
sdim.store_key, pdim.product_key, tdim.time_key
INTO sales_transformed
FROM 
sales fact, 
stores sdim, 
products pdim, 
time tdim
WHERE fact.store_id = sdim.store_id
AND convert(bigint, (fact.system + fact.generation + fact.vendor + fact.product)) = pdim.productid 
AND fact.weekid = tdim.weekid

轉換查詢是連續運行的，一個查詢後面緊接着另一個，以致於在策略I中要保存前面的分區架構設置

# 並行線程

執行時間

處理器使用率

(跨1個處理器)

# 被轉換的行

(每個轉換)

磁盤讀吞吐量

磁盤寫吞吐量

1

每個轉換差不多1小時13分鐘

100%

差不多3億行

 

3.5 MB/秒 跨 1 LUN on CLARiiON

(256 KB 讀)

2.8 MB/秒 跨1 LUN on CLARiiON (128 KB 寫)

以下是來自一個分區架構的性能數值，它是基於策略II的並且所有的線程都是以並行方式執行的。

索引構建性能

在我們的測試中，我們可以用2個小時在跨越擁有25億行數據的八個分區的三個整型列（維度鍵）上創建一個聚簇索引。其中的SORT_IN_TEMPDB選項允許tempdb數據庫被用來對數據進行排序。在這個測試過程中使用這個選項來隔離在索引建立過程中用來讀寫的物理磁盤。當'sort_in_tempdb'選項設置為on時，tempdb數據庫必須有足夠的剩餘空間在離線的索引創建過程中用來放置整個的索引。當排序在一個用户數據庫中完成時，每個文件組/分區需要有足夠的剩餘空間來放置相應的分區。

在我們的消費者場景和設計中，每個分區邏輯的代表了一週的有效數據，這些數據帶有一個和在任何給定的分區中所有的行相同的鍵值。對於每個單獨的索引建立的parallelism的級數只能是1，因為distinct關鍵字值的數量是1。因而，在SQL Server 2005中我們在創建了分區表之後再創建起始索引的做法讓起始索引的建立更好的利用了可用的處理器資源。

# 並行線程

執行時間

處理器使用率

#行

8

2小時

86%

2,478,765,081

數據庫備份性能

在我們的測試中，我們花了一個小時多一點的時間來備份活動分區到4個RAID 3 LUNs on EMC CLARiiON存儲系統。活動分區分佈在跨越8個LUN's的一個RAID 5 (4+1 10K RPM) EMC CLARiiON陣列上。目標設備是一個RAID 3 (4+1, 5K RPM ATA) CLARiiON陣列。

執行時間

# 備份的頁 (8K)

備份/還原吞吐量/秒

1小時20分鐘

30,518,330

50.15 MB/秒

老化數據到ATA 磁盤

老化數據到ATA磁盤包括使用SELECT/INTO語句批量導入數據。以下是一段用來實現滑動窗口的代碼示例。


ALTER DATABASE [Customer]  
ADD FILEGROUP [ARCHIVE_1]
GO
ALTER DATABASE [Customer]  
ADD FILE (NAME = N'ARCHIVE_1', FILENAME = N'F:/ATA/RAID5/2/ARCHIVE_PARTITION_1.NDF', SIZE = 100GB, FILEGROWTH = 0)
TO FILEGROUP [ARCHIVE_1]
GO
ALTER DATABASE Customer MODIFY FILEGROUP [ARCHIVE_1] DEFAULT
GO
SELECT * INTO iri..Sales_129_ARCHIVE
FROM VISF 
WHERE TIME_KEY = 129
OPTION (MAXDOP 1)

一個等價於在現存的分區表上的聚簇索引的聚簇索引必須在新的表上創建，以便能夠隨後交換進分區表。在我們的測試中，我們使用了SELECT/INTO來滑出第一個有2.79億行的老化分區到一個ATA磁盤陣列，整個過程用了不到20分鐘。在同樣的表上創建聚簇索引用了不到40分鐘。

附錄 B: 平台列表

以下的硬件和軟件部件是用來進行本文中所描述的測試的：

Microsoft 軟件Microsoft Windows Server™ 2003 數據中心版 Build 3790
Microsoft SQL Server 2005 Beta 2

服務器平台64-位 Unisys ES7000 Orion 130
16 Itanium 2 1.30 GHz CPUs 帶有 3 MB 緩存
64 GB 內存

存儲EMC Symmetrix 5.5
- 96, 72 GB 10 KB RPM 磁盤 16 GB 讀寫緩存
EMC CLARiiON FC4700
- 155, 133.680 GB, 10 KB RPM磁盤16 GB讀寫緩存
- 30, 344 GB, 5 KB RPM磁盤(ATA)

主機總線適配卡
8 Emulex LP9002L 2 GB/秒光纖通道主機總線適配卡
所有的適配卡映射到CLARiiON 存儲陣列，可以通過Powerpath 軟件實現負載均衡

存儲管理軟件
EMC Powerpath v 3.0.5

 

附錄 C: 服務器體系結構

我們使用了一台Unisys ES7000 Orion 130服務器來進行我們的測試。瞭解你的服務器的體系結構對於確定你的系統的理論上的吞吐量是要素之一。例如，一個100 Mhz PCI總線能夠提供800 兆位/秒 的吞吐量。本附錄簡要的描述了ES7000 Orion的體系結構。

企業服務器ES7000 Orion 130是一個模塊化機架固定式系統，它基於一個Intel芯片組以及Intel Itanium 2處理器家族。

在測試中使用的Orion服務器配置了2個服務器模塊，16個處理器，以及64 GB內存。每個服務器模塊帶有一個IOHM（輸入/輸出集線器模塊）。每個IOHM控制了一個由4個PCIAM's (PCI 適配器模塊)所組成的I/O子系統。每個PCIAM有兩個總線，每個提供2個PCI卡插槽。在每個總線上支持最高到100 Mhz的PCI卡。

那些高亮的槽是當前配置的。在總線BUS 0-0上的槽2以及在PCIAM 0-0/IOHM-0上的總線BUS 0-1是為本地的SCSI磁盤以及網卡配置的，一共給我們留了16個槽。其中的8個使用Powerpath軟件映射到了CLARiiON存儲陣列。

圖表5：本場景中使用的CASSIN 服務器的I/O配置

附錄 D: EMC CLARiiON 存儲

我們使用了一個CLARiiON CX600存儲陣列用來測試。本附錄概況的描述了使用的存儲陣列的配置信息。

CLARiiON存儲陣列是由一系列的托盤和機架組成的。每個托盤可以放置多達15個磁盤。放在托盤中的大量磁盤被組織到一起來組成一個RAID組。從RAID組中生成的邏輯單元號碼（LUN）可以在Microsoft Windows®操作系統中查看到。如果在將來需要添加更多的存儲設備，磁盤可以被安裝到存儲托盤並且組成RAID組，新創建的RAID組可以和任何現存的RAID組聯合在一起，形成一個可以在Windows操作系統中顯現的Meta -LUN。Meta -LUN將會保持源LUN的LUN號。這些磁盤組成RAID組的形式不會影響性能，原因是CLARiiON存儲陣列可以實現跨托盤和物理磁盤的負載均衡。

在我們的測試中使用的EMC CLARiiON存儲陣列有着一個混合了RAID 1+0, RAID 5, 和 RAID 3的配置。RAID 1+0為數據庫日誌文件所用而RAID 5為數據庫文件所用。RAID 3是用來備份和還原數據庫分區的。10KB RPM 物理磁盤是 133 GB 的而 5KB RPM 磁盤是344 GB

我們的存儲配置包括5個RAID 1 + 0 LUNs。LUNs是一個由8個物理磁盤組成的RAID組所生成的。由於磁盤使用了條帶和鏡像技術，因此每個LUNs都有大約500 GB ((8*133.680 GB)/2)的存儲空間。RAID 1+0 LUNs中的兩個被用來放置數據庫的日誌文件。

12個RAID 5 LUNs用來存儲數據文件。5個RAID 5 LUNs是給tempdb數據庫用的。這些LUNs中的每一個都有大約500 GB的存儲空間。每個LUN源自一個RAID組。RAID 5的RAID組配置的相對於RAID 1 + 0的RAID組所包含的磁盤要少一些。RAID 5的RAID組是由5個133 GB磁盤組成的。

下面描繪的是在一個CLARiiON存儲陣列中的RAID組和磁盤安排。來自於托盤2的3個物理磁盤和來自托盤1的兩個物理磁盤組成了一個RAID組。下一個RAID組是通過交替來自每個托盤的磁盤的號來實現的。物理磁盤在托盤間交叉使用，作為一個最佳實踐，是為了在托盤級別出現了任何的失敗後系統仍可維持。

 

圖表6：CLARiiON RAID 3/5 以及RAID組配置

拓樸

在我們的測試中，使用了光纖通道技術並實現了一個標準的光纖交換式拓樸結構。光纖通道是一個柔性的區域，在交換機級別使用了全世界通用的名稱光纖主機總線適配器(HBAs)。在這個測試場景中LUNs都被蓋了起來，只讓主機服務器可以看到存儲控制器。

圖表7描述了在HBA和存儲設備端口以及LUNs間的映射。由於在我們的測試場景中，邏輯卷直接映射到了單個的LUN，因此圖表也可被看作是到邏輯卷的映射EMC's Powerpath（多路徑軟件）是用來在所有基於CLARiiON存儲陣列的LUN間實現I/O負載均衡的。8個Emulex HBAs分成了區域來查看所有在CLARiiON存儲陣列上的LUNS。有關安裝Emulex HBAs with EMC storage的全部的詳細信息，請訪問： http://www.emulex.com/ts/docoem/framemc.htm!href(http://www.emulex.com/ts/docoem/framemc.htm.

 

圖表7：HBA-存儲端口-卷映射

附錄 E: 存儲隔離

在存儲域網絡環境中存儲隔離扮演了一個重要的角色，特別是當數據在一個或者幾個應用程序之間共享時。本附錄着重談到了在對你的關係型數據倉庫進行分區時對存儲隔離的一些需要考慮的事項。在這個附錄中的例子沿用了在我們的測試一開始就使用的EMC Symmetrix存儲陣列可以創建來自於多個物理磁盤的邏輯卷。類似的，多個邏輯卷也可以創建在同一個物理磁盤上。並且在某些情況下，多個邏輯卷可以在一組物理磁盤上創建。這種用法在存儲域網絡中是司空見慣的。在EMC Symmetrix中，物理磁盤被邏輯的分成了hyper卷。來自於多個物理磁盤的hyper卷可以成組創建一個Meta卷。Meta卷可以成組創建一個邏輯卷。在下面的圖表中，數字1到32代表了單個的物理磁盤。每個物理磁盤被分成了8 個9-GB 的hyper卷。Hyper卷1.1 到8.1成組創建了一個Meta卷。Meta卷，在這張圖中，是編號0001a, 0011a 到0071b的。Meta 卷 0001a 和0001b組成在一起創建了一個邏輯卷。在粗的豎線的右邊的物理磁盤代表了鏡像對。

 


圖表8：Symmetrix 存儲陣列磁盤配置邏輯圖

例如，我們可以創建兩個邏輯卷；一個，叫L1，是通過成組Meta 卷 0001a, 0001b來創建的。如果這些邏輯卷遇到了大量的併發I/O請求，磁盤系統顛簸將會發生，原因是hyper卷是從同樣的一組底層物理磁盤那裏創建的。為了避免在這種情況下發生的磁盤系統顛簸，需要格外小心的將可能會被併發訪問的分區創建在隔離的邏輯卷和物理磁盤上。

在為任何數據倉庫設計存儲方案時，都存在着一個在物理級別的隔離數據和採用象將硬件資源滿負荷使用的設計方案之間的平衡點活動分區以及存檔分區的數據可以放置在同一個物理存儲設備上，不過為了監視和管理的目的最好分別存儲在單獨的邏輯捲上——一個活動分區可以在邏輯卷L1上創建，而一個存檔分區可以在邏輯卷L2上創建。儘管這兩個分區在邏輯上是隔離的，但它們共享了同樣的一套物理存儲設備。

一個對應的替換的方法是將存檔的數據和活動的數據在物理存儲的級別就隔離開。這樣就可以減少用於活動數據分區的存儲設備的數量，不過由於數據的訪問頻率較低潛在的可能造成對為存檔分區提供的存儲設備的未充分利用。

現代的存儲域網絡設備裝備了大量的緩存存儲以及共享的不可預知的使用方式，最好是在實踐時將數據分散到許多的存儲設備。這樣就最大化了資源的利用率以及依靠硬件提供的合格的性能。

配置你的存儲

經過和EMC工程師們的共同工作後，基於我們的場景需要的存儲陣列的配置得到了優化。在為一個SQL Server的具體實現配置存儲時我們鼓勵我們的用户同他們的硬件供應商緊密協作。

附錄 F: 腳本

以下的腳本是在本白皮書前面被稱為“關係型數據倉庫分區策略”的部份中所描述的策略II實現過程中用來創建數據庫，分區函數，以及分區架構的。


CREATE DATABASE [SALES]
ON PRIMARY
(
NAME = N'SALES_PRIMARY', FILENAME = N'D:/SALESPrimary/SALES_PRIMARY.MDF', SIZE = 100MB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition1
(NAME = N'SALES_ACTIVE_PARTITION_1', FILENAME = N'F:/RAID5/DATA/1/SALES_ACTIVE_PARTITION_1.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition2
(NAME = N'SALES_ACTIVE_PARTITION_2', FILENAME = N'F:/RAID5/DATA/2/SALES_ACTIVE_PARTITION_2.NDF', SIZE = 100GB, FILEGROWTH = 0), 
FILEGROUP SALES_ActivePartition3
(NAME = N'SALES_ACTIVE_PARTITION_3', FILENAME = N'F:/RAID5/DATA/3/SALES_ACTIVE_PARTITION_3.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition4
(NAME = N'SALES_ACTIVE_PARTITION_4', FILENAME = N'F:/RAID5/DATA/4/SALES_ACTIVE_PARTITION_4.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition5
(NAME = N'SALES_ACTIVE_PARTITION_5', FILENAME = N'F:/RAID5/DATA/5/SALES_ACTIVE_PARTITION_5.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition6
(NAME = N'SALES_ACTIVE_PARTITION_6', FILENAME = N'F:/RAID5/DATA/6/SALES_ACTIVE_PARTITION_6.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition7
(NAME = N'SALES_ACTIVE_PARTITION_7', FILENAME = N'F:/RAID5/DATA/7/SALES_ACTIVE_PARTITION_7.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition8
(NAME = N'SALES_ACTIVE_PARTITION_8', FILENAME = N'F:/RAID5/DATA/8/SALES_ACTIVE_PARTITION_8.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition9
(NAME = N'SALES_ACTIVE_PARTITION_9', FILENAME = N'F:/RAID5/DATA/9/SALES_ACTIVE_PARTITION_9.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition10
(NAME = N'SALES_ACTIVE_PARTITION_10', FILENAME = N'F:/RAID5/DATA/10/SALES_ACTIVE_PARTITION_10.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_DIMENSIONS
(NAME = N'SALES_DIMENSIONS_1', FILENAME = N'F:/RAID5/DATA/11/SALES_DIMENSIONS_1.NDF', SIZE = 450GB, FILEGROWTH = 0),
(NAME = N'SALES_DIMENSIONS_2', FILENAME = N'F:/RAID5/DATA/12/SALES_DIMENSIONS_2.NDF', SIZE = 450GB, FILEGROWTH = 0)
LOG ON  
(NAME = N'SALES_LOG', FILENAME = N'F:/SQL/Path8/SALES_LOG.LDF', SIZE = 120GB, FILEGROWTH = 0)
GO
CREATE PARTITION FUNCTION SALES_WEEK_PARTITION_FUNCTION (INT)
AS RANGE RIGHT FOR VALUES ( 129, 130, 131, 132, 133, 134, 135, 136 )
GO
CREATE PARTITION SCHEME SALES_WEEK_PARTITION_SCHEME AS PARTITION SALES_WEEK_PARTITION_FUNCTION 
TO 
(
SALES_ActivePartition1, SALES_ActivePartition2, SALES_ActivePartition3, SALES_ActivePartition4, SALES_ActivePartition5, 
SALES_ActivePartition6, SALES_ActivePartition7, SALES_ActivePartition8, SALES_ActivePartition9, SALES_ActivePartition10
)
GO

以下的腳本是在本白皮書前面部份描述的策略II實現過程中用來創建數據庫，分區函數，以及分區架構的。


CREATE DATABASE [SALES]
ON PRIMARY
(NAME = N'SALES_PRIMARY', FILENAME = N'D:/SALESPrimary/SALES_PRIMARY.MDF', SIZE = 100MB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition
(NAME = N'SALES_ACTIVE_PARTITION_1', FILENAME = N'F:/SQL/Path2/1/SALES_ACTIVE_PARTITION_1.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_2', FILENAME = N'F:/SQL/Path2/2/SALES_ACTIVE_PARTITION_2.NDF', SIZE = 100GB, FILEGROWTH = 0), 
(NAME = N'SALES_ACTIVE_PARTITION_3', FILENAME = N'F:/SQL/Path3/1/SALES_ACTIVE_PARTITION_3.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_4', FILENAME = N'F:/SQL/Path3/2/SALES_ACTIVE_PARTITION_4.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_5', FILENAME = N'F:/SQL/Path4/1/SALES_ACTIVE_PARTITION_5.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_6', FILENAME = N'F:/SQL/Path4/2/SALES_ACTIVE_PARTITION_6.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_7', FILENAME = N'F:/SQL/Path5/1/SALES_ACTIVE_PARTITION_7.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_8', FILENAME = N'F:/SQL/Path6/1/SALES_ACTIVE_PARTITION_8.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_DIMENSIONS
(NAME = N'SALES_DIMENSIONS_1', FILENAME = N'F:/RAID5/DATA/11/SALES_DIMENSIONS_1.NDF', SIZE = 450GB, FILEGROWTH = 0),
(NAME = N'SALES_DIMENSIONS_2', FILENAME = N'F:/RAID5/DATA/12/SALES_DIMENSIONS_2.NDF', SIZE = 450GB, FILEGROWTH = 0),
LOG ON  
(NAME = N'SALES_LOG', FILENAME = N'F:/SQL/Path8/SALES_LOG.LDF', SIZE = 120GB, FILEGROWTH = 0)
GO
CREATE PARTITION FUNCTION SALES_WEEK_PARTITION_FUNCTION (INT)
AS RANGE RIGHT FOR VALUES ( 129, 130, 131, 132, 133, 134, 135, 136 )
GO
CREATE PARTITION SCHEME SALES_WEEK_PARTITION_SCHEME AS PARTITION SALES_WEEK_PARTITION_FUNCTION 
ALL TO (SALES_ActivePartition)
GO