主页 / dba / 问题 / 262111
Accepted
Danielle Paquette-Harvey
Asked: 2020-03-18 05:58:49 +0800 CST

SQL 估计在 DELETE 语句上与大型表上的触发器相差甚远

  • 772

我正在使用 Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 版权所有 (c) Microsoft Corporation Standard Edition (64-bit) o​​n Windows Server 2012 R2标准 6.3（内部版本 9600：）

此服务器位于 SSD 驱动器上，最大内存为 128 GB。CostTheshold for Parallelism 是 70，MaxDegree of Parallelism 是 3。

我有一个“Trips”表，它被 23 个外键和 ON DELETE CASCADE 选项引用。

这个表本身并没有那么大（530 万行，1.3 GB 的数据）。但是在 23 个引用的表中，有两个表非常大（超过 10 亿行，每个 54 和 69 GB）。

问题是当我们尝试删除“Trips”表中的少量行（比如说 4 行）时，SQL 估计要删除这么多行，它需要 10gb 的 RAM，估计数百万行将被删除返回，并锁定表。一切都停止，其他查询阻塞，应用程序超时。

以下是 1 个删除语句的主表和行数：

  • 行程（4 行）
  • Segments（27 行，与 Trips by SegmentId 相关）
  • 配置文件（2012 行，与 SegmentId 相关的 Segments）
  • ProfileRanges（2337 行，通过 ProfileId 与 Profiles 相关）
  • 事件（7750 行，与 SegmentId 相关的 Segments）
  • EventConditions（9230 行，通过 EventId 与事件相关）

表 EventConditions 和 ProfileRanges 各有超过 10 亿行。

这是计划缓存：https ://www.brentozar.com/pastetheplan/?id=HJNg5I0BU

当我查看 SentryOne 计划资源管理器时，我可以看到 SQL 正在读取整个表，即使“表假脱机”随后过滤并仅保留 2012 行 ProfileRanges 和 EventConditions 大致相同。

轮廓范围

TableSpool Profile范围

事件条件

TableSpool 事件条件

当我使用 Brent Ozar 的 sp_blitzCache 过程查看查询的内存授予时，我可以看到该查询要求大约 10gb 的 RAM。

内存授予

之后，查询要么等待 SOS_SCHEDULER_YIEL（因此等待 4ms 后轮到使用 CPU）或 MEMORY_ALLOCATION_EXT。程序超时并失败。

我能做些什么来完成这项工作？

我正在考虑的一件事是删除两个最大表上的外键并在而不是触发器中删除它们的行。但我不喜欢使用触发器而不是外键来强制数据库一致性。

任何建议或帮助将不胜感激

ProfileRanges 的主键是

  • ProfileId int
  • ProfileRangeDefId1 int
  • ProfileRangeDefId2 int

EventConditions 的主键是

  • EventId 大整数
  • EventConditionDefId int
sql-server query-performance

2 个回答

  1. Best Answer

    假设所有相关表都有正确的删除路径索引，您可以尝试：

    DELETE [Trips]
WHERE [ISAFileName]='ID_774199_20200311_133117.isa'
OPTION (LOOP JOIN, FAST 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

    如果可行，请尝试将其减少到最少的提示数。

    这类计划对于基数估计非常具有挑战性，而“默认”的 CE 模型通常会造成混乱。

    一旦你有一个工作良好的计划形状，你应该能够在必要时使用计划指南等强制该形状。

    • 17

  2. 级联删除时的表扫描是表上没有正确索引的常见症状。

    确保所有 FK 表都有支持外键的索引。即以 FK 列作为其他表索引中的前导列的聚集或非聚集索引。

    例如

    create index ix_TripId on EventConditions (TripId)

    并考虑 TripID FK 列是否不应该是聚集索引中的前导列，例如：

    create table EventConditions
(
  TripId int not null,
  EventId bigint not null,
  EventConditionDefId int not null,
  constraint pk_EventConditions 
     primary key clustered(TripId, EventId, EventConditionDefId),
  ...
)

    这将优化每个表以供 TripId 访问。

    此外

    我正在考虑的一件事是删除两个最大表上的外键并在而不是触发器中删除它们的行。

    您不需要删除 FK。只需先从子表中删除，并可能从 FK 中删除 ON DELETE CASCADE 以要求先删除子表。这将从加载临时表开始，其中包含要在每个级别删除的键值，然后从上到下加载它们。

    create table #tripIdsToDelete(TripId int primary key)
insert into #tripIdsToDelete ...

create table #EventIdsToDelete(EventId int primary key)
insert into #EventIdsToDelete(EventID)
  select EventId from Events 
  where TripId in (select TripId from #tripIdsToDelete)
...
create table #EventConditionIdsToDelete ...
    • 9

相关问题