Azure SQL带TOP级联删除异常：TOP(3+)无返回问题排查

问题背景 #

在Azure SQL环境中存在三张关联表：orders（小表）、shipments（较大表）、destinations（大表），建表及索引语句如下：

create table orders
(
  id                        bigint    not null,
  constraint order_pk primary key (id),
  delivery_date             date      not null,
  latest                    bit       not null,
  created_on                datetime2 not null,
)
create index orders_house_keeping_idx on orders(created_on, latest);

create table shipments
(
  id               bigint    not null,
  constraint shipment_pk primary key (id),
  order_id         bigint    not null,
  constraint shipment_parent_order_fk foreign key (order_id) references orders (id),
  delivery_runtime tinyint   not null,
  created_on       datetime2 not null,
)
alter table shipments
  add constraint shipment_parent_order_fk foreign key (order_id) references orders (id) on delete cascade;
create index shipments_order_id_fk_idx on shipments (order_id, delivery_runtime);

create table destinations
(
  id             bigint       not null,
  constraint destination_pk primary key (id),
  order_id         bigint    not null,
  prefix         varchar(255) not null,
  shipment_id    bigint       not null,
  constraint destination_parent_shipment_fk foreign key (shipment_id) references shipments (id),
  created_on     datetime2    not null,
)
alter table destinations
  add constraint destination_parent_shipment_fk foreign key (shipment_id) references shipments (id) on delete cascade;
alter table destinations
  add constraint destination_order_id_fk foreign key (order_id) references orders (id);
create index destinations_order_id_fk_idx on destinations(order_id)

执行级联删除语句时出现异常：

delete top(2) o from orders o         
where o.created_on < '2023-08-25' and o.latest = 0

具体执行现象：

• delete top(1/2) 始终耗时5秒完成
• delete top(3+) 无返回结果
• 当删除条件改为o.latest = 1时，delete top(29)可立即返回，但delete top(30+)同样无返回

已排查情况：数据库服务器CPU、内存、数据IO无明显波动；调整OPTION (MAXDOP 0)无效果；无法获取TOP(3+)及以上查询的执行计划。

可能的异常原因 #

1. 级联删除路径的索引缺失 #

当前destinations表仅创建了order_id的索引，但级联删除的关联路径是orders → shipments → destinations：删除orders时会先删关联的shipments，再通过shipment_id删除对应的destinations记录。由于destinations没有针对shipment_id的索引，每次删除一条shipments记录都要对大表destinations执行全表扫描。

• 小N时，扫描总次数和数据量有限，5秒内可完成；
• N超过阈值后，多次全表扫描的时间成本叠加，导致操作长时间无法完成，表现为无返回。

2. 事务日志持久化瓶颈 #

级联删除是单事务操作，TOP(N)越大，事务覆盖的记录数越多，生成的事务日志量越大。Azure SQL存在隐性的日志持久化限流机制，当日志生成量超过后台处理能力时，事务会进入阻塞等待状态——表面CPU/IO指标可能无明显变化，因为日志写入是异步队列式处理，只有队列满后事务才会阻塞。

• 小N时日志量小，能快速完成持久化；大N时日志队列积压，事务长时间挂起。

3. 执行计划切换与锁升级 #

查询优化器会根据TOP(N)的大小选择不同的执行策略：

• 小N时可能采用嵌套循环逐行级联删除，锁粒度为行锁，资源占用可控；
• 大N时优化器可能切换为哈希匹配等批量操作，触发行锁升级为表锁，引发内部锁等待。由于destinations是大表，表锁会导致后续操作阻塞，甚至因锁等待超时或资源耗尽而无响应。

4. 统计信息过期导致执行计划低效 #

若三张表的统计信息过期，查询优化器无法准确评估删除操作涉及的子表数据量。小N时，优化器选择的执行计划尚能应对；但N增大后，错误的数据量估计会导致优化器选择低效的执行计划，进而引发长时间运行甚至无响应。

验证与修复建议 #

• 为destinations表添加shipment_id索引：create index destinations_shipment_id_fk_idx on destinations(shipment_id);，消除级联删除时的全表扫描；
• 更新三张表的统计信息：UPDATE STATISTICS orders; UPDATE STATISTICS shipments; UPDATE STATISTICS destinations;，确保优化器能获取准确的数据分布；
• 手动拆解级联删除：先查询出待删除的orders ID，依次删除destinations、shipments、orders，验证是否是级联执行计划的问题；
• 执行大TOP删除时，通过sys.dm_tran_locks和sys.dm_exec_requests查看锁等待类型和请求状态，定位具体阻塞原因。

内容的提问来源于stack exchange，提问作者Ginkgochris