为什么我应该在 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-tree 搜索可以更快地发生。此外，这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 流程管理这种完整性）。