SQL Server Agent工作流死锁与性能优化技术问询

项目背景 #

我接手了一个基于SQL Server 2019中SQL Agent作业实现工作流的项目。对象具备特定状态，作业会查找处于起始状态的对象进行处理，随后将其状态更新至下一阶段。

表结构与索引定义 #

CREATE TABLE CurrentStatus (
    ObjectID bigint primary key,
    StatusID smallint,
    UpdateDate DATETIME
)
CREATE NONCLUSTERED INDEX [ixCurrentStatus-StatusID] ON [dbo].[CurrentStatus]
([StatusID] ASC)

CREATE TABLE StatusHistory (
    ObjectID bigint,
    StatusID smallint,
    ErrorMsg varchar(max),
    UpdateDate DATETIME)

CREATE NONCLUSTERED INDEX [ndxStatusHistoryUpdateDate] ON [dbo].[StatusHistory]
([UpdateDate] ASC)
INCLUDE([StatusID])
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180906-190017] ON [dbo].[StatusHistory]
([ObjectID] ASC)

作业步骤逻辑 #

每个作业步骤的代码逻辑如下：

DECLARE @worklist TABLE (ObjectID bigint)
BEGIN TRANSACTION
UPDATE TOP (@batchSize) CurrentStatus SET StatusID=@newstatus, UpdateDate=GetDATE()
OUTPUT inserted.ObjectID into @worklist
WHERE StatusID=@oldStatus

INSERT INTO StatusHistory
SELECT ObjectID, @newStatus, GetDATE()
FROM @worklist
COMMIT TRANSACTION

当前问题 #

近期发现各作业步骤偶发死锁，且StatusID索引未被使用，执行计划对ObjectID进行表扫描。已知CurrentStatus表中99.9%的行处于“Done”状态，非Done状态的StatusID选择性极高，批处理大小默认200，常用值为50。

优化方案咨询 #

针对以下优化方案咨询最佳实践：

1. 是否应为@worklist的ObjectID定义主键，并先通过SELECT语句利用StatusID索引获取数据，再进行更新与历史插入？
2. 维护CurrentStatus与StatusHistory的操作，使用单语句（UPDATE Current OUTPUT inserted.* INTO History）比拆分两个语句更优吗？曾看到旧文章提及前者存在额外开销。

死锁细节 #

死锁报告显示死锁均发生在CurrentStatus表的ObjectID聚集主键上，场景为：

• INSERT持有X锁请求U锁，UPDATE持有U锁请求X锁；
• INSERT持有X锁请求RangeI-N锁，UPDATE持有RangeS-U锁请求X锁。

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