我應該為聚集列儲存索引表創建多少個分區？我也應該對行儲存表進行分區嗎？

我有一個由四個聚集列儲存索引表 (CCI) 和九個行儲存表組成的數據倉庫。這些表僅用於分析，每 15 分鐘從臨時表插入 CCI 數據。我希望通過添加分區和排序來優化查詢性能。

對該數據的所有查詢都基於一個具有大約 350 個不同值的整數欄位。最左邊的 CCI 有 100M 條記錄和 125 列。三個子 CCI 具有相同的整數欄位。CCI 2 有 15M 記錄和 150 列，CCI 3 和 4 都有大約 30M 記錄和 25 列。

在這 350 個不同的整數中，最左邊的表中記錄數的分佈如下：

• 5% 大於 100 萬
• 46% 大於 100K
• 83% 大於 10K

此外，還有九個其他行儲存表也加入了 CCI。這些具有涓流插入，是 CCI 的子項，並且它們都包含相同的整數欄位。這些行儲存具有相似或較小的記錄量，每列 < 10 列，兩個包含 LOBS，兩個經常進行大量更新（這些更新也基於 ID 欄位）。

我應該做多少個分區？

我也應該對行儲存表進行分區嗎？

有沒有我忽略的重要考慮因素？

關於我之前提到的“排序”的注意事項：

最左側 CCI 中的日期欄位通常是這些查詢中的次要謂詞，因此我正在考慮每四個星期左右按日期重新排序 CCI 以作為維護。我將通過刪除 CCI、在日期上添加聚集行儲存索引、刪除該索引，然後重新添加 MAXDOP=1 的 CCI 來實現這種排序。我還在考慮按父級的連接鍵對子級 CCI 進行排序。

更新已將分區一直帶到生產：

為聚集列儲存索引 (CCI) 確定正確的分區是一個非常定制的過程。如果選擇了錯誤的分區，性能和壓縮將比非分區方案更差。

因為我要對四個 CCI 進行分區，所以我選擇了記錄最少的 CCI，並將其記錄數除以 1,048,576（理想的 CCI 行組大小）。我使用該商作為我建議的分區數。然後我根據該方案執行記錄計數查詢以返回每個分區的實際行數。此步驟是為了確保分區之間的記錄分佈合理均勻。有。幸運的我。

出現了一個障礙：這個生產分析過程幫助我得出了正確的分區數量，但僅限於生產。我的較低環境比生產環境小得多。所選的分區級別對數據進行瞭如此精細的切片，以至於我根本沒有完整的行組。較低的數據庫變得更大，查詢時間保持不變。IO 確實大幅下降，我不得不反復指出這一點，因為該計劃的收益受到質疑。在我投入生產之前，很難證明分區真的會有所幫助。

結果：分區在生產中取得了巨大成功。IO 大幅下降，我的查詢時間減少了 70% 或更多。我還有更多選項可以以小塊的形式管理這些表。

一些注意事項：選擇正確的欄位進行分區。如果您的查詢必須導航很多分區，您可能會發現性能下降。此外，我還留出了增長空間，為我的分區函式添加了分區和範圍，用於儲存現在不存在但有一天會出現的數據。

僅來自本地測試的原始答案：

自從提出這個問題以來，我一直在當地做更多的研究和 POC。有人建議我在答案中分享這個 POC。

在我的 POC 中，我選擇使用以下分區函式：

CREATE PARTITION FUNCTION [MyIntPF](int) 
AS RANGE LEFT 
FOR VALUES (
   N'50'
   , N'100'
   , N'150'
   , N'200'
   , N'250'
   , N'300'
   , N'350'
   , N'400'
   , N'450'
   , N'500'
);

CREATE PARTITION SCHEME [MyIntPS] 
AS PARTITION [MyIntPF] 
TO (
   [MyInt050fg]
   , [MyInt100fg]
   , [MyInt150fg]
   , [MyInt200fg]
   , [MyInt250fg]
   , [MyInt300fg]
   , [MyInt350fg]
   , [MyInt400fg]
   , [MyInt450fg]
   , [MyInt500fg]
   , [MyInt000fg]
);

此函式為每個分區分配 50 個 MyInts，並留有一點增長空間。

請記住，我在 PROD CCI 的 170M 記錄中大約有 350 個不同的 MyInts。David Browne 建議分區中的最小記錄大小為 1M，這對於優化 CCI 壓縮段是有意義的。我犯了更大的錯誤有兩個原因。第一個原因是為了避免創建一個 100 分區的 POC 怪物。第二個是我假設1M適用於分區中的每個表。我正在對四個列儲存進行分區，其中最小的有 25M 條記錄。如果我把它分成 100 塊，它就永遠不會完整。

在我的本地開發數據庫中，我在最左邊的 CCI 中有 220 萬條記錄，甚至比子 CCI 中的記錄還要少。這為創建 PROD 的真實複製提出了問題。我真的應該優先考慮一些額外的時間來為此創建一個大型本地數據庫，但與此同時，這裡是本地分區的之前/之後的 IO 結果。我從最左邊的 CCI 中查詢了基於 MyInt = 單個值的聚合。

未分區

掃描計數 1，邏輯讀取 0，物理讀取 0，預讀讀取 0，lob 邏輯讀取 1548，
lob 物理讀取 0，lob 預讀讀取 44。
段讀取 4，段跳過 0。

分區

掃描計數 1，邏輯讀取 0，物理讀取 0，預讀讀取 0，lob 邏輯讀取 268，
lob 物理讀取 0，lob 預讀讀取 0。
段讀取 1，段跳過 0。

正如預期的那樣，SQL Server 能夠在使用 MyInt 相等謂詞的查詢中跳過除我的一個分區之外的所有分區。

我正在繼續努力，隨著事情的進展，應該有時間在這裡更新。

對 CCI 進行分區的好處：

1. 可以提高查詢性能，因為無論數據如何載入或修改，都可以保證最低級別的行組消除。大多數通用 SQL Server 分區指南都沒有考慮到這一點。
2. 提高了維護操作的靈活性，您可以在分區級別進行重建或在分區級別進行重組（在分區切換後）。您還可以將不同的分區發送到不同的文件組，但我需要提醒您這樣做幾乎不會提高性能。文件組是一項維護功能。增加文件數有時可以提高性能。根據您的儲存設置，您幾乎肯定希望與查詢相關的數據分佈在多個文件中以改進 I/O。
3. 在同一列上，分區消除比行組消除涵蓋更多的場景。例如，過濾器WHERE ID < 0 OR ID > 10將不適合行組消除，但符合分區消除的條件。
4. 在執行需要更改所有行的維護操作時，按分區循環會很有幫助。例如，假設您要向表中添加一個新列，該列可以從該表中的現有列派生而來。如果需要，分區允許您有效地將工作拆分為批次。

對 CCI 進行分區的缺點：

1. 如果不進行維護，增量行組中的行數會急劇增加。考慮在 MAXDOP 8 載入並行插入的未分區 CCI。增量儲存中最多有 4194304 行。如果將表更改為具有 50 個分區，則增量儲存中現在可能有 209715200 行。
2. 用於插入和刪除列儲存的查詢計劃可能包含一個排序運算符作為 DML 運算符的子項。如果這種排序無法獲得足夠的記憶體，您最終可能會導致性能極度下降。如果使用並行插入，我建議一次只修改一個分區。
3. 如果您不明智地選擇分區函式，您最終可能會得到太小的分區。許多人會指出行組的 1048576 行限制是理想的大小，但我個人認為到達那裡的好處被誇大了。如果可以的話，您可能確實希望避免使用許多小分區。
4. 如果您的表或數據庫中有太多分區，那麼可能會發生不好的事情。不幸的是，這沒有很好的定義，而且很難找到“太多分區”實際上意味著什麼的可靠來源。我聽說並看到了查詢編譯時間的問題。這裡也有一個最近的答案DBCC CHECKTABLE。

將上述內容應用於您的場景：使用您擁有的行數，您不應該遇到任何非常糟糕的情況。對於查詢性能，有些人需要非常快的查詢執行時間，他們需要盡可能多地跳過行組。其他人只需要最低級別的行組消除，因為查詢中完成的大部分工作都在列儲存掃描之外。這使得外部人員很難為您推薦分區數量。對於 1 億張桌子，從 4 到 100 的任何值都可能是合理的。

您可以嘗試使用分區中不同數量的行測試您的一些查詢，以查看性能如何變化。這可以通過創建表的副本或通過在一個具有故意偏斜度的表上創建分區函式並更改您過濾的 ID 來模擬。如果您採用能夠產生足夠好的查詢性能的方法並驗證載入數據不會有任何問題，那麼您應該很好。

行儲存與問題無關，或者更確切地說，它們是一個完全不同的問題。分區不是提高行儲存查詢性能的正確工具。我已經看到僅通過對列儲存表進行分區並單獨保留行儲存表來提高系統性能。

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