為什麼我應該在 SQL Server 上“避免對維度表進行分區”？

SQL Cat 有一個名為“建構大型關係數據倉庫的 10 大最佳實踐”的技巧列表。

在4 - Design dimension tables appropriately他們聲明的部分下：

避免對維度表進行分區。

他們沒有提到為什麼不應該這樣做，我在網上也找不到任何明確指出為什麼要避免這樣做的東西。

為什麼要避免對維度表進行分區？

下面提供了一個更具體的例子來幫助回答，並討論為什麼不應該在大型關係數據倉庫中進行分區。我不是在尋找有關改進特定於具體範例的數據模型的建議。如果該範例無法提供任何關於為什麼不應該進行分區維度的額外見解，請忽略它。

---

範例：您可以在答案中參考為什麼分區維度是一個壞/次優的想法（如果它對您有幫助）……

在我們的環境中，我們有一個Account維度，它被分區並每月DateEffective載入。我們的一些查詢涉及，這似乎是一個很好的分區消除候選者。此外，如果我們需要重新載入當月的數據，我們將刪除整個月的數據，這似乎也將從表分區中受益。WHERE DateEffective >= @ReportDate

---

自發布問題以來更新我們的環境……

上面提到的表有非對齊的非聚集索引（用下面的 Brent Ozar 程式碼調查）。

select
   [db_name]               = isnull(db_name(s.database_id),db_name())
   ,[schema_name]          = object_schema_name(i.object_id,db_id())
   ,[object_name]          = o.name
   ,index_name             = i.name
   ,index_type_desc        = i.type_desc
   ,data_space_name        = ds.name
   ,data_space_type_desc   = ds.type_desc
   ,s.user_seeks
   ,s.user_scans
   ,s.user_lookups
   ,s.user_updates
   ,s.last_user_seek
   ,s.last_user_update
from
   sys.objects as o

   inner join sys.indexes as i 
       on o.object_id = i.object_id

   inner join sys.data_spaces as ds 
       on ds.data_space_id = i.data_space_id

   left join sys.dm_db_index_usage_stats as s 
       on  i.object_id     = s.object_id 
       and i.index_id      = s.index_id
       and s.database_id   = db_id()
where 
       o.type      = 'u'
   and i.type      in (1, 2)
   and o.object_id in
   (
        select filter.object_id 
        from
        (
            select ob.object_id, ds.type_desc 
            from 
               sys.objects ob 
               inner join sys.indexes ind on ind.object_id = ob.object_id 
               inner join sys.data_spaces ds on ds.data_space_id = ind.data_space_id
            group by ob.object_id, ds.type_desc 
        ) as filter 
        group by filter.object_id 
        having count(*) > 1
    )
order by
   [object_name] desc
;

這表明：

• clustered分區方案上的索引
• non-clustered分區方案的 8 個索引中的 5 個
• 8 個non-clustered索引中primary的 3 個，rows_filegroup
  • 其中 1 個是unique, non-clustered索引（為了完整起見：primary key non-clustered在原始碼管理的創建表腳本中定義為 a）

---

另一個更新

我找到了 Remus Rusanu 的這個答案，它揭示了與維度相關的分區表的複雜性。

使用我上面的範例，他的陳述與我的解釋一起被引用

---

非對齊索引阻止高效的分區切換操作

因此，我們應該在表被分區時嘗試對齊索引。在我的範例中，甚至沒有使用分區切換（？可能阻止？）載入表，因為存在未對齊的索引。

使用對齊索引解決了這些問題，但也帶來了一系列問題，因為這種物理、儲存設計、選項會影響數據模型

我提供的範例似乎就是這種情況，並且需要進行一些更改才能實現對齊的索引。

由於維度通常使用代理鍵作為primary key(a unique clustered index)，這提供了一個不斷增加的窄鍵（即磁碟上的小數據大小）。這很重要，因為在連接維度和事實時發生的 B 樹搜尋可以更快地發生。此外，這clustered index將是任何non-clustered index創建的 es 的一部分，這也可以防止非聚集索引膨脹，也可以在此處創建更有效的索引查找/掃描。

為什麼這很重要？

對齊索引意味著無法再創建/強制執行唯一約束（分區列除外）

和

引用分區表的所有外鍵都必須包含分區鍵

和

這反過來又要求所有引用分區表的表都包含分區鍵列值…以便正確聲明外鍵約束。

影響是…

• DateEffective需要在我們環境中引用帳戶維度的每個表中添加一列。在我們擁有的事實表上實現一個DateEffective列是多餘的，因為這個查找是由我們載入正確AccountID鍵值的 ETL 過程來處理的。此外，某些事實的聲明粒度比date數據類型更具選擇性，其中DateEffective顯然是這樣，這使得將這一列包含在事實表中變得更加荒謬（數據模型漣漪效應）。
• non-clustered index需要更改許多es 以包含該DateEffective列

然而 …

• 數據倉庫通常沒有實施foreign key約束。關於 SO 的一個很好的答案涵蓋了這一點。
• 此外，自 2008 版以來，Sql Serverparallel bitmap filtered hash-joins可用於優化星型連接（請參閱：通過點陣圖過濾優化數據倉庫查詢性能），並且此優化不需要外鍵。
• 這似乎表明可以對維度表進行分區，因為現在所需的更改“僅”必須將分區鍵包含到非對齊索引中，因為在我們的環境中不存在外鍵約束問題（我們的 ETL 流程管理這種完整性）。

我懷疑該建議是基於對維度表進行分區的可能用途。在數據倉庫中，事實表是格言的一個很好的例子，大數據是中等數據，加上時間。維度表沒有時間（不是真的），並且通常沒有有用的分區屬性。

你的似乎是一個很好的例子。為什麼要Accounts 分區DateEffective？“因為某些報告在該列上選擇”不是一個充分的答案。該列上的索引將是正常解決方案，並且具有不偏向物理資料結構的優點。

不管你有多少賬戶，你的事實表至少要大 1-3 個數量級。您的伺服器按比例縮放。查找帳戶是一項相對簡單的操作。從表面上看，它似乎不是分區的候選者。

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