主页 / dba / 问题 / 165966
Accepted
Alexei
Asked: 2017-03-03 05:53:00 +0800 CST

如何调查 BULK INSERT 语句的性能？

  • 772

我主要是使用实体框架 ORM 的 .NET 开发人员。但是，因为我不想在使用 ORM 时失败，所以我试图了解数据层（数据库）中发生的情况。基本上，在开发过程中，我启动分析器并检查代码的某些部分根据查询生成了什么。

如果我发现一些非常复杂的东西（ORM 甚至可以从相当简单的 LINQ 语句中生成糟糕的查询，如果没有仔细编写的话）和/或繁重的（持续时间、CPU、页面读取），我会将其放入 SSMS 并检查其执行计划。

它适用于我的数据库知识水平。但是， BULK INSERT 似乎是一种特殊的生物，因为它似乎不会产生 SHOWPLAN。

我将尝试说明一个非常简单的示例：

表定义

CREATE TABLE dbo.ImportingSystemFileLoadInfo
(
    ImportingSystemFileLoadInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ImportingSystemFileLoadInfo PRIMARY KEY CLUSTERED,
    EnvironmentId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo REFERENCES dbo.Environment,
    ImportingSystemId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo_ImportingSystem REFERENCES dbo.ImportingSystem,
    FileName NVARCHAR(64) NOT NULL,
FileImportTime DATETIME2 NOT NULL,
    CONSTRAINT UQ_ImportingSystemImportInfo_EnvXIs_TableName UNIQUE (EnvironmentId, ImportingSystemId, FileName, FileImportTime)
)

注意：表上没有定义其他索引

批量插入 （我在分析器中捕获的内容，仅一批）

insert bulk [dbo].[ImportingSystemFileLoadInfo] ([EnvironmentId] Int, [ImportingSystemId] Int, [FileName] NVarChar(64) COLLATE Latin1_General_CI_AS, [FileImportTime] DateTime2(7))

指标

  • 已插入 695 项
  • 中央处理器 = 31
  • 读取 = 4271
  • 写入 = 24
  • 持续时间 = 154
  • 总表数 = 11500

对于我的应用程序，没关系，虽然读取看起来相当大（我对 SQL Server 内部知识知之甚少，所以我比较了 8K 页面大小和我拥有的小记录信息）

问题：如何调查此 BULK INSERT 是否可以优化？或者它没有任何意义，因为它可以说是将大数据从客户端应用程序推送到 SQL Server 的最快方式？

sql-server sql-server-2014

3 个回答

  1. Best Answer

    据我所知，您可以以与优化常规插入非常相似的方式优化批量插入。通常，简单插入的查询计划信息量不大，因此不必担心没有计划。我将介绍几种优化插入的方法，但其中大多数可能不适用于您在问题中指定的插入。但是，如果您将来需要加载大量数据，它们可能会有所帮助。

    1.按聚类键顺序插入数据

    SQL Server 通常会在将数据插入具有聚集索引的表之前对其进行排序。对于某些表和应用程序，您可以通过对平面文件中的数据进行排序并让 SQL Server 知道数据是通过以下ORDER参数排序的，从而提高性能BULK INSERT：

    订单（{ 列 [ ASC | DESC ] } [ ,... n ] ）

    指定数据文件中的数据如何排序。如果要导入的数据根据​​表上的聚集索引（如果有）进行排序，则可以提高批量导入性能。

    由于您使用IDENTITY列作为聚集键，因此您无需担心这一点。

    2.TABLOCK尽可能使用

    如果保证只有一个会话向表中插入数据，则可TABLOCK以为BULK INSERT. 这可以减少锁争用，并在某些情况下导致最少的日志记录。但是，您要插入到具有已包含数据的聚集索引的表中，因此如果没有跟踪标志 610，您将不会获得最少的日志记录，这将在本答案后面提到。

    如果TABLOCK不可能，因为你不能改变代码，并不是所有的希望都失去了。考虑使用sp_table_option：

    EXEC [sys].[sp_tableoption]
    @TableNamePattern = N'dbo.BulkLoadTable' ,
    @OptionName = 'table lock on bulk load' , 
    @OptionValue = 'ON'

    另一种选择是启用跟踪标志 715。

    3. 使用适当的批量大小

    有时您可以通过更改批量大小来调整插入。

    ROWS_PER_BATCH = rows_per_batch

    表示数据文件中数据的大致行数。

    默认情况下，数据文件中的所有数据作为单个事务发送到服务器，并且查询优化器不知道批处理中的行数。如果您指定 ROWS_PER_BATCH（值 > 0），则服务器使用此值来优化批量导入操作。为 ROWS_PER_BATCH 指定的值应与实际行数大致相同。有关性能注意事项的信息，请参阅本主题后面的“备注”。

    以下是文章后面的引述：

    如果单个批次中要刷新的页面数量超过内部阈值，则可能会发生缓冲池的完整扫描，以确定在批次提交时要刷新哪些页面。此完整扫描可能会损害批量导入性能。当大型缓冲池与慢速 I/O 子系统结合使用时，可能会出现超过内部阈值的情况。为避免大型机器上的缓冲区溢出，请不要使用 TABLOCK 提示（这将删除批量优化）或使用较小的批量大小（保留批量优化）。

    由于计算机各不相同，我们建议您使用数据负载测试各种批量大小，以找出最适合您的方法。

    就我个人而言，我只会在一个批次中插入所有 695 行。但是，在插入大量数据时，调整批量大小会产生很大的不同。

    4.确保您需要该IDENTITY列

    我对您的数据模型或要求一无所知，但不要陷入为IDENTITY每个表添加列的陷阱。Aaron Bertrand 有一篇关于这方面的文章，称为要改掉的坏习惯：在每个表上放置一个 IDENTITY 列。需要明确的是，我并不是说您应该IDENTITY从该表中删除该列。但是，如果您确定IDENTITY不需要该列并将其删除，这可能会提高插入性能。

    5.禁用索引或约束

    如果您将大量数据加载到表中，而不是您已经拥有的数据，那么在加载之前禁用索引或约束并在加载之后启用它们可能会更快。对于大量数据，SQL Server 一次构建索引而不是将数据加载到表中通常效率较低。看起来您将 695 行插入到一个有 11500 行的表中，所以我不推荐这种技术。

    6. 考虑 TF 610

    跟踪标志 610 允许在一些附加场景中进行最少的日志记录。对于具有IDENTITY聚集键的表，只要您的恢复模型是简单的或批量记录的，您将获得对任何新数据页的最少记录。我相信此功能默认情况下未启用，因为它可能会降低某些系统的性能。在启用此跟踪标志之前，您需要仔细测试。推荐的 Microsoft 参考似乎仍然是The Data Loading Performance Guide

    跟踪标志 610 下最小日志记录的 I/O 影响

    当您提交一个记录最少的批量加载事务时，所有加载的页面必须在提交完成之前刷新到磁盘。任何未被早期检查点操作捕获的刷新页面都会产生大量随机 I/O。将此与完全记录的操作进行对比，后者在日志写入时创建顺序 I/O，并且不需要在提交时将加载的页面刷新到磁盘。

    如果您的负载场景是不跨越检查点边界的 btree 上的小型插入操作，并且您的 I/O 系统速度较慢，那么使用最少的日志记录实际上会降低插入速度。

    据我所知，这与跟踪标志 610 无关，而是与最少的日志记录本身有关。我相信早先关于ROWS_PER_BATCH调优的引述也涉及到了同样的概念。

    总之，您可能无法调整BULK INSERT. 我不会担心您在插入时观察到的读取计数。每当您插入数据时，SQL Server 都会报告读取。考虑以下非常简单的问题INSERT：

    DROP TABLE IF EXISTS X_TABLE;

CREATE TABLE X_TABLE (
VAL VARCHAR(1000) NOT NULL
);

SET STATISTICS IO, TIME ON;

INSERT INTO X_TABLE WITH (TABLOCK)
SELECT REPLICATE('Z', 1000)
FROM dbo.GetNums(10000); -- generate 10000 rows

    输出SET STATISTICS IO, TIME ON：

    表'X_TABLE'。扫描计数 0，逻辑读取 11428

    我报告了 11428 次读取，但这不是可操作的信息。有时可以通过最少的日志记录来减少报告的读取次数，但当然不能将差异直接转化为性能提升。

    • 17

  2. 我将开始回答这个问题，目的是在我建立技巧知识库时不断更新这个答案。希望其他人能遇到这一点，并帮助我在此过程中提高自己的知识。

    1. 直觉检查：您的防火墙是否在进行有状态的深度数据包检查？您在 Internet 上找不到太多关于此的内容，但如果您的批量插入速度比应有的速度慢 10 倍，那么您可能有一个安全设备进行 3-7 级深度数据包检查并检查“通用 SQL 注入预防” ”。

    2. 测量您计划批量插入的数据的大小，以字节为单位，每批。并检查您是否存储了任何 LOB 数据，因为这是一个单独的页面读取和写入操作。

      您应该这样做的几个原因：

      一个。在 AWS 中，弹性块存储 IOPS 被分解为字节，而不是行。

      1. 有关什么是 EBS IOPS 单位的说明，请参阅Linux 实例上的 Amazon EBS 卷性能 » I/O 特征和监控
      2. 具体来说，通用 SSD (gp2) 卷具有“I/O 积分和突发性能”概念，繁重的 ETL 处理通常会耗尽突发余额积分。您的突发持续时间以字节为单位，而不是 SQL Server 行:)

      湾。虽然大多数库或白皮书根据行数进行测试，但实际上是可以写入的页数，为了计算这一点，您需要知道每行有多少字节以及您的页面大小（通常为 8KB ，但请务必仔细检查您是否从其他人那里继承了该系统。）

      SELECT *
FROM 
sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'YourTable'), NULL, NULL, 'DETAILED')

      注意 avg_record_size_in_bytes 和 page_count。

      C。正如 Paul White 在https://sqlperformance.com/2019/05/sql-performance/minimal-logging-insert-select-heap中解释的那样，“要启用最小日志记录INSERT...SELECT，SQL Server 的总大小必须超过 250 行至少一个范围（8 页）。”

    3. 如果您有任何带有检查约束或唯一约束的索引，请使用SET STATISTICS IO ONand SET STATISTICS TIME ON（或 SQL Server Profiler 或 SQL Server 扩展事件）来捕获信息，例如您的批量插入是否有任何读取操作。读取操作是由于 SQL Server 数据库引擎确保完整性约束通过。

    4. 尝试创建一个测试数据库，其中PRIMARYFILEGROUP安装在 RAM 驱动器上。这应该比 SSD 稍快，但也消除了关于您的 RAID 控制器是否会增加开销的任何问题。在 2018 年，它不应该，但是通过创建多个像这样的差异基线，您可以大致了解硬件增加了多少开销。

    5. 还将源文件也放在 RAM 驱动器上。

      如果您从数据库服务器的 FILEGROUP 所在的同一驱动器读取源文件，则将源文件放在 RAM 驱动器上将排除任何争用问题。

    6. 确认您已使用 64KB 扩展区格式化您的硬盘驱动器。

    7. 使用UserBenchmark.com对您的 SSD 进行基准测试。这将：

      1. 向其他性能爱好者添加更多关于设备性能期望的知识
      2. 帮助您确定您的驱动器的性能是否低于具有相同驱动器的同行
      3. 帮助您确定您的驱动器的性能是否低于同一类别的其他驱动器（SSD、HDD 等）

    8. 如果您通过实体框架扩展从 C# 调用“INSERT BULK”，请确保首先“预热”JIT 并“丢弃”前几个结果。

    9. 尝试为您的程序创建性能计数器。使用 .NET，您可以使用benchmark.NET，它会自动分析一堆基本指标。然后，您可以与开源社区分享您的分析器尝试，并查看运行不同硬件的人是否报告相同的指标（即从我之前关于使用 UserBenchmark.com 进行比较的观点）。

    10. 尝试使用命名管道并将其作为 localhost 运行。

    11. 如果您的目标是 SQL Server 并使用 .NET Core，请考虑使用 SQL Server Std Edition 启动 Linux - 即使对于严重的硬件，每小时的成本也不到一美元。使用不同操作系统尝试使用相同硬件的相同代码的主要优点是查看操作系统内核的 TCP/IP 堆栈是否导致问题。

    12. 使用 Glen Barry 的 SQL Server 诊断查询来测量存储数据库表的 FILEGROUP 的驱动器的驱动器延迟。

      一个。确保在测试之前和测试之后进行测量。“在你的测试之前”只是告诉你你是否有可怕的 IO 特征作为基线。

      湾。对于“在测试期间”进行测量，您确实需要使用 PerfMon 性能计数器。

      为什么？因为大多数数据库服务器使用某种网络附加存储（NAS）。在云中，在 AWS 中，弹性块存储就是这样。您可能会受到 EBS 卷/NAS 解决方案的 IOPS 的限制。

    13. 使用一些工具来衡量等待统计。 Red Gate SQL Monitor、SolarWinds Database Performance Analyzer，甚至 Glen Barry 的 SQL Server 诊断查询，或Paul Randal 的 Wait Statistics 查询。

      一个。最常见的等待类型可能是 Memory/CPU、WRITELOG、PAGEIOLATCH_EX 和ASYNC_NETWORK_IO。

      湾。如果您正在运行可用性组，您可能会产生额外的等待类型。

    14. 在禁用的情况下测量多个同时INSERT BULK命令的效果TABLOCK（TABLOCK 可能会强制序列化 INSERT BULK 命令）。您的瓶颈可能正在等待 aINSERT BULK完成；您应该尝试将数据库服务器的物理数据模型可以处理的尽可能多的这些任务排队。

    15. 考虑对表进行分区。举个特别的例子：如果你的数据库表是只追加的，Andrew Novick 建议创建一个“TODAY”FILEGROUP并分区到至少两个文件组，TODAY 和 BEFORE_TODAY。这样，如果您的INSERT BULK数据只是今天的数据，您可以过滤 CreatedOn 字段以强制所有插入命中单个FILEGROUP，从而减少使用时的阻塞TABLOCK。此技术在 Microsoft 白皮书：使用 SQL Server 2008 的分区表和索引策略中进行了更详细的描述

    16. 如果您使用的是列存储索引，请关闭TABLOCK并加载 102,400 行 Batch Size 中的数据。然后，您可以将所有数据并行加载到列存储行组中。这个建议（以及记录在案的合理性）来自 Microsoft 的Columnstore 索引 - 数据加载指南：

      批量加载具有以下内置性能优化：

      并行加载：您可以有多个并发批量加载（bcp 或批量插入），每个加载单独的数据文件。与行存储批量加载到 SQL Server 不同，您不需要指定TABLOCK，因为每个批量导入线程都会将数据以独占方式加载到单独的行组（压缩或增量行组）中，并对其进行独占锁定。使用TABLOCK将强制对表进行排他锁，您将无法并行导入数据。

      最少的日志记录：批量加载对直接进入压缩行组的数据使用最少的日志记录。任何进入增量行组的数据都会被完整记录。这包括小于 102,400 行的任何批量大小。但是，批量加载的目标是让大多数数据绕过增量行组。

      锁定优化：加载到压缩行组时，获取行组上的 X 锁。但是，当批量加载到增量行组时，会在行组获取 X 锁，但 SQL Server 仍会锁定锁 PAGE/EXTENT，因为 X 行组锁不是锁定层次结构的一部分。

    17. 从 SQL Server 2016 开始，不再需要启用跟踪标志 610 以最小化登录索引表。引用微软工程师 Parikshit Savjani （强调我的）：

      SQL Server 2016 的设计目标之一是提高引擎开箱即用的性能和可扩展性，使其运行速度更快，而无需客户使用任何旋钮或跟踪标志。作为这些改进的一部分，SQL Server 引擎代码中的一项增强功能是在使用简单或批量记录恢复模型。如果您不熟悉最小日志记录，我强烈建议您阅读 Sunil Agrawal 的这篇博文，他解释了 SQL Server 中最小日志记录的工作原理。对于要最小化记录的批量插入，它仍然需要满足此处记录的先决条件。

      作为 SQL Server 2016 中这些增强功能的一部分，您不再需要启用跟踪标志 610 以最大限度地减少对索引表的登录它与其他一些跟踪标志（1118、1117、1236、8048）一起成为历史的一部分。在 SQL Server 2016 中，当批量加载操作导致分配新页面时，如果满足前面讨论的最小日志记录的所有其他先决条件，则顺序填充该新页面的所有行都将被最小记录。插入到现有页面（没有新页面分配）以维护索引顺序的行仍然被完全记录，在加载期间由于页面拆分而移动的行也是如此。为索引打开 ALLOW_PAGE_LOCKS（默认为 ON）以使最小的日志记录操作工作也很重要，因为在分配期间会获取页面锁，因此只记录页面或范围分配。

    18. 如果您在 C# 或 EntityFramework.Extensions 中使用 SqlBulkCopy（在后台使用 SqlBulkCopy），请检查您的构建配置。您是否在发布模式下运行测试？目标体系结构是否设置为任何 CPU/x64/x86？

    19. 考虑使用 sp_who2 查看 INSERT BULK 事务是否已挂起。它可能被挂起，因为它被另一个 spid 阻止。考虑阅读如何最小化 SQL Server 阻塞。您也可以使用 Adam Machanic 的 sp_WhoIsActive，但 sp_who2 将为您提供所需的基本信息。

    20. 您可能只是有错误的磁盘 I/O。如果您进行批量插入并且您的磁盘利用率没有达到 100%，并且停留在 2% 左右，那么您可能有错误的固件或有缺陷的 I/O 设备。（这发生在我的一位同事身上。）使用 [SSD UserBenchmark] 与其他人比较硬件性能，特别是如果您可以在本地开发机器上复制慢速。（我把它放在列表的最后，因为由于 IP 风险，大多数公司不允许开发人员在他们的本地机器上运行数据库。）

    21. 如果您的表使用压缩，您可以尝试运行多个会话，并在每个会话中，从使用现有事务开始并在 SqlBulkCopy 命令之前运行它：

      ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=AUTO;

    22. 对于连续加载，一个想法流首先在 Microsoft 白皮书《使用 SQL Server 2008 的分区表和索引策略》中进行了概述：

      连续加载

      在 OLTP 场景中，新数据可能会不断涌入。如果用户也在查询最新的分区，连续插入数据可能会导致阻塞：用户查询可能会阻塞插入，同样插入也可能会阻塞用户查询。

      READ COMMITTED SNAPSHOT使用快照隔离（尤其是隔离级别）可以减少加载表或分区上的争用。在READ COMMITTED SNAPSHOT隔离状态下，向表中插入不会导致tempdb版本存储中的活动，因此插入的tempdb开销最小，但用户查询不会占用同一分区上的共享锁。

      在其他情况下，当数据以高速率连续插入分区表时，您仍然可以在暂存表中暂存数据，然后将数据重复插入到最新的分区中，直到当前分区通过，然后将数据插入下一个分区。例如，假设您有两个临时表，每个表交替接收 30 秒的数据：一个表用于前半分钟，第二个表用于后半分钟。插入存储过程确定当前插入在哪半分钟，然后插入到第一个临时表中。当 30 秒结束时，插入过程确定它必须插入到第二个临时表中。然后另一个存储过程将数据从第一个登台表加载到表的最新分区中，然后截断第一个登台表。又过了 30 秒，同一个存储过程将第二个存储过程中的数据插入到当前分区中，然后截断第二个 staging 表。

    23. Microsoft CAT 团队的数据加载性能指南

    24. 确保您的统计数据是最新的。如果可以在每个索引构建后使用 FULLSCAN。

    25. 使用 SQLIO 进行 SAN 性能调优，如果您使用的是机械磁盘，请确保您的磁盘分区是对齐的。请参阅 Microsoft 的磁盘分区对齐最佳实践。

    26. COLUMNSTORE INSERT/UPDATE性能

    • 14

  3. 读取可能是在插入期间检查的唯一和 FK 约束 - 如果您可以在插入期间禁用/删除它们并在之后启用/重新创建它们，则可能会提高速度。与保持它们处于活动状态相比，您需要测试这是否会使整体速度变慢。如果其他进程同时写入同一个表，这也可能不是一个好主意。-加雷斯·里昂

    根据 Q & A Foreign keys become untrusted after bulk insert，FK 约束在BULK INSERT没有CHECK_CONSTRAINTS选项后变得不受信任（我的情况是我以不受信任的约束结束）。目前尚不清楚，但是检查它们并仍然使它们不受信任是没有意义的。但是，仍将检查 PK 和 UNIQUE（请参阅BULK INSERT (Transact-SQL)）。-阿列克谢

    • 2

相关问题