星型連接查詢優化 - 更改分區，使用列儲存？

我想知道提高客戶給我的查詢性能的最佳方法。它包含幾個連接的表，其中一個表被稱為dwh.fac_sale_detail包含 15 億行。

該表dwh.fac_sale_detail基於其名為 的列之一進行分區TradingDateKey1。它實際上以 yyyymmdd 格式儲存數據，但它是INTDatatype。

這具有從 2005 年到 2015 年的 TradingDateKeys，但僅在 2014 年之前創建分區。

另一個團隊中的一個人提出了以下建議，我正在嘗試遵循他的建議，但我是創建或更改分區的新手，不知道這是否會對查詢性能產生任何影響：

用他自己的話來說是“該FactSalesDetail表目前大約有 15 億行，目前TradingDate按年劃分為 10 個分區，每個分區大約有 1.5 億行。最好將最近一年進一步劃分為每月分區。並在所有分區上應用列儲存索引。在每個分區上應用索引將是一次性的，您只需要維護目前分區的索引即可。”

這是我要優化的查詢的查詢計劃。

另請參閱隨附的螢幕截圖以更好地理解：

感謝您添加查詢計劃；這是非常有用的。我有一些基於查詢計劃的建議，但首先要注意：不要只接受我所說的並假設它是正確的，首先嘗試一下（最好是在你的測試環境中），並確保你理解為什麼會發生這些變化或者不要改進您的查詢！

查詢計劃：概述

從這個查詢計劃（以及相應的 XML）中，我們可以立即看到一些有用的資訊：

• 您使用的是 SQL 2012
• 這是一個經典的星形連接查詢，您將受益於 SQL 2008 中為此類計劃添加的行內點陣圖過濾器優化
• 事實表包含大約 15 億行，其中超過 5 億行與維度過濾器匹配
• 該查詢請求 72GB 記憶體，但僅授予 12GB 記憶體（假設 12GB 是授予任何給定查詢的最大值，這意味著您的電腦可能有 ~64GB 記憶體）
• SQL Server 正在執行一個排序流聚合，它將 5 億行減少到 600,000 行。排序超出了它的記憶體授權並溢出到 tempdb
• 由於查詢中的顯式和隱式轉換，我們對影響計劃的轉換發出警告
• 該查詢使用 32 個執行緒，但對事實表的初始搜尋具有巨大的執行緒偏差；32 個執行緒中只有 2 個完成所有工作。（然而，在查詢計劃的後續步驟中，工作更加平衡。）

優化：列儲存與否

這是一個棘手的問題，但總的來說，在這種情況下，我不會為您推薦 columnstore。主要原因是您使用的是 SQL 2012，因此如果您能夠升級到 SQL 2014，我認為可能值得嘗試列儲存。

通常，您的查詢是為列儲存設計的類型，並且可以從列儲存減少的 I/O 和批處理模式的更高 CPU 效率中受益匪淺。

但是，SQL 2012中列儲存的限制太大了，tempdb 溢出行為（任何溢出都會導致 SQL Server 完全放棄批處理模式）可能是毀滅性的懲罰，可能會在您使用大量行時發揮作用正在使用。如果您確實在 SQL 2012 上使用列儲存，請準備好密切關注所有查詢，並確保始終可以使用批處理模式。

優化：更多分區？

我不認為更多的分區會幫助這個特定的查詢。當然，歡迎您嘗試它，但請記住，分區主要是一種數據管理功能（通過SWITCH PARTITION而不是性能功能在 ETL 流程中交換新數據的能力。它在某些情況下顯然可以提高性能，但類似地，它會損害其他人的性能（例如，現在必須每個分區執行一次的大量單例查找）。

如果您確實使用列儲存，我認為載入數據以更好地消除段將比分區更重要；理想情況下，您可能希望每個分區中的行數盡可能多，以便擁有完整的列儲存段和出色的壓縮率。

優化：改進基數估計

因為您有一個巨大的事實表和來自每個維度表的少數非常小的（數百或數千行）行集，所以我建議您顯式創建一個臨時表，其中僅包含您計劃使用的維度行. 例如，您應該編寫一個預處理查詢以僅從中提取您關心的行並將適當的 PK 添加到這些行，而不是Dim_Date使用複雜的邏輯來連接。cast(right(ALHDWH.dwh.Dim_Date.Financial_Year,4) as int) IN ( 2015, 2014, 2013, 2012, 2011 )``Dim_Date

這將允許 SQL Server 僅針對您實際使用的行創建統計資訊，這可能會在整個計劃中產生更好的基數估計。因為與整體查詢複雜性相比，這種預處理工作量非常小，我強烈推薦這個選項。

優化：減少執行緒傾斜

從它自己的表中提取數據Dim_Date並向該表添加主鍵也可能有助於減少執行緒傾斜（執行緒間工作的不平衡）。這是一張有助於說明原因的圖片：

在本例中，Dim_Date表有 22,000 行，SQL Server 估計您將使用其中的 7,700 行，而您實際上只使用了其中的 1,827 行。

由於 SQL Server 使用統計資訊將行範圍分配給執行緒，因此在這種情況下，基數估計不佳可能是行分佈非常差的根本原因。

1,872 行上的執行緒傾斜可能無關緊要，但痛苦的一點是，這會級聯到您的 15 億行事實表中，我們有 30 個執行緒處於空閒狀態，而 6 億行正在由 2 個執行緒處理。

優化：擺脫排序溢出

我要關注的另一個領域是排序溢出。我認為這種情況下的主要問題是基數估計差。正如我們在下面看到的，SQL Server 認為由 aSort和的組合執行的分組操作Stream Aggregate將產生 3.24 億行。然而，它實際上只產生了 643,000 行。

如果 SQL Server 知道從這個分組中出來的行數很少，它幾乎肯定會使用HASH GROUP(Hash Aggregate) 而不是SORT GROUP(Sort-Stream) 來實現您的GROUP BY子句。

如果您進行上述其他一些更改以改進基數估計，這可能會自行解決。但是，如果不是，您可以嘗試使用OPTION (HASH GROUP) 查詢提示來強制 SQL Server 這樣做。這將讓您評估改進的幅度並決定是否在生產中使用查詢提示。我通常對查詢提示持謹慎態度，但指定 justHASH GROUP比使用連接提示、使用FORCE ORDER或以其他方式從查詢優化器手中奪走太多控制權要輕得多。

優化：記憶體授予

最後一個潛在問題是 SQL Server 估計查詢需要使用 72GB 記憶體，但您的伺服器無法為查詢提供這麼多記憶體。雖然從技術上講，向伺服器添加更多記憶體會有所幫助，但我認為至少還有其他幾種方法可以解決這個問題：

• 擺脫Sort運營商（如上所述）；它確實是唯一一個在您的查詢中消耗大量記憶體授權的運算符
• 將您的查詢分成多個批次；例如，您可能會在每個分區執行一次查詢。這可以減少排序的大小，將其保存在記憶體中，並可能顯著提高性能。附帶的好處可能是，如果您只訪問一個分區，您可能會更好地利用執行緒，因為這確實會影響 SQL Server 在某些情況下將執行緒分配給分區的方式。

優化 STAR 查詢在許多方面與優化其他查詢樣式相同。此外，STAR 查詢提出了一些特殊的考慮。重新審視一些必需品和基礎知識可能會讓您付出代價。

1. 精密風格與倉庫風格。任何查詢 STAR 或其他方式都可以歸類為 PRECISION 或 WAREHOUSE 樣式。我們的意思是它返回大量行或少量行。大和小通常被定義為過濾後表中的行的百分比，在此上下文中，經常引用 2% 規則，因為這個百分比在數學上是合理的，並且在現實世界的實踐中作為大多數查詢情況的指南是成功的在定義查詢樣式時。這意味著如果您想要表中 <2% 的行，那麼索引可能更適合獲取這些行，但如果您想要表中 >2% 的行，那麼表掃描可能會更好。是的，有邊緣情況，以及特定數據偏差的變化，
2. 鑑於“2% RULE%”的想法，真正的 STAR 查詢旨在返回其事實表中 <2% 的行。由維度包圍並在 BITMAP 索引中覆蓋的事實表並不意味著針對此設計的查詢是 STAR 查詢。從事實表返回> 2% 的行的“STAR 查詢”不是慢速STAR 查詢，而是使用錯誤數據庫設計的數據探勘操作。此外，BITMAP 索引是專門為優化的 STAR Query 問題創建的。因此，對於 STAR 查詢，雖然沒有特定的單個索引必須辨識 <2% 的行，但在所有搜尋的索引通過 BITMAP MERGE 步驟組合之後，返回的結果行集應該是事實表中 <2% 的行。這是在事實表的 RAW 行中定義的，而不是在聚合行中定義的。否則，在大多數情況下，您最好只掃描 FACT 表，而不是在索引上浪費時間。因此，即使是 STAR Query 在優化空間中也受到 PRECISION 與 WAREHOUSE 風格的概念的支配。
3. 性能分區。從性能角度來看，分區有兩個主要用途。首先是分區修剪。對於謂詞提供針對分區鍵的過濾的查詢，優化器可以跳過整個分區而不是掃描它們。這減少了IO。第二個用途是啟用作為分區對完成的並行連接。如果查詢中的兩個表都在查詢的連接鍵上等分區，則該查詢可以僅使用匹配的分區對來並行連接兩個表。這使得連接具有高度可擴展性，並顯著減少了記憶體需求。同樣，這裡的關鍵是跨連接列的等分。
4. 列式儲存和性能。列式數據儲存也以兩種基本方式提供性能。首先是壓縮。這種儲存策略將相似的數據放在一起。因此，特別是如果採取預排序步驟，可以在列數據儲存中實現大的壓縮比。這種壓縮在合適的條件下可以大大降低IO成本。其次，這種儲存策略試圖利用大多數查詢不需要所有列的事實。因此，列式數據儲存可能會跳過查詢不需要的列。這同樣可以降低 IO 成本。作為一般規則，如果查詢需要表中 <5% 的列，則列投影帶來的性能提升可能非常顯著。
5. 預聚合。匯總表在性能方面仍然發揮著重要作用。獲取已經求和的行總是更快，而不是每次需要時獲取詳細資訊並求和它們。在 STAR Query 空間中，這通常意味著監控查詢以查看哪些最流行的維度正在被切片/切塊/分層遍歷。假設您的數據庫系統能夠在需要時透明地辨識這些匯總對象，那麼這些是匯總表的良好候選者。

考慮到這一點，首先問自己一些問題：

1. 你有正確的設計嗎？您的查詢真的是一個 STAR 查詢，它最終會重新調整事實表中 <2% 的行嗎？或者您是否正在嘗試針對錯誤的儲存設計做一些大事？
2. 您的查詢謂詞是否映射到您的分區方案？如果是這樣，他們是否在利用分區（實際上是在進行修剪）？如果是這樣，那麼您是否可能是跨其中一個連接的並行候選者？
3. 您要查找的列的百分比是多少？您是否可以使用壓縮將空間需求減少一個數量級，從而通過減少 IO 來加快查詢時間？您是否正在查找表中 <5% 的列，因此可能會受益於列儲存以跳過表中未使用的部分。
4. 您的查詢成本中有多少來自 IO，有多少來自聯接/聚合/排序？您的成本所處的位置將推動您對性能特徵的選擇。
5. 是否存在特定的聚合，如果它們存在會極大地改變您的查詢性能配置文件？

至於你朋友的建議，你應該要求更多的澄清。那裡顯然有很多假設。1. 舊分區是只讀的。2.您的查詢是獲取特定月份而不是跨年（例如比較兩個季度）。

最後考慮一下，如果您的朋友事物分區和列儲存可能是重要的幫助，那麼這表明您的查詢不是真正的 STAR 查詢，因為如果是，那麼 BITMAP INDEXING 將是查詢及其分區和列儲存的主要性能驅動因素對整體性能意義不大。因此很明顯，您首先需要查看查詢想要什麼並將其映射到正確的儲存設計。

引用自：https://dba.stackexchange.com/questions/115220