Alex's BI Blog  
公告
日历
统计
  • 随笔 - 11
  • 文章 - 0
  • 评论 - 5
  • 引用 - 0

导航

 

构建大型关系数据仓库的十大最佳实践

撰稿人Stuart Ozer、Prem Mehra 和 Kevin Cox

技术审阅Lubor Kollar、Thomas Kejser、Denny Lee、Jimmy May、Michael Redman 和 Sanjay Mishra

构建大型关系数据仓库是一项复杂的任务。本文介绍一些使用 SQL Server 构建高效的大型关系数据仓库时的设计技巧。由于大多数大型数据仓库都使用表分区和索引分区,所以,本文中的许多建议都涉及区。这些技巧大都是使用 SQL Server 2005 构建大型数据仓库的经验之谈。

 

clip_image001考虑将大型事实数据表分区

  • 考虑将 50-100 GB 或更大的事实数据表分区。
  • 分区可提高易管理性,且通常可改善性能。
    • 索引维护更快、粒度更小。
    • 备份/还原选项更灵活。
    • 数据加载和删除速度更快
  • 当限制在单个分区内时,查询速度会更快。
  • 一般应用日期当作事实表的分区键。
    • 应用可调窗口。
  • 应用查询分区消除。

clip_image002基于事实数据表日期键生成聚集索引

  • 这样可使填充多维数据集或检索历史数据切片的查询更高效。
  • 如果在批处理窗口中加载数据,则对事实数据表上的聚集索引使用 ALLOW_ROW_LOCKS = OFF 和 ALLOW_PAGE_LOCKS = OFF 选项。这有助于在查询期间加快表扫描操作,还有助于避免在大量更新过程中出现过多锁定活动。
  • 为每个外键生成非聚集索引。这有助于“精确定位查询”,即根据选定的维度谓词提取行。对于备份/还原和部分数据库可用性等管理要求,请使用文件组。

clip_image003仔细选择分区粒度

  • 大多数客户使用月、季度或年。
  • 对于高效的删除,必须一次删除一个完整的分区。
  • 一次加载一个完整的分区更快。
    • 对每日加载而言,按天分区可能是个不错的选择。
    • 但请记住,一个表最多只能有 1000 个分区。
  • 分区粒度会影响查询并行度。
    • 搜索单个分区的查询的并行上限为 MAXDOP(最大并行度)。
    • 搜索多个分区的查询对一个分区使用一个线程,并行上限为 MAXDOP。
  • 如果需要 MAXDOP 并行度(假设 MAXDOP = 4 或更大),应避免常用查询只搜索两三个分区的分区设计。

clip_image004恰当地设计维度表

  • 对所有维度,日期维度除外,使用整数代理键; 并将可能的最小整数用于维度代理键。这样可以尽量缩小事实数据表。
  • 使用从 DATETIME 数据类型派生的 Integer 型有意义的日期键(如 20060215)。
    • 不要对日期维度使用代理键
    • 易于编写对此列执行 WHERE 子句的查询,以便能够消除事实数据表的分区。
  • 对每个维度表的业务键(非代理键)生成聚集索引。
    • 加载事实数据表时支持快速查找。
    • 支持快速查找现有维度行,以管理类型 2 更改维度。
  • 对每个维度表的维度键(代理键)生成非聚集主键索引。
  • 对其他经常搜索的维度列生成非聚集索引。
  • 避免将维度表分区。
  • 避免事实数据表和维度表之间存在主键-外键关系。允许快速加载。通过 Transform Lookups 确保完整性,或在数据源处执行完整性检查。

clip_image005编写有效的查询,以便消除分区

  • 尽量将查询谓词(WHERE 条件)直接放在事实数据表的分区键(日期维度键)上。

clip_image006使用可调窗口方法维护数据

  • 维护一个滚动的时间窗口,以便联机访问事实数据表。加载最新的数据,卸载最旧的数据。
  • 始终在分区范围的两端保留空分区,以确保分区拆分(加载新数据之前)和分区合并(卸载旧数据之后)操作不会导致任何数据移动。
  • 避免拆分或合并已填充的分区。拆分或合并已填充的分区效率极低,因为这可能会导致额外产生 4 倍的日志,还会导致严重的锁定。
  • 在要加载的分区所在的文件组中创建加载临时表。
  • 在要删除的分区所在的文件组中创建卸载临时表。
  • 一次加载整个最新分区时速度最快,但仅在分区大小等于数据加载频率时才有可能(例如,您每天有一个分区,每天加载一次数据)。
  • 如果分区大小与数据加载频率不符,应增量加载最新的分区。
  • 将大容量数据加载到分区表的不同做法在白皮书 http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/loading_bulk_data_partitioned_table.mspx 中进行了讨论。
  • 每次始终只卸载一个分区。

clip_image007有效加载初始数据

  • 在初始数据加载期间使用 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 博客文章 http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx

clip_image008有效删除旧数据

  • 尽量使用分区切换。
  • 从非分区的索引表中删除大量行时,
    • 避免使用 DELETE FROM ...WHERE ...,否则
      • 会产生大量锁定和日志记录
      • 如果取消删除,回滚时间会很长
    • 通常,以下方法更快
      • 使用 INSERT 将记录插入到非索引表
      • 对表创建索引
      • 重命名新表来替换原始表名
  • 或者,在循环中重复使用以下语句,“分批”删除
    DELETE TOP (1000) ... ;
    COMMIT
  • 另一种方法是更新行,将其标记为已删除,然后在不忙的时候删除。

clip_image009手动管理统计信息

  • 将分区表的统计信息作为整体来维护。
  • 加载新数据后,请手动更新大型事实数据表的统计信息。
  • 对分区重新生成索引后,请手动更新统计信息。
  • 如果在定期加载后会定期更新统计信息,则可以关闭该表的 autostats。
  • 这对于优化可能只需要读取最新数据的查询很重要。
  • 在增量加载后更新小型维度表的统计信息也可能有助于提高性能。对维度表更新统计信息时使用 FULLSCAN 选项可获得更准确的查询计划。

clip_image010考虑有效的备份策略

  • 对于非常大的数据库来说,备份整个数据库可能需要很长时间。
    • 例如,将一个 2 TB 的数据库备份到 SAN 上拥有 10 个心轴的 RAID-5 磁盘中可能需要 2 个小时(速率为 275 MB/秒)。
  • 使用 SAN 技术的快照备份是个非常不错的选择。
  • 定期减少要备份的数据量。
    • 历史分区的文件组可标记为 READ ONLY。
    • 文件组标记为只读后,只需对其备份一次。
    • 只对读/写文件组执行定期备份。
  • 请注意,不能对只读文件组并行执行 RESTORE。
posted on 2009-02-24 10:25 Alexwei 阅读(...) 评论(...) 编辑 收藏
 
Copyright © Alexwei Powered by: 博客园 模板提供:沪江博客