发布日期： 2004年11月10日

本页内容

	摘要
	简介
	了解大容量装载
	方案
	附录 A：测试环境
	附录 B：预测交叉点

摘要

增量大容量装载指的是向一个非空表中装载数据。增量大容量装载过程中的关键问题是，大容量装载前是否删除索引。这个问题的答案取决于多种因素。本文通过一个案例研究来回答上述问题，即在 Microsoft Windows 2003 Server® 上运行 Microsoft® SQL Server™ 2000 的代表性决策支持系统 (DSS) 中使用 BULK INSERT 语句进行增量大容量装载。您应当将本文中提供的结果视为建议，而不应当将它们视为绝对正确的答案，因为您获得的结果取决于您的硬件（例如，CPU 数、I/O 和网络带宽）、表中的数据分布以及 BULK INSERT 语句支持的数据文件和参数。所有的测试都是在一个 Hewlett-Packard 硬件系统上进行的（有关测试环境的详细信息，请参见附录 A“测试环境”）。本文针对以下方案提供了一些一般性建议：

•	目标表没有索引： • 并发执行多个大容量装载命令。 • 必须将数据库设置为“大容量日志记录的”恢复模式。 • 使用 TABLOCK 参数来最大限度地减少锁定开销并在单个批处理中装载各个数据文件。 注意   针对以下方案提供的建议适用于在本文介绍的案例研究中进行了测试的负载类型。这些建议为其他类型的负载提供了一个良好的起点。
•	目标表有一个聚集索引： • 在保留聚集索引的情况下进行大容量装载，以便获得较好的装载性能和数据可用性。 • 使用最佳批处理大小来执行多个并发的大容量装载命令，以便最大限度地减少阻塞。最佳批处理大小取决于现有数据和增量数据的数据大小及其分布。
•	目标表有单个非聚集索引： • 在增量数据与表中现有数据大小相当时，保留非聚集索引进行大容量装载。 • 使用最佳批处理大小来执行多个并发的大容量装载命令，以便最大限度地减少阻塞。最佳批处理大小取决于现有数据和增量数据的数据大小及其分布。 • 在增量数据多于表中现有数据的情况下，进行大容量装载前先删除非聚集索引。之后，您需要重新创建非聚集索引。
•	目标表有一个聚集索引和多个非聚集索引： • 与表中的现有数据相比，如果增量数据只是很小一部分，则保留所有索引并使用一个大容量装载命令和最佳批处理大小（通过测试来确定）来装载数据以便获得最高的装载速度。 • 与表中现有数据相比，如果增量数据不止是一小部分，则删除所有的非聚集索引（但保留聚集索引），然后进行大容量装载。在进行大容量装载之后，您需要重新创建这些非聚集索引。

除极少数例外情况外，在使用 bcp 实用工具和数据转换服务 (DTS) 进行大容量数据装载时，这些建议都是适用的。本文对例外情况作了说明。

简介

在典型的 DSS 环境中，数据是定期地从外部源（例如，在线事务处理 [OLTP] 系统）进行装载的。为获得最佳查询性能，除了一个聚集索引外，表通常还有多个非聚集索引。与向一个没有索引的表中进行装载相比，索引会使装载变慢，因为每个 Insert 操作都需要对所有索引进行更新。本文推荐了用于增量大容量装载的最佳做法，并使用四种不同方案中的测试数据来解释这些做法的基本原理。这些建议主要适用于获得最大的大容量装载吞吐量。大容量装载在 OLTP 负载中也很常见；它并非 DSS 负载所独有的。本文提供的建议同样适用于 OLTP 负载。

了解大容量装载

有多种因素会影响大容量装载操作的性能（例如，BULK INSERT 语句和 bcp 实用工具）。下面几节介绍了这些因素，并建议了可用来提高性能的方法。这里介绍的参数适用于 BULK INSERT 语句。其他大容量装载方法中也提供了类似的参数。

大容量装载方法

SQL Server 支持三种大容量装载方法：

•	Bulk Insert 任务 — Transact-SQL 命令
•	bcp 实用工具
•	DTS

Transact-SQL BULK INSERT 语句与 SQL Server 一起在进程内执行，它们共享相同的内存地址空间。由于数据文件是由 SQL Server 进程打开的，因此，避免了跨进程的数据复制。

BCP 和 DTS 可执行文件都是在进程外运行的，因此必须进行进程间数据复制和参数封送处理，才能在不同的进程内存空间之间移动数据。进程间数据封送处理是将方法调用参数转换为字节流的过程，它会显著地增加 CPU 开销。通过使用 Bulk Insert，您可以跳过这些步骤并直接转到 OLE-DB。但是，与 Bulk Insert 不同，BCP 在客户端进程中分析数据并将数据转换为本机存储格式。如果 SQL Server 运行在单处理器计算机上，则 BCP 可以比 Bulk Insert 提供更好的性能，因为，在执行 BULK INSERT 时，SQL Server 进程还用于执行分析和数据转换操作。根据处理和复制的情况，与 Bulk Insert 相比，使用 BCP 可以使大容量数据装载性能提高 20％，但也可能会带来高达 100％ 的性能降低（例如，大容量装载 LOB 数据时）。

对于此案例研究，我们选择使用 Bulk Insert 命令来执行大容量装载操作。

数据库的恢复模式

在某些条件下，大容量装载操作能够执行优化的日志记录（只记录日志页分配，而不记录实际数据或索引行）。如果数据库被配置为“完全恢复”模式，则大容量操作和排序操作的专用日志记录处于未启用状态。如有可能，在大容量装载操作期间，您应当将数据库的恢复模式设置为“大容量日志记录的恢复”（首选）或“简单恢复”。

注意   设置数据库恢复模式只是实现优化的大容量日志记录的条件之一，这种优化的实现还取决于下文中讨论的其他一些因素。

目标表的架构

目标表的架构影响大容量装载操作、优化的日志记录和并发的大容量装载的查询计划，而最终会影响大容量装载操作的吞吐量。下面几节讲解目标表架构中影响查询计划的元素。

索引

大多数表都有索引；大容量装载操作的目标表也不例外。但是，目标表上索引的存在会影响大容量装载的性能。关于如何处理目标表的索引，您有两种选择：删除一个或多个索引，进行大容量数据装载，然后再重新创建被删除的索引；或者，不删除任何索引。针对目标表，请考虑以下情况：

•	没有索引   如果目标表没有索引且指定了 TABLOCK，则可以并发执行多个大容量装载命令。这些并发执行的大容量装载在表级别使用一个大容量更新 (BU) 锁，该锁在插入数据时不会阻塞其他会话。如果在没有索引的情况下使用 TABLOCK，则也可以使用大容量日志记录的优化。表是空的还是满的都没有关系。这是最快的大容量数据装载方法。不过，BU 锁会与常规的共享锁和排它锁发生冲突。 如果没有指定 TABLOCK，您仍然可以并发地执行多个大容量装载命令，不过，这些命令都使用常规锁定（像 Transact-SQL Insert 使用的锁定一样），并且无法使用大容量日志记录优化。
•	有单个非聚集索引或聚集索引   如果目标表有一个索引，则在指定了 TABLOCK 的情况下，无法使用 BU 锁进行并发大容量装载。 注意   不指定 TABLOCK 时，您仍然可以执行并发大容量装载（就像在“没有索引”时所讨论的一样），在执行时会使用常规锁定。不过，并发的大容量装载会话可能会发生阻塞。在这种情况下，只有从一个空的表开始并且用单个批处理执行大容量装载，才能使用大容量日志记录的优化。 注意   在这种情况下，如果您用多个批处理来装载数据，则从第二个批处理开始，无法使用大容量日志记录的优化；因为在第一个成功完成的批处理后，表变为“非空”。 在一个具有聚集索引的表上使用的典型查询计划是 （数据文件扫描）-->（按聚集键进行排序）-->（插入到聚集索引中）。 如果已通过指定 ORDER 提示对数据文件中的数据按聚集索引键列进行了排序，则您可以省略排序步骤。 类似地，在具有单个非聚集索引的表上使用的典型查询计划是 （数据文件扫描）-->（将数据插入表中）-->（按非聚集键进行排序）-->（插入到非聚集索引中）。 注意   即使输入数据是已排序的非聚集键列，排序步骤也不可以省略。只有向基表（具有聚集索引）插入数据时，ORDER 提示才适用。在所有其他情况下，它都会被忽略。
•	有聚集索引和多个非聚集索引   如果目标表具有一个聚集索引和多个非聚集索引，则在指定了 TABLOCK 的情况下，无法使用 BU 锁进行并发大容量装载。 注意   不指定 TABLOCK 时，您仍然可以执行并发大容量装载（就像在“没有索引”时所讨论的一样），在执行时会使用常规锁定。不过，并发的大容量装载会话可能会发生阻塞。在这种情况下，只有从一个空的表开始并且用单个批处理执行大容量装载，才能使用大容量日志记录的优化。 注意   在这种情况下，如果您用多个批处理来装载数据，则从第二个批处理开始，无法使用大容量日志记录的优化；因为在第一个成功完成的批处理后，表变为“非空”。 这种情况下的典型查询计划较为复杂，但是初始部分与“有单个非聚集索引或聚集索引”的情况中一样。                                    --> 后台处理 --> 按非聚集键进行排序 --> 插入到非聚集索引 1 中 （早先的计划）-->|                  .......                                    --> 后台处理 --> 按非聚集键进行排序 --> 插入到非聚集索引 2 中 在创建聚集索引后，创建非聚集索引时所需的所有属性均已进行了后台处理。这些属性包括非聚集索引的所有键列和聚集键列。聚集索引键列用于从非聚集索引叶级页指向数据行。在上述大容量装载查询计划中，装载到各个非聚集索引中的数据都是并行插入的。

如果目标表是空的，则在大容量装载完成后创建索引很有意义，这样做有两个理由：首先，您可以使用 BU 锁和优化的大容量日志记录来执行并发大容量装载。其次，您可以并行执行各个索引创建操作。

注意   在本文中，并行指的是多个线程执行单个命令。例如，多个线程可以执行单个 Transact-SQL CREATE INDEX 语句。在这种情况下，BULK INSERT 语句不能并行执行。您必须调用多个 BULK INSERT 语句来并行装载数据。

约束

对于插入的每一行，都要对其进行约束检查。如果可以的话，建议您禁用约束检查。但是，唯一可以禁用的约束是检查约束。您不能禁用唯一约束、主键/外键约束和 NOT NULL 约束。重新启用“检查约束”选项时，SQL Server 必须检查整个表以重新使约束生效。因此，建议您不要在增量大容量装载期间禁用约束，因为重新使约束对整个表生效所需的开销超过了将约束应用于增量数据所需的开销。

如果输入数据中包含违反约束的行，这时您可能需要禁用约束。禁用约束后，您可以装载该数据，然后可以使用 Transact-SQL 语句清除该数据。

触发器

如果为目标表上的插入操作定义了触发器，则在每一个批处理完成后都将引发这些触发器。如果可以的话，请在大容量装载操作期间禁止执行触发器。

TABLOCK

BULK INSERT 语句接受 TABLOCK 提示，该提示允许用户指定使用的锁定行为。

向堆中进行大容量装载时，TABLOCK 指定在整个大容量装载过程中使用大容量更新 (BU) 表级锁。因为这减少了表上的锁争用情况，所以能够改善大容量装载操作的性能。此外，当数据库的恢复模式被配置为“大容量日志记录的”或“简单”时，TABLOCK 还是大容量日志记录的一个必需参数。

向具有一个或多个索引的表中进行大容量装载时，TABLOCK 强制大容量装载操作获取 X 表锁，以禁止并发大容量装载。

如果您没有指定 TABLOCK，则大容量装载不会获取表锁，而会获取行锁或页锁。不过，根据您的批处理大小和目标表上的并发活动，行锁或页锁可能会升级为 X 锁。如果锁的数目超过了 5,000，SQL Server 就会尝试将行锁或页锁升级为表锁。

表 1 描述了目标表非空时（意味着操作是增量大容量装载）的日志记录和锁定行为。

表 1   非空目标表的日志记录和锁定行为

表架构	TABLOCK	日志记录类型	并发的大容量装载
堆（包括 LOB）	是	大容量日志记录的	是（BU 锁）
堆（包括 LOB）	否	完全日志记录的	是（行或页的常规 X 锁）
具有索引的表	是	完全日志记录的	否
具有索引的表	否	完全日志记录的	是（行或页的常规 X 锁）

注意   在表架构中，堆是指没有任何索引（聚集和非聚集均无）的表。允许使用 LOB，尽管出于分配目的在内部将 LOB 表示为索引。

注意   并发的大容量装载是指各自具有自己的数据流的多个大容量装载命令。如果指定了 TABLOCK，大容量装载命令便等候获取表锁。但是，如果没有指定 TABLOCK，则各个大容量装载命令将分别获取一个行锁或页锁（假定没有锁升级），具体是哪种锁取决于表上的锁定精确度设置。

并发的大容量装载

大容量装载通常与 CPU 相关。因此，如果 SQL Server 运行在一台多处理器计算机上，当并发调用多个大容量装载命令时，您会获得较高的大容量装载吞吐量（假定没有阻塞）。另外，在大容量装载期间，如果您指定了 TABLOCK，则可以使用一个特殊的表级锁（BU 锁）。BU 锁在作用上就是一个排它锁，因为它阻止通过常规 Transact-SQL 语句对表进行访问，但是不阻止并发的大容量装载线程。BU 锁仅在堆（没有索引的表）上可用，它可以为堆提供最佳并发装载性能。表 1 描述了并发大容量装载的条件。

注意   对于并发大容量装载，不要求数据库的恢复模式必须是“简单”或“大容量日志记录的”。

要并发执行大容量装载，您需要发出多个大容量装载命令，并且这些命令分别读取各自的数据文件。多个大容量装载命令不能读取同一个数据文件。

输入数据的排序

如果输入数据按聚集键列进行了排序，那么，就像上文的“索引”小节中“有单个非聚集索引或聚集索引”的查询计划中所说的那样，您可以省略排序步骤。

BATCHSIZE

BATCHSIZE 的默认设置是一个 BULK INSERT 语句的输入文件的长度。通过使用非默认值，可以使大容量装载作为一个事务，也可以将其分割成多个事务。每个事务可插入的行数为 BATCHSIZE。较小的批处理有下列好处：

•	有索引时，较小的批处理减少了排序所需的内存。在进行并发大容量装载期间，较小的批处理还可以减少阻塞，具体情况取决于输入数据文件的数据模式及其分布。
•	发生故障或错误时，您只需要从最后一个不成功的批处理开始重新装载数据。例如，如果您花费了 3 个小时进行大容量装载，但在接近结束时出现了故障，您只需要重新装载最后一个批处理，而不需要重新开始整个大容量装载。

数据模式

数据文件中的数据可以是字符格式，也可以是本机格式（即二进制表示形式）。装载以字符格式存储的数据需要分析数据并根据目标表中的列类型将其转换为本机存储格式，这必须使用服务器资源。字符格式是最常见的数据格式；但是，装载本机格式的数据效率会更高。

输入数据的大小

在有索引的情况下，输入数据的大小非常重要。创建一个聚集索引时，SQL Server 对数据进行排序，并增加它的大小。输入数据的大小越大，所需的内存就越多（排序可能需要运行多遍）。对于非聚集索引，情况类似，不同的是要排序的数据大小取决于索引键的大小和行数。您可以通过指定一个较小的批处理大小来控制数据的大小。此外，根据您要装载的数据的大小，关于目标表的统计信息可能会有很大变化。建议您在大容量装载操作后重新创建统计信息；特别是在您禁用了统计信息的自动重新生成的情况下，更应该进行重新创建。

方案

在 DSS 负载中，考虑那些需要使用具有一个或多个索引的目标表的增量大容量装载的方案是有用的。考虑一个需要向没有索引的表中进行大容量装载的方案也有益处，因为在某些情况下（处于交叉点时），可以删除表上的所有索引，执行增量大容量装载，然后再重新创建所有索引，这样做比保留索引来执行增量大容量装载更好。

交叉点被定义为增量数据与目标表中的现有数据的一个比率 — 低于该比率时，您可获得较好的增量大容量装载性能而不必删除索引。简而言之，如果您知道了某个特定目标表的交叉点，您就可以决定您在进行大容量数据装载前是否应该删除索引。

不过，问题的关键在于定位交叉点。交叉点取决于多个变量，例如，表架构、硬件资源和数据分布。但是，对于一个具有一致的资源级别和压力级别的系统来说，确定交叉点对定义合理的大容量装载策略可能会有帮助。

注意   如果任何相关的因素发生了变化，便需要重新计算交叉点。本文为各种方案分别计算了交叉点。我们极力建议您将本文中介绍的最佳做法视为一般性建议；硬件和软件不同，您获得的体验也可能不同。

本文中介绍的方案的测试基于附录 A“测试环境”中定义的表。大容量装载的数据是以字符模式存储的。装载字符模式的数据时有 CPU 开销，因为在装载前必须对其进行分析，并将其转换为目标表列类型。如果您装载本机模式数据，则可以避免这种开销。

没有索引时的增量大容量装载

摘要

此方案的测试结果间接表明，如果在使用并发的大容量装载命令时采用默认批处理大小，并使用 TABLOCK 提示，将可以获得最高的装载速度。为此，您可以使启动的 Bulk Insert 命令数与 CPU 数目相同，同时使用 TABLOCK 提示。数据库应设置为“大容量日志记录的”恢复模式。

大容量装载命令

八个并发的大容量装载命令都使用下面的命令来进行大容量数据装载：

BULK INSERT fact_table <data-file> WITH (TABLOCK)

参数：

•	TABLOCK：并发执行多个大容量装载命令。此查询获取一个 BU 锁。
•	BATCHSIZE：不指定。各个大容量装载命令都通过同一个批处理装载数据。

结果

装载吞吐量随处理器数目的增加呈线性上升。没有遇到 I/O 瓶颈。不过，这是因为系统上没有其他负载。在大容量装载期间，CPU 使用率接近 100%。图 1 显示了八个并发的大容量装载命令共同实现的吞吐量。

建议

下面是帮助您获得增量大容量装载最佳性能的一些建议：

•	使用批处理大小的默认设置。它等于数据文件的大小，可以减少打开和关闭文件的开销。
•	使用 TABLOCK 提示来改善性能。TABLOCK 能够改善性能，原因如下： • 启用大容量日志记录。 • 减少锁定开销，因为没有行级锁。 • 使用 BU 锁模式执行多个并发的大容量装载命令而不会遇到阻塞。

仅有一个聚集索引时的增量大容量装载

摘要

增量大容量装载期间最好保留聚集索引。此方案中使用了以下两个计划：

•	计划 A   按以下步骤执行增量大容量装载：删除聚集索引，将数据装载到目标表，然后重新创建该聚集索引。
•	计划 B   在不删除聚集索引的情况下执行增量大容量装载。

测试结果间接表明，如果增量数据不比原始表中的数据多，装载数据时还是保留聚集索引为好。

大容量装载命令

计划 A

按顺序执行了以下命令：

1.	Drop clustered index fact_table.ci。（使用单个线程来执行。无法并行执行。）
2.	Bulk Insert fact_table <data-file>。（使用八个并发的大容量装载命令来执行。）
3.	create clustered index ci on fact_table(order_id)。（使用八路并行来执行。）

Bulk Insert 的参数：

•	TABLOCK：并发执行大容量装载。它获取一个 BU 锁。
•	BATCHSIZE：不指定。各个大容量装载命令都通过同一个批处理装载数据。

计划 B

Bulk Insert fact_table <data-file>。（批处理大小设置为 5,000 行。）（不删除聚集索引，使用八个并发的大容量装载命令来执行。）

Bulk Insert 的参数：

•	TABLOCK：不指定。如果指定，将获得一个 X 表锁。
•	BATCHSIZE：进行了指定，以减少锁定争用。

结果

对于计划 A，对删除和创建聚集索引进行了性能测定。删除聚集索引后，增量装载实质上是向堆中装载数据。有关没有索引时进行大容量装载的详细信息，请参见上文中的“没有索引时的增量大容量装载”。

如图 2 所示，在创建聚集索引时，结果显示可伸缩性大致呈线性。创建聚集索引时使用了八路并行。因为内存中放不下索引，所以排序是通过多遍运行来执行的。

注意   索引创建操作的实际并行度由下列设置决定：

•	操作系统中运行的处理器的数目，通过 boot.ini 选项 numproc 来设置。
•	SQL Server CPU 关系，通过 sp_configure 命令选项来设置。
•	SQL Server 的最大并行度设置，通过 sp_configure 命令选项来设置。
•	索引创建开始时的可用系统资源，通过动态方式来确定。如果系统繁忙，并行度将缩减回一个较小的数字。索引创建开始后，并行度将不会改变。 注意   创建聚集索引的时间取决于多种因素，例如，I/O 带宽、CPU 数目、可用内存、表的大小、聚集键的大小、数据库的恢复模式以及系统上的当前负载。在本文介绍的测试中，索引创建与 CPU 相关。

删除一个聚集索引的代价高昂，因为删除聚集索引后，数据库引擎必须为每一个数据页更新可用空间。这需要对表进行完全扫描。这些测试表明，删除一个聚集索引比创建相同的聚集索引代价更高（就时间而言）。这是因为，通过使用并行查询计划，索引的创建可以利用八个处理器，而聚集索引的删除却是单线程的。

对于计划 B，测试表明，向只有一个聚集索引的表中进行装载时，装载速度保持不变（图 3）。表的大小从 5 GB 变为 1 TB，但装载吞吐量在整个装载过程中保持不变。因为执行了八个并发的大容量装载命令，所以没有锁升级。

测试结果显示的交叉点是 350%（也就是说，如果增量数据的大小是表中原始数据的 3.5 倍以上，就值得执行计划 A）。有关测试中使用的数学模型的详细信息，请参见附录 B，“预测交叉点”。不删除索引的另一个好处是：在大容量装载期间，可以对表进行查询。

注意   在本文介绍的测试中，现有数据和增量数据都是按聚集键列均匀分布的。如果数据分布不均匀，则可能会导致严重的争用。您可以通过减小批处理的大小或并发的大容量装载命令的数目来最大限度地减少争用。

图 4 显示了增量数据大小增加时的交叉点。在此测试中，表的初始大小是 91 GB。这一初始大小应该不会影响交叉点，因为交叉点基于的是表的初始大小的百分比。对于每一个标绘点，装载时间都包括执行计划的所有步骤所花费的时间。例如，使用计划 A 将增加的 200％ 的数据装载到此表中共花费了 26,663 秒，其中包括删除聚集索引所用的 10,391 秒，将增加的 182 GB 的数据装载到没有索引的表中所用的 4,203 秒及在有 273 GB 数据的表上重新创建聚集索引所用的 12,069 秒。创建聚集索引比删除它花费的时间更长，因为在完成数据的增量装载后，表的大小增加了 200%。

建议

下面是帮助您获得增量大容量装载最佳性能的一些建议。这些建议适用于在本文介绍的案例研究中进行了测试的负载类型。这些建议也为其他类型的负载提供了一个良好的起点。

•	如果增量装载的数据大小不是明显大于目标表的当前大小，则使用计划 B 可能会获得较好的大容量装载性能。
•	检查目标表和增量数据中的聚集键分布。如果在聚集键值中没有重叠，则您在并行进行大容量数据装载时遇到的阻塞最小或根本没有阻塞。通常，在一个数据仓库方案中，增量数据集中在聚集键的尾部，这会导致其与并发的大容量装载命令间发生更多争用。 表 2 说明了装载吞吐量如何随并发的大容量装载命令数目的增加而增长。这些测试表明，随着处理器数目的增加，大容量装载吞吐量并不呈线性上升。这是由独立的大容量装载命令之间的锁或闩锁争用以及较小的批处理大小或事务（包括关闭和打开输入数据文件）的开销导致的。当从单个大容量装载命令增加到八个并发的大容量装载命令时，装载吞吐量大约会增长一倍。不过，总的 CPU 使用率从 12%（这就是说，如果使用单个 CPU，其使用率将为 100%）增长到了 95%，即增长到了原来的八倍。我们根据实现最佳性能所需的并发大容量装载命令的数目选择了不同的批处理大小。例如，对于单个大容量装载命令，我们使用了单个批处理；而对于八个并发的大容量装载命令，我们使用了一个 5,000 行的批处理大小。 表 2   装载吞吐量随并发的大容量装载命令数目的增加而增长 大容量装载命令的数目 使用最佳批处理大小时的装载吞吐量（MB/秒） CPU 使用率百分比（所有 8 个 CPU） 1 4.48 12 2 6.25 24 8 9.03 95
•	如果存在数据重叠，可使用一个较小的批处理大小来最大限度地减少争用。大容量装载中的每个批处理都是作为一个单独的事务运行的。因此，较小的批处理大小将限制被锁定的行数，从而最大限度地减少阻塞。另外，较小的批处理大小还允许按照将要插入到目标表中的数据的聚集键列进行内存中排序。

有一个非聚集索引时的增量大容量装载

摘要

在此方案中，我们对下面的计划进行了测试：

•	计划 A   按以下步骤执行增量大容量装载：删除非聚集索引，将数据装载到目标表，然后重新创建该非聚集索引。
•	计划 B   在不删除非聚集索引的情况下执行增量大容量装载。

在此方案中，如果增量数据的大小与表的初始大小相当，则在增量大容量装载期间还是保留非聚集索引为好。如果增量数据的大小明显大于表的初始大小，最好在装载前先删除索引，装载后再重新创建。

大容量装载命令

计划 A

按顺序执行了以下命令：

1.	Drop nonclustered index fact_table.nci_1。（使用单个线程来执行。）
2.	Bulk Insert fact_table <data-file> with TABLOCK。（使用八个并发的大容量装载命令来执行。）
3.	Create nonclustered index nci_1 on fact_table(order_id)。（使用八路并行来执行。）

参数：

•	TABLOCK：并发执行大容量装载命令。它获取一个 BU 锁。
•	BATCHSIZE：不指定。各个大容量装载命令都通过同一个批处理装载数据。

计划 B

Bulk Insert fact_table <data-file> with batchsize=5000。（使用八个并发的大容量装载命令来执行。）

参数：

•	TABLOCK：不指定。如果指定，将获得一个 X 表锁。
•	BATCHSIZE：进行了指定，以减少锁定争用。

结果

对于计划 A，SQL Server 2000 中的非聚集索引的删除是瞬间完成的。删除非聚集索引后，增量装载实质上是向堆中装载数据。请参考前面的方案。有关向堆中进行增量大容量装载的性能的详细信息，请参见上文中的“没有索引时的增量大容量装载”。

假定索引项较小，B 树的结构层次较浅，该函数便接近线性。图 5 显示了当表中数据的大小增加时，创建非聚集索引所需的时间随之呈近似线性增长。

注意   虽然在创建非聚集索引的过程中需要进行排序的数据大约是创建聚集索引时的六分之一，但是创建非聚集索引所需的时间只比创建聚集索引所需的时间少 20%。这是因为，索引创建与 CPU 相关。如果是 I/O 相关的，性能差异就会更大。

对于计划 B，在只保留一个非聚集索引时，装载的速度是不变的。如图 6 所示，对于只有一个非聚集索引的同一个表来说，在整个装载过程中，虽然表的大小（此处仅指数据，不包括索引）从 5 GB 变为 1 TB，但装载吞吐量保持不变。

图 7 显示了增量数据大小增加时的交叉点。此测试中的表的交叉点是 98%。这个数字与使用附录 B 中介绍的一个公式所预测的结果很接近。对于该测试系统，可以作以下结论：当增加的数据少于现有数据时，在保留非聚集索引的情况下装载增量数据的效率会更高。否则，删除索引后再进行装载的效率更高。

建议

下面的建议适用于在本文介绍的案例研究中进行了测试的负载类型。这些建议为其他类型的负载提供了一个良好的起点。有助于实现最佳大容量装载速度的建议是：

•	如果增量数据与目标表大小相近，则在增量大容量装载期间保留非聚集索引。否则，最好在装载前先删除索引，装载后再重新创建。
•	如果存在数据重叠，可使用一个较小的批处理大小来最大限度地减少争用。大容量装载中的每个批处理都作为一个单独的事务运行，因此，较小的批处理大小将限制被锁定的行数，从而最大限度地减少阻塞。另外，较小的批处理大小还允许您按照将要插入到目标表中的数据的非聚集键列进行内存中排序。
•	与我们在具有一个聚集索引的表中看到的类似，如果您不删除索引而进行大容量装载，那么，由于存在锁或闩锁争用，大容量装载吞吐量不会随并发装载命令的增加而呈线性增长。从表 3 中可以看出，使用八个并发的大容量装载命令时，大容量装载吞吐量仅增长了 2.5 倍。因此，除非对 CPU 的请求不存在争用，否则通过运行并发的大容量装载命令获得的好处很有限。在本文介绍的测试中，为了获得最佳性能，我们根据并发的大容量装载命令的数目选择了不同的批处理大小。例如，对于单个大容量装载命令，我们使用了单个批处理，而对于八个并发的大容量装载命令，我们使用了一个 5,000 行的批处理大小。 表 3   大容量装载吞吐量的增长 并发的大容量装载命令的数目 使用最佳批处理大小时的装载吞吐量（MB/秒） CPU 使用率百分比（所有 8 个 CPU） 1 4.56 12 2 7.02 24 8 10.11 94

有一个聚集索引和多个非聚集索引时的增量大容量装载

摘要

在此方案中，我们对下面的计划进行了测试：

•	计划 A   删除所有的索引（包括聚集索引），装载数据，然后重新创建所有索引。
•	计划 B   删除所有的非聚集索引但保留现有的聚集索引，装载数据，然后重新创建所有非聚集索引。保留聚集索引而删除非聚集索引的决定是基于以下理由做出的： • 删除聚集索引的代价很高。删除聚集索引后，SQL Server 需要遍历所有的数据页来为各个已分配的页更新可用空间信息。 • 创建聚集索引的代价相对较高，具体开销取决于数据行的大小。
•	计划 C   在不删除任何索引的情况下进行增量大容量装载。

测试结果表明，除非增量数据相对于现有数据来说很少，否则使用计划 B 比较好。

大容量装载命令

计划 A

按顺序执行了以下命令：

1.	删除所有非聚集索引。
2.	删除聚集索引（通过单个线程来执行）。
3.	执行 Bulk Insert fact_table <data-file>（使用八个并发的大容量装载命令来执行）。
4.	创建聚集索引。
5.	创建所有非聚集索引。

向堆中进行装载时 Bulk Insert 使用的参数：

•	TABLOCK：并发执行大容量装载命令。它获取一个 BU 锁。
•	BATCHSIZE：不指定。各个大容量装载命令都通过同一个批处理装载数据。

计划 B

按顺序执行了以下命令：

1.	删除所有非聚集索引。
2.	使用一个大小为 5,000 的批处理来执行 Bulk Insert fact_table <data-file>。（使用八个并发的大容量装载命令来执行。）
3.	创建所有非聚集索引。

Bulk Insert 的参数：

•	TABLOCK：不指定。如果指定，将获得一个 X 表锁。
•	BATCHSIZE：进行了指定，以减少锁定争用。

计划 C

在不删除任何索引的情况下执行了命令。

Bulk Insert fact_table <data-file>。（使用单个大容量装载命令来执行。）

Bulk Insert 的参数：

•	TABLOCK：不指定。如果指定，将获得一个 X 表锁。
•	BATCHSIZE：使用了一个 20,000,000 行的批处理大小。

结果

对于计划 A，所花费的时间是以下操作所需时间的总和：删除所有非聚集索引和聚集索引，将数据大容量装载到堆中，然后重新创建聚集索引和非聚集索引。删除所有索引后，增量装载实质上是向堆中进行大容量装载。请参考前面的方案。有关向堆中进行增量大容量装载期间的性能的详细信息，请参见上文中的图 1。有关删除和重新创建索引的性能的详细信息，请参见上文中的图 2 和图 5。

对于计划 B，所花费的时间是以下操作所需时间的总和：删除所有非聚集索引，将数据大容量装载到具有聚集索引的表中，然后重新创建所有非聚集索引。有关向具有聚集索引的表中进行增量大容量装载的性能的详细信息，请参见上文中的“……”。有关删除和重新创建非聚集索引的性能的详细信息，请参见上文中的图 2 和图 5。

在计划 C 中，与向堆中进行大容量装载的吞吐量相比，在保留所有索引的情况下进行大容量装载的吞吐量明显较低。一般来说，当索引的数目增加时，装载吞吐量会因锁定争用的存在而显著降低。在本文介绍的测试中，当现有数据的大小增加时，大容量装载吞吐量略有下降，如图 8 所示。

测试结果表明，计划 B 比计划 A 效率更高，如图 9 所示。对于计划 C 和计划 B，使用附录 B 中的公式为具有一个聚集索引和七个非聚集索引的表计算出的交叉点是 1%。换句话说，当增加的数据不足现有数据的 1% 时，如果在装载增加的数据时保留所有索引，效率会更高。否则，效率更高的方法将是：删除所有非聚集索引但保留聚集索引，装载数据，然后重新创建所有非聚集索引。计划 B 的另外一个好处是，在装载期间数据和聚集索引都仍然可用。

建议

下面的建议适用于在本文介绍的案例研究中进行了测试的负载类型。这些建议为其他类型的负载提供了一个良好的起点。

•	为优化查询，DSS 中的表通常包含多个索引，包括非聚集索引、聚集索引或者这两类索引兼用。随着索引数目的增加，使用并发的大容量装载命令时，阻塞便成了一个较严重的性能问题。因此，我们建议您对具有多个索引的表执行单个大容量装载命令。
•	如果增量数据不足表的初始大小的 1%，请考虑在增量大容量装载期间保留所有的索引；如果不是这种情况，则使用计划 B 较好。
•	在保留索引的情况下进行增量大容量装载期间，除非已按聚集键对数据进行了排序并且使用了排序提示，否则会在进行插入操作之前对数据进行排序，以改善逐行插入过程中的缓存命中率。通过将批处理数据放置到物理内存中带来的性能提高超过了由打开和关闭文件的开销所造成的性能损失。图 10 说明了向一个具有一个聚集索引和七个非聚集索引的表中装载数据时批处理大小对平均装载性能（包括后台处理、排序和插入三个阶段）的影响。较大的批处理大小会导致数据的后台处理和排序从内存溢出到 tempdb，进而要求为 tempdb 提供大量空间，为 tempdb 中的并发排序提供足够数量的磁盘轴。就典型 DSS 的 tempdb 大小和磁盘带宽而言，这只是一个小问题。物理内存大则性能高，因为较大的物理内存可以容纳较多的数据，从而可以减少因打开和关闭文件而产生的开销。 图 10   向具有一个聚集索引和七个非聚集索引的表中进行增量大容量装载

附录 A：测试环境

主数据库服务器：Hewlett Packard Proliant 8500

•	CPU：8 个 733 MHz 处理器
•	RAM：8 GB
•	主机总线适配器 (HBA)：4 个 Emulex 952 HBA

存储器：Hewlett Packard 企业虚拟阵列 (EVA)

•	存储器机柜：2 个
•	控制器：4 个
•	磁盘：168 个具有 72 GB 光纤通道和 10 KB RPM 的磁盘
•	84 个磁盘：每个具有 36 GB 光纤通道和 10 KB RPM
•	存储区域网络 (SAN) 交换机：Brocade Silkworm 3800
•	SAN 管理设备：Proliant DL380

软件

•	Windows 2000 Advanced Server Service Pack 3
•	Microsoft SQL Server 2000 Enterprise Edition Service Pack 3

SQL Server 设置

•	启用了 AWE
•	最大服务器内存设置为 7 GB，以使 SQL Server 最多可使用 7 GB 的内存

数据和数据库架构

使用了一个具有代表性的 DSS 数据库架构，并且使用了事实数据表作为大容量装载的目标表。表中的数据类型包括 int、money、datetime、char 和 varchar。下面是该表的架构：

•	表名：fact_tabe
•	列： Order_id   int Part_id      int Supplier_id   int Line_no      int Price      money Sale_price   money Special      money Tax      money Status01   char(1) Status02   char(1) Shipdate   datetime Commitdate   datetime Receivedate   datetime Directions   char (25) Comments   varchar (40) General      char(10)
•	聚集索引：    ci；   键：(shipdate)
•	非聚集索引：    nci_1；   键：(order_id)
•	非聚集索引：    nci_2；   键：(part_id, supplier_id)
•	非聚集索引：    nci_3；   键：(price)
•	非聚集索引：    nci_4；   键：(status01)
•	非聚集索引：    nci_5；   键：(commitdate)
•	非聚集索引：    nci_6；   键：(shipdate)
•	非聚集索引：    nci_7；   键：(comments)
•	八个包含未排序、非唯一数据的平面文件。

现有数据和增量数据都按索引键均匀分布。各平面文件（例如大容量装载线程）间以及现有数据与增量数据间的数据范围重叠比较多。

附录 B：预测交叉点

大容量装载的性能取决于多种因素，例如，表架构、目标表与增量数据中的数据分布、硬件配置和负载等。因此，不存在适合于所有情况的标准数字。不过，如果您能够计算出一个代表性的数据集和环境上的大容量装载性能，您就可以预测出一个相当准确的交叉点。本附录介绍如何使用具有代表性的数据来计算交叉点。

具有一个聚集索引的目标表

步骤 1：收集数据

在试验中，您需要收集以下数据：

•	没有索引的数据的单位大小装载时间（没有索引时的时间 T）：22 秒/GB。
•	有聚集索引的数据的单位大小装载时间（有索引时的时间 T）：112 秒/GB。
•	单位表大小的索引创建时间 (ac)：45 秒/GB。
•	单位表大小的索引删除时间 (ad)：114 秒/GB。

您可以使用较小的规模来执行这些基准试验，也可以从前面的装载中收集数据。

步骤 2：估计各个执行计划的时间

假定您需要将数量为 X 的数据装载到大小为 X₀ 的表中。

•	计划 A：在不保留索引的情况下进行装载   因为没有索引的表的装载速度保持不变，所以在不保留索引时装载数量为 X 的数据所需的时间等于：X * 没有索引时的时间 T。
•	计划 B：在保留索引的情况下进行装载   因为具有聚集索引的表的装载速度保持不变，所以保留索引时装载数量为 X 的数据所需的时间等于：X * 有索引时的时间 T。 因为删除表的聚集索引所需的时间随表中现有数据大小的增加而呈线性增长，所以删除大小为 X0 的表的索引所需的时间等于：ad X0。 因为创建表的聚集索引所需的时间随表中现有数据大小的增加而呈线性增长，所以创建大小为 X0 的表的索引所需的时间等于：ac (X0 + X)。 那么，计划 B 所需的总时间就是：X * T+ ad X0 + ac (X0 + X)。

步骤 3：预测装载计划的交叉点

在计划 A 与计划 B 之间的交叉点处，

X * 有索引时的时间 T = X * 没有索引时的时间 T + ad X₀ + ac (X₀ + X)

所以，X / X₀ = (ad + ac) /(有索引时的时间 T – 没有索引时的时间 T - ac)

如果 (有索引时的时间 T – 没有索引时的时间 T - ac) < 0，则不存在正值交叉点，这样在保留索引的情况下进行数据装载将始终是较好的做法。

使用本文介绍的测试中的数据计算交叉点：

X/ X₀ = (114 + 45)/(112 – 22 – 45) = 350%（约值）

您可在本文介绍的测试中找到该数字。因为表在删除和重新创建聚集索引期间是不可用的，而装载 350% 的增量数据的情况并不常见，所以在大多数情况下，装载数据时保留聚集索引是较好的做法。

具有一个非聚集索引的目标表

步骤 1：收集数据

在试验中，您需要收集以下数据：

•	没有索引的数据的单位大小装载时间（没有索引时的时间 T）：22 秒/GB。
•	有聚集索引的数据的单位大小装载时间（有索引时的时间 T）：99 秒/GB。
•	单位表大小的索引创建时间 (ac)：38 秒/GB。

您可以使用较小的规模来执行这些基准试验，也可以从前面的装载中收集数据。

步骤 2：估计各个执行计划的时间

假定您需要将数量为 X 的数据装载到大小为 X₀ 的表中。

•	计划 A：在不保留索引的情况下进行装载   因为没有索引的表的装载速度保持不变，所以不保留索引时装载数量为 X 的数据所需的时间等于：X * 没有索引时的时间 T。
•	计划 B：在保留索引的情况下进行装载   因为具有一个非聚集索引的表的装载速度保持不变，所以保留非聚集索引时装载数量为 X 的数据所需的时间等于：X * 有索引时的时间 T。 因为创建表的非聚集索引所需的时间随表中现有数据大小的增加而呈线性增长，所以创建大小为 X0 的表的索引所需时间等于：ac (X0 + X)。 因此，计划 A 的总时间为：X * 没有索引时的时间 T + ac (X0 + X)。在此例中，创建非聚集索引所需的时间被忽略，因为它是瞬时完成的。

步骤 3：预测装载计划的交叉点

在计划 A 与计划 B 之间的交叉点处，

X * 有索引时的时间 T = X * 没有索引时的时间 T + ac (X₀ + X)

所以，X / X₀ = ac /(有索引时的时间 T – 没有索引时的时间 T - ac)

如果 (有索引时的时间 T – 没有索引时的时间 T - ac) < 0，则不存在正值交叉点。在这种情况下，在保留索引的情况下进行数据装载始终是较好的做法。

使用本文介绍的测试中的数据计算交叉点：

X/X₀ = (38)/(99 – 22 – 38) = 38/39 = 100%（约值）

您可在本文介绍的测试中找到该数字。与现有的数据相比，如果增加的数据量比较大，则在不考虑删除索引所需要的时间的情况下，删除非聚集索引可能是较好的做法。

具有 1 个聚集索引和 7 个非聚集索引的目标表

步骤 1：收集数据

在试验中，您需要收集以下数据：

•	没有索引的数据的单位大小装载时间（没有索引时的时间 T）：22 秒/GB。
•	有全部索引的数据的单位大小装载时间（有全部索引时的时间 T）：35067 秒/GB。 注意   只使用了对大于 5 GB 的现有数据收集的速度。
•	只有聚集索引的数据的单位大小装载时间（有聚集索引时的时间 T）：112 秒/GB。
•	单位表大小的全部索引创建时间 (ac all)：324 秒/GB。
•	单位表大小的全部索引创建时间 (ac all nonclustered)：362 秒/GB。
•	单位表大小的聚集索引删除时间 (ad)：114 秒/GB。

您可以使用较小的规模来执行这些基准试验，也可以从前面的装载中收集数据。

步骤 2：估计各个执行计划的时间

假定您需要将数量为 X 的数据装载到大小为 X₀ 的表中：

•	计划 A：在不保留索引的情况下进行装载   因为没有索引的表的装载速度保持不变，所以不保留索引时装载数量为 X 的数据所需的时间等于：X * 没有索引时的时间 T。 因为删除表的聚集索引所需的时间随表中现有数据大小的增加而呈线性增长，所以删除大小为 X0 的表的索引所需的时间等于：ad X0。 因为创建表的非聚集或聚集索引所需的时间随表中现有数据大小的增加呈线性增长，所以创建一组非聚集索引和一个聚集索引所需的时间随表中现有数据大小的增加呈线性增长，那么，在大小为 X0 的表上创建索引所需的时间等于：ac all (X0 + X)。 因此，计划 A 的总时间等于：X * 没有索引时的时间 T + ad X0 + ac all (X0 + X)。
•	计划 B：只保留聚集索引进行装载   与计划 A 相似，计划 B 的总时间等于：X * 有聚集索引时的时间 T + ac all nonclustered (X0 + X)。
•	计划 C：在保留索引的情况下进行装载   保留所有索引装载数量为 X 的数据所需的时间等于：X * 有全部索引时的时间 T。

步骤 3：预测装载计划的交叉点

在计划 A 与计划 B 之间的交叉点处，

X * 有聚集索引时的时间 T + ac all nonclustered (X₀ + X) = X * 没有索引时的时间 T + ad X₀ + ac all (X₀ + X)。

X / X₀ = (ad + ac all) /(有聚集索引时的时间 T – 没有索引时的时间 T - ac all nonclustered)

在本文介绍的测试中，(有聚集索引时的时间 T – 没有索引时的时间 T - ac all nonclustered) < 0，因此在进行大容量装载时保留聚集索引始终比删除它为好，前面的测试也验证了这一点。

在计划 B 与计划 C 之间的交叉点处，

X * 有全部索引时的时间 T = X * 有聚集索引时的时间 T + ac all nonclustered (X₀ + X)

所以，X / X₀ = ac all nonclustered /(有全部索引时的时间 T – 有聚集索引时的时间 T - ac all)。

使用本文介绍的测试中的数据计算交叉点：

X/ X₀ = (362) / (35067 – 112 – 362) = 362/34593 = 1%（约值）

因此，如果增量数据比现有数据多 1%，则计划 B 较好。在本文介绍的测试中，该交叉点约等于 1%，测试也验证了这一点。除非与现有数据相比，需要装载的增量数据的数量较少，否则删除非聚集索引而保留聚集索引将是较好的做法。