主页 / dba / 问题 / 45613
Accepted
db2
Asked: 2013-07-03 12:00:05 +0800 CST

对超大表进行分区/索引

  • 772

我正在对大约 500 GB 的单个数据仓库表进行索引和分区。该表是一个堆，有一百多TEXT列，并且该TEXT_IN_ROW选项已启用。这张桌子不是我设计的，我也没有能力在不久的将来改变它。

我的任务是对它进行分区。我们正在使用测试服务器上的数据库副本来解决这个问题。它可以每秒向 SSD RAID 阵列推送大约 2 GB 的数据，因此 I/O 不是一个重大瓶颈，它有 16 个内核（2 个 NUMA 节点）和 64 GB 的 RAM。

我的方法是禁用所有非聚集索引，创建分区函数和分区方案（大约 12 个分区，全部在PRIMARY文件组上 - 他们使用它来启用滚动维护并为夜间 ETL 提供更多本地化插入，而不是分发我/O)，然后使用此分区方案为表构建聚集索引。

我正在创建聚集索引并对表进行分区，如下所示：

CREATE CLUSTERED INDEX CX_DailyTable ON DailyTable (LoadDate, SeqNumber) 
  WITH (SORT_IN_TEMPDB = ON) ON monthly_on_primary (LoadDate)

显然，这需要很长时间（到目前为止，这篇文章需要 3 个小时），而且我当然不希望它很快。让我稍微担心的是 tempdb 现在正在推动近 1 TB 并稳步攀升，尽管当前表的大小约为该大小的一半。我读过的 MS 文档建议 tempdb 空间使用量应该大约是最终表/聚集索引的大小。

http://msdn.microsoft.com/en-us/library/ms188281.aspx

如果 SORT_IN_TEMPDB 设置为 ON，则 tempdb 中必须有足够的可用空间来存储排序运行，并且目标文件组中必须有足够的可用空间来存储最终的索引结构。排序运行包含索引的叶行。

他们的估计不正确吗？tempdb 的用途不仅仅是排序运行吗？或者创建这个聚集索引以某种方式使表的大小增加了一倍？（似乎不太可能；这是一个相当宽的表，我估计我们每行会获得额外的 4-8 个字节，加上通过添加聚集索引的非叶页。）

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    我的方法是禁用所有非聚集索引 [...] 然后使用此分区方案为表构建聚集索引。

    在堆上创建聚集索引会自动重建所有非聚集索引（甚至是禁用的索引）。非聚集索引被重建但不被分区。假设所需的最终状态是具有对齐索引的分区聚集表，将非聚集索引重建为非对齐完全是浪费精力。

    让我稍微担心的是 tempdb 现在正在推动近 1 TB 并稳步攀升，尽管当前表的大小约为该大小的一半。我读过的 MS 文档建议 tempdb 空间使用量应该大约是最终表/聚集索引的大小。

    排序空间的问题非常复杂。要了解所有细节（包括并行性的影响），您需要仔细阅读SQL Server 查询处理团队的整个系列文章。将堆转换为启用并行的分区聚簇表可能非常接近最坏的情况。

    在最基本的情况下（忽略 QP 团队帖子中的大部分重要信息），您要求 SQL Server 运行如下查询：

    SELECT *
FROM DailyTable
ORDER BY
    $partition.monthly_on_primary(LoadDate),
    LoadDate,
    SeqNumber;

    无论您选择将不适合内存的排序运行写入何处，此查询都不会快速执行。再加上在单独的行集中实际构建整个数据集的完整新副本的工作，以及毫无意义地重建非聚集索引所涉及的工作......

    建议

    要使此更改有效地工作，有许多考虑因素。重要的是尽可能避免排序，并尽可能使用并行最小日志批量加载。

    其细节取决于问题中未包含的细节，完整的解决方案超出了此处的答案。尽管如此，过去对我个人来说效果很好的方法的大纲是：

    • bcp每个最终分区使用一个文件提取现有数据
    • 删除现有表并创建新表
    • 使用并行最小日志批量加载加载新表

    每个分区的数据提取需要在(LoadDate, SeqNumber). 理想情况下，您会避免排序操作。如果您在 (LoadDate, SeqNumber) 上有一个现有的非聚集索引，如果您正确构建查询，则可以按正确的顺序提取数据而无需排序。

    一旦每个分区的数据被提取到单独的文件中（如果您的硬件可以做到这一点，这可以并行完成），然后可以删除源表，从而释放空间。然后创建一个新的分区堆或聚簇表，并使用预排序的数据批量加载，可能也是并行的。

    如果做得好，整个过程需要不超过 1 倍的数据大小，并在两个方向上实现尽可能快的数据传输速率，同时使用最少的日志。

    • 17

相关问题