主页 / dba / 问题 / 49719
Accepted
paulH
Asked: 2013-09-13 03:09:43 +0800 CST

提高 SQL Server 上索引重建的速度

  • 772

我将大量数据导入一个空数据库，在开始之前，我禁用了所有非唯一非聚集索引，看看是否可以提高导入性能。

现在我想重新启用索引，我想知道是否可以做些什么来优化它。

有超过 100 个表和近 2,000 个索引需要重建。数据库大小为 200GB。

我正在运行的脚本的关键部分是：

declare c_toggle_index cursor FORWARD_ONLY READ_ONLY for
    select  'alter index ' + QUOTENAME(i.name) + ' on ' + o.name + ' rebuild'
    from    sys.indexes as i
    Inner Join sys.objects o
    On o.object_id = i.object_id
    Where o.is_ms_shipped = 0
    And i.index_id >= 1
    and i.type > 1
    and i.is_disabled = 1

我考虑为alter index 语句设置ONLINE=OFF，但是当索引开始被禁用时，我不确定这个设置是否会产生任何影响。我还考虑设置 SORT_IN_TEMPDB = ON，但由于 tempdb 文件与数据库的 .mdf 文件位于同一驱动器上，因此我认为这样做也没有任何好处。

在运行重建脚本时，我注意到我有很多 CXPACKET 等待类型。我真的不明白为什么会这样，或者这是否是我应该寻求解决的问题。

最后一点可能相关：除了将数据导入数据库之外，我的整个服务器当前处于非活动状态。无需考虑或担心其他用户活动；我唯一关心的是在尽可能短的时间内将数据导入数据库。

index sql-server-2008-r2

1 个回答

  1. Best Answer

    在这种情况下实现最佳导入性能需要三件事：

    1. 最少记录的基表插入
    2. 最少记录的非聚集索引构建
    3. 避免物理读取

    最少的日志记录

    在没有非聚簇索引的情况下实现对空聚簇表的最少记录插入需要：

    1. 使用SIMPLE或BULK_LOGGED数据库恢复模型
    2. 指定表锁和有序输入（例如TABLOCK和ORDER提示）

    边注：

    如果启用了跟踪标志 610，还可以实现对具有非聚集索引的聚集表的最小记录插入。非聚集索引插入是否被最小记录取决于查询优化器选择的查询计划。

    如果查询计划对非聚集索引使用单独的迭代器， 并且迭代器的DMLRequestSort属性设置为true，那么只要满足前面提到的其他条件，就会最少记录非聚集索引插入。

    单独构建非聚集索引

    这样做的好处是：

    1. 可以在不启用 TF 610 的情况下对聚集索引插入进行最少记录
    2. CREATE INDEX如果恢复模型不是，则最少记录FULL

    避免物理读取

    理想情况下，要导入的数据将存储在单独的机器上，或者至少存储在与用于托管数据库的物理存储不同的物理存储上。

    数据库服务器应该有足够的内存来保存缓存中最大的基表，并有足够的剩余内存用于构建非聚集索引时所需的排序操作。

    一个好的模式是快速加载基表（最小日志记录聚集索引负载），然后为该表构建所有非聚集索引，同时它的数据页仍然被缓存。

    该问题概述了一个过程，首先加载基表，然后构建非聚集索引。游标定义不使用ORDER BY子句至少将同一表上的非聚集索引构建组合在一起。

    可能的结果是，不同表的数据页被重复读入缓存，然后被丢弃，因为非聚集索引是以不确定的顺序构建的。

    重复物理读取的成本完全支配了单独构建非聚集索引所获得的最小日志记录的好处。这解释了为什么您发现使用现有索引加载表更快（因为给定表的所有非聚集索引都在移动到下一个表之前得到维护）。

    概括

    应重新设计导入过程以一次批量加载一张表。这意味着在继续下一个之前加载表并构建所有非聚集索引。SQL Server 实例应该有足够的可用内存来容纳最大的表并同时执行最大的非聚集索引排序。

    您还可以在将数据加载到已经存在非聚集索引的表中之前尝试启用 TF 610。这通常不如以前的方法快，但它可能足够快。

    有关详细信息，请参阅以下内容：

    数据加载性能指南

    可以最少记录的操作

    • 10

相关问题