构建大型关系数据仓库的十大最佳实践
撰稿人：Stuart Ozer、Prem Mehra 和 Kevin Cox
技术审阅人：Lubor Kollar、Thomas Kejser、Denny Lee、Jimmy May、Michael Redman 和 Sanjay Mishra
构建大型关系数据仓库是一项复杂的任务。本文介绍一些使用 SQL Server 构建高效的大型关系数据仓库时的设计技巧。由于大多数大型数据仓库都使用表分区和索引分区，所以，本文中的许多建议都涉及区。这些技巧大都是使用 SQL Server 构建大型数据仓库的经验之谈。
考虑将大型事实数据表分区
考虑将 50-100 GB 或更大的事实数据表分区。分区可提高易管理性，且通常可改善性能。 索引维护更快、粒度更小。备份/还原选项更灵活。数据加载和删除速度更快当限制在单个分区内时，查询速度会更快。一般应用日期当作事实表的分区键。 应用可调窗口。应用查询分区消除。
基于事实数据表的日期键生成聚集索引
这样可使填充多维数据集或检索历史数据切片的查询更高效。如果在批处理窗口中加载数据，则对事实数据表上的聚集索引使用 ALLOW_ROW_LOCKS = OFF 和 ALLOW_PAGE_LOCKS = OFF 选项。这有助于在查询期间加快表扫描操作，还有助于避免在大量更新过程中出现过多锁定活动。为每个外键生成非聚集索引。这有助于“精确定位查询”，即根据选定的维度谓词提取行。对于备份/还原和部分数据库可用性等管理要求，请使用文件组。
仔细选择分区粒度
大多数客户使用月、季度或年。对于高效的删除，必须一次删除一个完整的分区。一次加载一个完整的分区更快。 对每日加载而言，按天分区可能是个不错的选择。但请记住，一个表最多只能有 1000 个分区。分区粒度会影响查询并行度。 搜索单个分区的查询的并行上限为 MAXDOP（最大并行度）。搜索多个分区的查询对一个分区使用一个线程，并行上限为 MAXDOP。如果需要 MAXDOP 并行度（假设 MAXDOP = 4 或更大），应避免常用查询只搜索两三个分区的分区设计。
恰当地设计维度表
对所有维度，日期维度除外，使用整数代理键； 并将可能的最小整数用于维度代理键。这样可以尽量缩小事实数据表。使用从 DATETIME 数据类型派生的 Integer 型有意义的日期键（如 0215）。 不要对日期维度使用代理键易于编写对此列执行 WHERE 子句的查询，以便能够消除事实数据表的分区。对每个维度表的业务键（非代理键）生成聚集索引。 加载事实数据表时支持快速查找。支持快速查找现有维度行，以管理类型 2 更改维度。对每个维度表的维度键（代理键）生成非聚集主键索引。对其他经常搜索的维度列生成非聚集索引。避免将维度表分区。避免事实数据表和维度表之间存在主键-外键关系。允许快速加载。通过 Transform Lookups 确保完整性，或在数据源处执行完整性检查。
编写有效的查询，以便消除分区
尽量将查询谓词（WHERE 条件）直接放在事实数据表的分区键（日期维度键）上。
使用可调窗口方法维护数据
维护一个滚动的时间窗口，以便联机访问事实数据表。加载最新的数据，卸载最旧的数据。始终在分区范围的两端保留空分区，以确保分区拆分（加载新数据之前）和分区合并（卸载旧数据之后）操作不会导致任何数据移动。避免拆分或合并已填充的分区。拆分或合并已填充的分区效率极低，因为这可能会导致额外产生 4 倍的日志，还会导致严重的锁定。在要加载的分区所在的文件组中创建加载临时表。在要删除的分区所在的文件组中创建卸载临时表。一次加载整个最新分区时速度最快，但仅在分区大小等于数据加载频率时才有可能（例如，您每天有一个分区，每天加载一次数据）。如果分区大小与数据加载频率不符，应增量加载最新的分区。将大容量数据加载到分区表的不同做法在白皮书 /technet/prodtechnol/sql/bestpractice/loading_bulk_data_partitioned_table.mspx 中进行了讨论。每次始终只卸载一个分区。
有效加载初始数据
在初始数据加载期间使用 SIMPLE 或 BULK LOGGED 恢复模式。创建带有聚集索引的分区事实数据表。为每个分区创建非索引临时表，并使用各自不同的源数据文件填充每个分区。并行填充临时表。 使用多个 BULK INSERT、BCP 或 SSIS 任务。 如果没有 IO 瓶颈，请创建与 CPU 数量一样多的加载脚本，将它们并行运行。如果 IO 带宽有限，则使用较少的并行脚本。在加载中使用 0 批大小。在加载中使用 0 提交大小。使用 TABLOCK。如果源文件为同一个服务器上的平面文件，则使用 BULK INSERT。如果要从远程计算机推送数据，则使用 BCP 或 SSIS。对每个临时表生成聚集索引，然后创建适当的 CHECK 约束。不要使用 SORT_IN_TEMPDB 选项。使用 SWITCH 将所有分区切换到分区表中。对分区表生成非聚集索引。在吞吐量可达到 14 GB/秒的 SAN 中，在 64 个 CPU 的服务器上，一小时之内可以加载 1 TB（非索引表）。有关详细信息，请参阅 SQLCAT 博客文章 /sqlcat/archive//05/19/602142.aspx。
有效删除旧数据
尽量使用分区切换。从非分区的索引表中删除大量行时， 避免使用 DELETE FROM ...WHERE ...，否则 会产生大量锁定和日志记录如果取消删除，回滚时间会很长通常，以下方法更快 使用 INSERT 将记录插入到非索引表对表创建索引重命名新表来替换原始表名或者，在循环中重复使用以下语句，“分批”删除
DELETE TOP (1000) ... ;
COMMIT另一种方法是更新行，将其标记为已删除，然后在不忙的时候删除。
手动管理统计信息
将分区表的统计信息作为整体来维护。加载新数据后，请手动更新大型事实数据表的统计信息。对分区重新生成索引后，请手动更新统计信息。如果在定期加载后会定期更新统计信息，则可以关闭该表的 autostats。这对于优化可能只需要读取最新数据的查询很重要。在增量加载后更新小型维度表的统计信息也可能有助于提高性能。对维度表更新统计信息时使用 FULLSCAN 选项可获得更准确的查询计划。
考虑有效的备份策略
对于非常大的数据库来说，备份整个数据库可能需要很长时间。 例如，将一个 2 TB 的数据库备份到 SAN 上拥有 10 个心轴的 RAID-5 磁盘中可能需要 2 个小时（速率为 275 MB/秒）。使用 SAN 技术的快照备份是个非常不错的选择。定期减少要备份的数据量。 历史分区的文件组可标记为 READ ONLY。文件组标记为只读后，只需对其备份一次。只对读/写文件组执行定期备份。请注意，不能对只读文件组并行执行 RESTORE。
如果觉得《构建大型关系数据仓库的十大最佳实践》对你有帮助，请点赞、收藏，并留下你的观点哦！
