主页 / dba / 问题 / 178114
Accepted
Sir Swears-a-lot
Asked: 2017-07-06 14:14:23 +0800 CST

当目标表具有聚集索引时插入速度要慢得多

  • 772

我只需要调试一个让我彻底困惑的问题。

我们的开发数据仓库上的 ETL 转换流程在每天成功运行数月后刚刚失败。相同的 SSIS 作业调用相同的存储过程，具有相同的表架构、索引和数据在生产中运行良好。

此步骤通常需要不到 2 分钟。今天，4 小时后，这项工作还没有完成，但也没有失败。没有报告错误。SQL 日志中没有任何内容，sp_who2也没有显示任何阻塞。

  • 这是查询执行良好时估计计划的链接。
  • 这是查询未完成时的估计计划的链接。

该作业截断一个临时表，然后插入大约 600,000 行数据。ETL 进程对表具有独占访问权。当我检查时，我能看到的只是 waits on CXPACKET。

我已将故障追溯到一个唯一的聚集索引。

该表在标识列上有一个非聚集主键（见下文）

CREATE TABLE [dbo].[Transform_JobCosting_Transaction](
    [ETL_TransformKey] [int] IDENTITY(1,1) NOT NULL,
    [TransactionId] [varchar](255) NOT NULL,
    [KeyType] [varchar](255) NOT NULL,
    [FinancialYear] [varchar](255) NOT NULL,
    [Job] [varchar](255) NOT NULL,
    [Subjob] [varchar](255) NOT NULL,
    [AnalysisCode] [varchar](255) NULL,
    [etc] [varchar](255) NOT NULL,
    [etc] [varchar](255) NOT NULL,
    [etc] [varchar](255) NOT NULL
     CONSTRAINT [PK_Transform_JobCosting_Transaction] PRIMARY KEY NONCLUSTERED 
(
    [ETL_TransformKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

问题聚集索引是：

CREATE UNIQUE CLUSTERED INDEX [IDX_Unique] ON [dbo].[Transform_JobCosting_Transaction]
(   [FinancialYear] ASC,
    [KeyType] ASC,
    [TransactionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

有一个第三个非聚集索引根本不影响插入。

我们两个人已经为此工作了 4 个小时。我已经删除并重新添加了索引 20-30 次尝试不同组合的选项。

摘要：聚集索引块插入。非集群工作正常。

我们尝试过：

  • 服务器重新启动没有任何区别。
  • 表被截断并从 SSMS 手动运行 sp 没有差异。（有 SA 权限）
  • 重建索引没有帮助。
  • 如果我删除聚集索引，则插入工作。
  • 在上面插入之后，我可以添加聚集索引而不会出错。
  • 如果将索引删除并重新添加为非聚集索引，则它可以工作。
  • 我检查了数据，它是由这 3 个字段分组的唯一的。
  • 更改索引以使其不唯一并没有任何区别。
  • 添加/删除with tablock提示没有帮助。
  • 我尝试在插入之前对数据进行排序，但没有任何区别。

运行：Windows Server 2012 R2 Standard 6.3 上的 Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Developer Edition（64 位）

任何想法或建议将不胜感激。

sql-server sql-server-2016

1 个回答

  1. Best Answer

    让我们退后一步，忘记所有围绕聚集索引的故障排除。您有一个INSERT查询，该查询曾经在合理的时间内完成，但现在不会在几个小时后完成。为什么现在该查询可能很慢？让我们看一下预计的计划：

    估计计划不好

    从右向左读，计划是先从 扫描单行，在内侧Extract_DW_Control_Finance用扫描的做一个循环连接，根据目标表的聚簇键对数据进行排序，再用一个循环连接内侧Extract_JCS_Trans扫描。Extract_GL_Jnl_Trans第一次加入可能不是问题。该计划实际上不能从并行性中受益，但是对于外部结果集中的单行，扫描Extract_JCS_Trans应该只发生一次。但是，优化器估计单行将从该连接中出来。如果该行估计是错误的，那么您最终可能会在Extract_GL_Jnl_Trans.

    执行良好的查询的查询计划使用不同的策略。行估计有很大不同，它执行哈希连接：

    查询计划好查询

    我怀疑如果您修复行估计，优化器将为性能不佳的查询选择不同的计划。如果Extract_DW_Control_Finance表始终只有一行，您可以考虑将其移入局部变量并可能使用RECOMPILE提示。这可能会导致更好的估计。

    至于为什么删除聚集索引会导致问题，我怀疑优化器会在Extract_GL_Jnl_Trans没有聚集索引的情况下进行哈希连接。散列连接不保留外部输入的顺序，但循环连接确实保留了顺序。优化器对单行进行排序和执行循环连接的成本可能低于对 356566 行执行哈希连接和稍后执行排序的成本。但是，如果不需要排序，则执行散列连接的成本可能低于循环连接。这可能都归结为修正你的基数估计。

    如果您需要在慢速查询运行时进行更多故障排除，如果您使用的是 SQL Server 2016 SP1 ，则可以考虑跟踪标志 7412 。这应该为您提供有关 SQL Server 在查询计划中“卡住”的位置的线索。如果您能够要求实际计划或直接在 SSMS 中运行查询，则可以使用 sys.dm_exec_query_profiles 或实时查询统计功能。

    • 8

相关问题