Trim_reason = 聚集列儲存索引上的 DICTIONARY_SIZE

在具有 170 億行的分區表上，我得到了非常多的 row_groups。

我聽說我應該嘗試在每個行組中獲得 1.048.576 行。我該如何做到這一點？

據報導，這trim_reason是。DICTIONARY_SIZE``sys.dm_db_column_store_row_group_physical_stats

當我的批處理作業每批插入 1,048,576 行時，我平均每個行組有 100.000 行。

我對列儲存索引相當陌生，但我能找到的唯一參考DICTIONARY_SIZE是字元串列。我的表只有bit、bigint和numeric列。

我的表看起來像這樣（列名被隱藏），而且我知道很多列主要包含 NULL。如果有值，它們可能會有很大差異（大標準偏差）。是振動數據。

CREATE TABLE fct.MeasureV2
(
MeasureV2ID bigint NOT NULL IDENTITY(1, 1),
DT1ModelID int NOT NULL CONSTRAINT DF_MeasureV2_DT1ModelID DEFAULT ((0)),
DT1TID int NOT NULL CONSTRAINT DF_MeasureV2_DT1TID DEFAULT ((0)),
TimeStampUTC datetime NOT NULL,
AChkSum bigint NULL,
OChkSum bigint NULL,
SChkSum bigint NULL,
ATp numeric (18, 10) NULL,
ATpAvg numeric (18, 10) NULL,
AWDAbsAvg numeric (18, 10) NULL,
G1TMF numeric (18, 10) NULL,
G2Ps_1TMF numeric (18, 10) NULL,
G2Ps_2TMF numeric (18, 10) NULL,
/* about 600 more numeric (18, 10) NULL columns */
WdSdv numeric (18, 10) NULL,
UpdatedDate datetime NOT NULL,
UpdatedID bigint NULL,
IsCorrected bit NOT NULL CONSTRAINT DF_MeasureV2_IsCorrected DEFAULT ((0)),
TRATransformJobID int NOT NULL
) ON PS_FctMeasure (TimeStampUTC)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_MeasureV2 ON fct.MeasureV2 ON PS_FctMeasure (TimeStampUTC)
GO

有很多列，但替代的屬性值表將包含超過 2000 億行，並且幾乎不可能查詢。這種格式很慢，但不會太慢。

我們也嘗試過SPARSE列，但這會佔用大量磁碟空間。

我聽說我應該嘗試在每個 row_group 中獲得 1.048.576 行。

這是一個理想，但在實踐中並不總是可以實現。在任何情況下，這通常不是您應該關注的主要因素。

…我能找到的對 DICTIONARY_SIZE 的唯一參考是字元串列

使用長字元串列更容易展示字典壓力，僅此而已。

字典編碼適用於所有數據類型。另一種基於值的編碼僅適用於數字類型（在最廣泛的意義上）。

值編碼通過縮放和重新設置數值來工作。例如，值 {1100, 1200, 1300} 可以儲存為 (0, 1, 2)，方法是先按 0.01 倍縮放以得到 {11, 12, 13}，然後從 11 變基得到 {0, 1, 2}。

使用字典編碼，每個唯一值都儲存在字典中並分配一個整數 id。然後段數據引用字典中的 id 編號而不是原始值。

使用字典時，主字典儲存整個列的值。可以為列的每個段（即每個行組）創建一個附加的輔助字典。

字典的最大記憶體大小為 16MB。它在持久儲存時被壓縮。如果字典已滿，則在列儲存建構期間行組的大小會自動減小。

但請注意，如果同一個執行緒負責建構它們，則多個行組可能會引用同一個輔助字典。如果該字典達到其最大大小，則引用它的所有行組的大小也將受到限制。

您可以找到 中使用的編碼類型sys.column_store_segments。

可以從 中找到字典的類型和與之關聯的數據sys.column_store_dictionaries。

使用固定的架構和數據集，您幾乎無法影響 SQL Server 使用的編碼和壓縮方案的類型。

如果您的列儲存已經分區良好並且在實踐中表現得足夠好，那麼由於字典大小限制，我不會太擔心較小的行組。

避免增量儲存、在執行時實現分區/行組消除（如果可能）以及聚合下推通常更為重要。

我猜不是每個查詢都使用了所有 600 多列。另外我猜有些列比其他列有更多不同的值，所以更快地填滿字典。如果幸運的話，這些高基數列很少使用。

所以我建議對大表進行垂直分區。每個新的子表都會有主鍵列和一些列。這個想法是將通常一起使用的列保持在一起。如果需要連接，則子表之間存在隱式的一對一關係。當然，如果有幫助，可以在子表之間複製列。

如果行組的任何字典達到限制，則行組會更小。因此，如果 col 1 具有高基數或 col 2 具有高基數或 col 3 … 或 col 610 具有高基數或 col 611 具有高基數，則行組很小。填字典的方法有很多。如果只有 20 列，那麼您的機率會好得多。

當然，某些表必須具有高基數列。它仍然會有小的行組。但這不會比現在的情況更糟。

建構這些子表需要在磁碟上移動數據。所以這麼多行不會很快，並且需要大量的備用容量。也許一次做一個分區會起作用？

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