Subject Matter Experts:
T.K. Anand
Sasha (Alexander) Berger
Marius Dumitru
Eric Jacobsen
Edward Melomed
Akshai Mirchandani
Mosha Pasumansky
Cristian Petculescu
Carl Rabeler
Wayne Robertson
Richard Tkachuk
Dave Wickert
Len Wyatt
Published: February 2007
Applies To: SQL Server 2005, Service Pack 2
概要：此白皮書詳述應用程式開發人員如何應用效能調校手段來優化Microsoft SQL Server 2005聯機分析處理(OLAP)方案。
發表於：部落格園
版權
文件中的資訊僅代表發表之日止微軟公司的觀點。因為微軟公司需要響應不斷變化的市場環境，所以微軟公司不能保證發表之日後，文件中資訊的正確性。
此白皮書僅用於報告目的。微軟不為此文件作任何擔保。
使用者有責任遵從版權法。如果沒有微軟公司的授權，使用者不得超出許可權以任何形式（包括電子或機械複製、拍照、記錄、及其他形式）複製此文件。
此文件可能包含了微軟的專利、專利應用、商標、版權或其他非物質財富。除了微軟提供專門的書面授權，否則你不能使用文件中的這些專利、商標……
除非特別說明，此文件的例子中出現的公司、企業、產品、域名、電子郵箱地址、圖示、人名、地點和事件都是虛構的，如有雷同，純屬巧合。
2007 Microsoft Corporation. All rights reserved.
Microsoft, Windows, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
All other trademarks are property of their respective owners.
介紹
聯機分析處理系統都需要快速的查詢響應和及時的資料更新，以提供高效的資料分析。傳統的OLAP系統使用層次組織和彙總資料，層次為有效地分析提供合理的資料結構。但嚴格的層次結構又限制了使用者自由的組織和分析資料。
為了提供更自由和更彈性的資料分析，Microsoft® SQL Server™ Analysis Services (SSAS) 2005既包含了傳統層次分析的優點，又有新一代的更彈性的屬性層次。屬性層次允許使用者在查詢時自由的組織資料，而不限於設計好的導航路徑。要支援這樣的彈性化分析，Analysis Services OLAP構架經過了特殊的設計，以應用屬性和層次分析，同時還保持傳統OLAP資料庫的快速查詢效能。
你需要理解OLAP構架是如何支援屬性和層次構架，理解如何有效使用這種構架滿足分析需求，以及如何讓構架充分利用系統資源。
注意 要使用此白皮書中討論的效能調校技術，你必須安裝了SQL Server 2005 Service Pack 2。
為了滿足各種OLAP設計方案的效能需要，此文件提供了廣泛的指導，指導你使用更多手段優化Analysis Services效能。因為Analysis Services效能調校是非常寬泛的話題，此白皮書的內容按如下的四個章節組織。
增強查詢效能 – 查詢效能直接影響終端使用者的體驗，也是衡量OLAP是否成功的主要標準。Analysis Services提供了多種機制加速查詢效能，包括聚合、快取、資料索引，而且你可以通過優化維度屬性、cube、MDX查詢語句來提升效能。
調校處理效能 – 處理是更新Analysis Services資料庫的操作。處理速度越快，使用者能更及時地獲取更新後的資料。Analysis Services提供了多種機制讓你影響處理效能，包括有效的維度設計、合適的聚合、分割槽、節制的處理策略（例如用增量更新代替完全更新，主動快取技術等）。
為特殊應用優化設計 – 複雜的設計場景要求特殊的效能調校技術，以確保OLAP能夠成功的應用，特別是複雜的設計再加上大資料量。例如，在OLAP中包含了特殊聚合函式、父子層次、複雜的維度關係、近實時地更新資料。
調校伺服器資源 – Analysis Services的操作受伺服器資源的限制，理解Analysis Services如何使用記憶體、CPU和磁碟資源可以幫助你更有效的管理伺服器，優化查詢和處理效能。
文件的三個附錄提供了更多相關資訊。
增強查詢效能
查詢是指Analysis Services依據多維表示式（MDX）將資料提供給客戶端應用程式。因為查詢的效能直接影響到客戶的體驗，這部分將詳述改進查詢效能的幾種手段。下面就是這部分的主要內容：
理解查詢構架 - Analysis Services查詢構架支援三種主要操作：會話管理、MDX查詢執行和返回資料。若要優化查詢效能就需理解這三種操作如何協調工作實現查詢。
優化維度設計 - 經過良好調校的維度設計可能會是提高Analysis Services效能最重要的因素。建立屬性關係和在層次中使用屬性會影響到聚合設計、MDX計算、維度資料儲存效率和磁碟讀取資料的效能。
最大化聚合資料 - 通過預彙總計算，聚合資料能夠提高查詢效能。為了最大化聚合資料，確保你有一種合適聚合設計以滿足你特定的需求。
使用分割槽增強查詢效能 - 分割槽是一種將度量值組儲存到獨立物理單元的機制，這種機制能提高查詢、處理效能，使管理更簡便。而且分割槽能實現併發查詢，可以通過聚合設計選項和服務端屬性設定優化分割槽效能。
編寫高效的MDX語句 – 這部分詳述如何編寫高效的MDX語句，例如：1）在MDX語句中使用更精確和更窄的計算空間。2）設計多使用者可重用的計算成員。3）用最簡潔的方式編寫計算成員表示式，使查詢執行引擎能最有效的選擇執行路徑。
理解查詢構架
為了使終端使用者能有儘可能快速的查詢體驗，Analysis Services使用了若干元件協同工作實現高效計算和返回資料。圖1標明瞭在查詢發生時三個主要操作：會話管理、MDX查詢執行、返回資料，以及參與每部分操作的服務元件。
圖 1 Analysis Services查詢構架
會話管理
客戶端應用程式通過TCP IP或HTTP，使用XML for Analysis (XMLA)與Analysis Services通訊。Analysis Services使用XMLA監聽元件管理所有的XMLA通訊。會話管理決定客戶端連線到Analysis Services例項的方式。通過Windows認證，且擁有相關許可權的使用者才能連線到Analysis Services。在使用者連線到Analysis Services後，安全管理依據使用者在Analysis Services中的角色決定使用者的許可權。基於客戶端應用程式構架和連線安全許可權，當客戶端應用程式連線到Analysis Services時，為客戶應用程式建立一個會話，使用者的所有查詢請求都會重用這個會話，直到客戶端應用程式關閉會話或在服務端終止會話。在查詢執行引擎執行使用者的請求時，會話提供了上下文。關於會話的生命週期，參考文件中“監控超時的空閒會話”章節。
MDX查詢執行
查詢執行引擎的主要工作是執行MDX查詢，這部分概述查詢執行引擎如何執行查詢。關於優化MDX的細節，參考文件中“編寫高效的MDX語句”章節。
實際的查詢執行過程分多步執行，從效能方面考慮，查詢執行引擎必須考慮兩種基本需求：找到資料和產生結果集。
1. 找到資料—為了找到查詢請求的資料，查詢執行引擎將MDX查詢分解成多個資料請求。又在與儲存引擎通訊時，將這些資料請求翻譯成儲存引擎能夠理解的子立方體請求，子立方體的數量依賴於查詢的粒度和複雜度。子立方體代表了查詢、快取、查詢資料的邏輯單元。注意，這裡的子立方體是泛指，不要與MDX語句CREATE SUBCUBE所指的子立方體搞混淆。
2. 產生結果集—為了處理從儲存引擎查詢到的資料，查詢執行引擎使用兩種執行計劃來計算結果：可以批量計算整個子立方，或者計算單獨的單元。通常，子立方體賦值路徑更高效，但查詢執行引擎依靠查詢的複雜程度選擇合適的執行計劃。注意，同一查詢的會分解成多個查詢部分，因而產生多個子執行計劃，而且這些子執行計劃可以獨立的選擇兩種執行計劃中的一種，所以沒有單一的全域性計劃方式。例如，要查詢年際利潤大於10%的分銷商，查詢執行引擎使用一個執行計劃來計算每個分銷商的年際利潤，另一個執行計劃用來篩選利潤大於10%的分銷商。
當你執行一個MDX計算，查詢執行引擎需要計算的單元數量可能遠超出你的想象。比如，你使用MDX查詢年初至今銷售最大的五個區域。看起來你只需要五個單元值，但要決定哪五個區域銷售最大，及計算年初至今銷售值，Analysis Services必須計算更多單元。一個常用的優化手段是在MDX語句中將查詢執行引擎所需計算的資料量最小化。要了解更多MDX優化手段，參考此文件中“指明計算空間”章節。
當查詢執行引擎計算單元，它會使用查詢執行引擎快取和儲存引擎快取儲存計算結果。快取的好處就是優化計算和支援計算結果重用。為了優化快取重用，查詢執行引擎可管理三種範圍的快取：全域性範圍、會話範圍、查詢範圍。關於快取共享和重用的資訊，參考“利用查詢執行引擎快取”章節。
資料查詢：維度
在資料查詢期間，儲存引擎必須選擇最佳的查詢機制以滿足維度和度量資料請求。
為了滿足維度資料請求，儲存引擎從屬性和層次儲存中抽取資料。當查詢所需的資料時，儲存引擎使用動態即需快取，僅將所需的成員存入記憶體，而不是將所有維度成員靜態儲存在記憶體中。維度資料結構可以寄存於磁碟上、Analysis Services的記憶體中、或者Windows作業系統的檔案快取中，這由系統記憶體裝載方式決定。
顧名思義，維度屬性儲存包含維度屬性的所有資訊。維度屬性儲存的各部分如圖 2所示。
圖 2 維度屬性儲存
如圖中那樣，維度屬性儲存中每個維度屬性包含下面部分：
· 鍵儲存—鍵儲存包含屬性鍵成員值和一個稱為DataID的內部唯一標識。Analysis Services為每一個屬性成員分配DataID。
· 引數儲存—引數儲存用來記錄屬性的各種附帶引數，包括成員名稱和翻譯。DataID是從零開始連續分配的，引數儲存對應到DataID。為了能不使用額外的索引或雜湊表實現快速的隨機訪問，引數儲存和關係儲存一樣按照DataID的順序物理排列。
※注：為了不跟維度中的Attribute搞混，Property我只好翻譯成“引數”。
· 雜湊表—為了在查詢和處理過程中，便捷的找到屬性，會為每一個屬性在磁碟上儲存兩個雜湊表。鍵雜湊表通過唯一鍵索引成員；名稱雜湊表通過名稱索引成員。
· 關係儲存—關係儲存包含了屬性與其他屬性的關係。確切地說，關係儲存儲存了帶有DataID的原記錄與其他屬性的對應關係。考慮下面的產品維的例子：在一個維度中產品是鍵屬性，它與顏色屬性和尺寸屬性有直接關係。“運動頭盔”是產品屬性的成員之一，它的DataID是1001；“黑色”是顏色屬性的成員之一，它的DataID是25；“大號”是尺寸屬性的成員之一，它的DataID是5。這些屬性成員組成了一個維度例項集——“運動頭盔，黑色，大號尺寸”，這在關係儲存中儲存為1001, 25, 5。注意如果一個屬性與其他屬性沒有屬性關係，則不會為特殊的屬性建立關係儲存。關於屬性關係更多資訊，參考“識別屬性關係”章節。
· 點陣圖索引—為了在查詢時快速在關係儲存中找到屬性資料，儲存引擎在處理時建立點陣圖索引。對屬性的每一個DataID（成員），點陣圖索引描述資料頁中是否包含帶有此DataID的紀錄。如果屬性有非常多的DataID，既此屬性有很多成員，那麼在處理時將花費大量時間。在大部分場合，點陣圖索引能顯著的改進查詢效能。但在設計時，第一次建立點陣圖索引的代價超出了查詢時的益處。可以通過將AttributeHierarchyOptimizedState設定為Not Optimized，刪除指定屬性的點陣圖索引。關於這種設計方案，參考“減少屬性負擔”章節。
除了屬性儲存，層次儲存為終端使用者將屬性排列成導航路徑，如圖 3所示。
圖 3 層次儲存
層次儲存由下面幾個主要部分組成：
· 集合儲存—集合儲存將DataID從第一級排列到當前級別，來為每一個成員構建路徑。例如，“山地自行車500系列”的DataID是6，它的父成員是“山地自行車”；“山地自行車”的DataID是5，它的父成員是“自行車”；“自行車”的DataID是2，它的父成員是“所有產品”；“所有產品”的DataID是1。那麼“山地自行車500系列”的結構儲存就是1，2，5，6。
· 結構儲存—對級別中每個成員，結構儲存包含父成員的DataID，第一個子成員的DataID和子成員的數量。結構儲存中的每個成員都按照其級別索引排序，級別索引是由維度排序設定決定的成員在級別中的位置。為了更好的理解結構儲存，考慮下面的例子：如果成員自行車有3個子成員，那麼它的結構儲存為1，5，3；1是自行車父成員的DataID，5是自行車第一個子成員的DataID，3是子成員的數量。
注意，只有自然層次才在層次結構中物化儲存。關於設計層次最佳實踐，參考“有效使用層次”章節。
資料查詢：度量值組
對資料請求，儲存引擎在分割槽中查詢度量資料。分割槽包含兩類度量資料：事實和聚合資料。為了適應不同的資料儲存結構，每個分割槽可使用不同的儲存模式。MOLAP儲存模式提供了最快的查詢效能，MOLAP分割槽會以壓縮的多維格式儲存事實和聚合資料。大部分情況下，都應該使用MOLAP儲存模式。如果你想了解更多分割槽模式的資訊，參看“附錄 B”。如果你考慮“近實時”部署，參考“近實時資料更新”章節。
在MOLAP分割槽中，事實和聚合資料結構是一樣的。關係型資料庫中事實表的資料被高度壓縮後，儲存到MOLAP分割槽中。記錄（通俗的講，就是二維表的行）是最小儲存單位，每個記錄儲存度量值組的所有度量值和一組內部的DataID用於關聯維度的粒度屬性。256個記錄組成一個頁，256個頁組成一個段。
為了有效地滿足資料請求，儲存引擎應用三種機制優化查詢請求：快取、聚合、事實資料。如圖 4。
圖 4 滿足資料請求
圖 4標示了對{(Europe, 2005), (Asia, 2005)}的資料請求，儲存引擎選擇了下面的途徑實現請求：
1. 儲存引擎快取—儲存引擎首先試圖使用儲存引擎快取滿足資料請求。儲存引擎快取永遠是居於記憶體中的，使用儲存引擎快取提供了最好的效能。關於管理儲存引擎快取的資訊，參考“查詢時記憶體需求”章節。
2. 聚合—如果快取中沒有相關資料，儲存引擎查詢預計算的聚合資料。在某些場合，聚合恰好能滿足資料請求。例如，當按產品類別和年來查詢銷售資料時，就可以使用聚合。儲存引擎也可以使用低階別的聚合資料，例如按月或季度查詢資料。關於如何設計聚合來提供效能，參考“最大化聚合”章節。
3. 事實資料—如果沒有合適的聚合資料能滿足查詢，儲存引擎必須從分割槽中查詢事實資料。儲存引擎使用多種內部優化手段從磁碟查詢資料，包括增強索引和聚類相關記錄。聚合和事實資料都可以將資料的不同部分儲存到磁碟或Windows作業系統檔案快取，這依賴於系統的記憶體轉載方式。
一種優化資料查詢的關鍵手段是使用多分割槽將度量值劃分成不同的資料片斷，從而減少儲存引擎需要掃描的資料量。多分割槽不僅能增強查詢速度，而且更便於資料管理和處理。
從查詢的角度考慮，儲存引擎預計每個MOLAP分割槽儲存的資料，並優化它的MOLAP分割槽並行查詢計劃。例如圖 4，2005的分割槽資料顯示藍色，2006的分割槽顯示黃色，資料請求{(Europe, 2005), (Asia, 2005)}只要求2005的資料，因此儲存引擎只需要查詢2005分割槽。為了優化查詢效能，儲存引擎儘可能的使用並行查詢。為了在分割槽中定位資料，儲存引擎並行查詢段，並使用點陣圖索引有效的掃描頁以找到想要的資料。
分割槽是高效能立方體的主要條件。關於分割槽的好處，參考下面的章節：
· 要了解如何建立點陣圖索引，在分割槽中使用點陣圖索引，參考“分割槽處理作業”章節。
· 要了解分割槽優點的細節，參考“使用分割槽增強查詢效能”章節。
· 要了解分割槽對處理所帶來的好處，參考“使用分割槽增強處理效能”章節。