高事务量表查询时长波动问题排查求助

近期发现访问我们最繁忙（高事务量）表的一条主查询执行时长波动极大，耗时介于<1ms到约7ms之间，原因不明。最初推测是特定账号值缓存导致分区行数差异，但已排除该可能。

当前数据库为SQL Server 2012，正升级至2019，重点关注该查询的性能表现。我们每周仅通过FULLSCAN、NORECOMPUTE更新一次该表统计信息，统计信息不会频繁变更。

表结构 #

CREATE TABLE [dbo].[MyTable](
    [INST_NO] [binary](3) NOT NULL,
    [ACCT_NO] [binary](16) NOT NULL,
    [REC_NO] [binary](9) NOT NULL,
    [TRAN_DATE] [decimal](9, 0) NOT NULL,
    [POST_DATE] [decimal](9, 0) NULL,
    [POST_TIME] [decimal](9, 0) NULL,
    [JRNL_NO] [decimal](9, 0) NULL,
    [TRAN_TYPE] [decimal](2, 0) NULL,
    [DELI] [binary](1) NULL,
    [VAR_AREA] [binary](80) NULL,
    [UUID] [binary](36) NULL,
    [UUID_SEQ] [binary](2) NULL,
 CONSTRAINT [MyTablePK] PRIMARY KEY CLUSTERED 
(
    [INST_NO] ASC,
    [ACCT_NO] ASC,
    [REC_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85)
)
GO

ALTER TABLE [dbo].[MyTable] SET (LOCK_ESCALATION = AUTO)
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_TRAN_DATE]  DEFAULT ((0)) FOR [TRAN_DATE]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF__MyTable__POST_DATE__47EB5F47]  DEFAULT ((0)) FOR [POST_DATE]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF__MyTable__POST_TIME__48DF8380]  DEFAULT ((0)) FOR [POST_TIME]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF__MyTable__JRNL_NO__49D3A7B9]  DEFAULT ((0)) FOR [JRNL_NO]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF__MyTable__TRAN_TYPE__4AC7CBF2]  DEFAULT ((0)) FOR [TRAN_TYPE]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF__MyTable__DELI__4BBBF02B]  DEFAULT (CONVERT([binary](1),replicate(char((0)),(1)))) FOR [DELI]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF__MyTable__VAR_AREA__4CB01464]  DEFAULT (CONVERT([binary](80),replicate(char((0)),(80)))) FOR [VAR_AREA]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_UUID]  DEFAULT (CONVERT([binary](36),replicate(char((32)),(36)))) FOR [UUID]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_UUID_SEQ]  DEFAULT (CONVERT([binary](2),replicate(char((48)),(2)))) FOR [UUID_SEQ]
GO

查询语句 #

SELECT TOP 1  INST_NO ,ACCT_NO ,REC_NO ,TRAN_DATE ,POST_DATE ,POST_TIME ,JRNL_NO ,TRAN_TYPE ,DELI ,VAR_AREA ,ISNULL(UUID,CAST(REPLICATE(CHAR(32),36) as BINARY)) ,ISNULL(UUID_SEQ,CAST(REPLICATE(CHAR(48),2) as BINARY)) 
FROM MyTable                               
WHERE (INST_NO = @P1     AND  ACCT_NO = @P2     AND  REC_NO > @P3  )ORDER BY INST_NO  ASC, ACCT_NO  ASC, REC_NO  ASC

执行计划差异 #

该查询存在两种执行计划：

• 平均耗时<1ms的执行计划
• 平均耗时约7ms的执行计划

恳请专业人士提供排查思路。

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