如何优化可能进行参数嗅探的 SQL Server 查询

SQL 2017 上的这个简化查询需要 40 多秒才能完成，我怀疑它的参数嗅探问题，但不是 100% 确定。

exec sp_executesql N'
SELECT
    T.[TicketRecId]
    , T.[Title]
FROM dbo.Ticket T
INNER JOIN [dbo].[State] S  
    ON S.[StateRecId] = T.[StateRecId]  
INNER JOIN [dbo].[StateType] ST  
    ON S.[StateTypeRecId] = ST.StateTypeRecId
INNER JOIN [dbo].[Board] B  
    ON B.[BoardRecId] = T.[BoardRecId]  
WHERE 1=1
    AND ST.[Name] NOT IN (''Closed'',''Canceled'')
    AND T.BoardRecId IN (SELECT items FROM dbo.Split(@BoardRecId, '',''))
    --AND T.BoardRecId = @BoardRecId2
    --AND T.BoardRecId = 17
ORDER BY T.[TicketRecId] ASC
OFFSET (@PageNo - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
',N'@PageNo int,@PageSize int,@TeamRecId int,@BoardRecId VARCHAR(2),@BoardRecId2 INT'
    ,@PageNo=1,@PageSize=50,@TeamRecId=4,@BoardRecId ='17',@BoardRecId2=17

• dbo.Ticket此查询返回表中 170 万行中的 15 行
• 表State, StateType,Board真的很小，分别有 400、20、35 行
• 在WHERE子句中，如果我将IN过滤器换成=for T.BoardRecId，它会在 7 秒内完成
• 删除OFFSET FETCH，原始查询在 13 秒内完成，前一个查询在 1 秒内完成
• 如果我设置参数值@BoardRecId='14'，持续时间会提高（大部分表包含具有此值的行）
• 尝试使用表的全扫描更新统计信息，性能没有变化
• 尝试创建不同的索引，性能没有提高
• 试过OPTION (RECOMPILE)里面sp_executesql没有帮助
• 尚未尝试重建索引，因为这必须在维护时间内完成
• 尝试用dbo.Split表变量和/或临时表替换，没有改进

我需要支持 multiple 的能力BoardRecIds，这就是函数背后的原因dbo.Split，它所做的只是打破一个逗号分隔的字符串，以便在IN子句中使用。

该模式在列方面要大得多，因此尝试简化它，请注意连接的列都有索引。

CREATE TABLE [Support].[Ticket] (
    [TicketRecId]             BIGINT             IDENTITY (1, 1) NOT NULL,
    [BoardRecId]              INT                NOT NULL,
    [StateRecId]              INT                NOT NULL,
    [Title]                   VARCHAR (250)      NOT NULL,
    [IsDeleted]               BIT        NOT NULL,
    [IsTemplate]              BIT        NOT NULL,
    CONSTRAINT [PK_Ticket_TicketRecId] PRIMARY KEY CLUSTERED ([TicketRecId] ASC),
    CONSTRAINT [FK_Ticket_BoardRecId] FOREIGN KEY ([BoardRecId]) REFERENCES [Support].[Board] ([BoardRecId]),
);
GO

CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecIdStateRecIdIsDeletedIsTemplate_Include]
ON [Support].[Ticket] ([BoardRecId],[StateRecId],[IsDeleted],[IsTemplate],)
GO

CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecId_IsDeleted_IsTemplate_Includes]
ON [Support].[Ticket] ([BoardRecId], [IsDeleted], [IsTemplate], [ContactRecId], [ContactSourceRecId])
INCLUDE ([StateRecId]);
GO

CREATE TABLE [Support].[Ticket] (
    [TicketRecId]             BIGINT             IDENTITY (1, 1) NOT NULL,
    [BoardRecId]              INT                NOT NULL,
    [StateRecId]              INT                NOT NULL,
    [Title]                   VARCHAR (250)      NOT NULL,
    [IsDeleted]               BIT        NOT NULL,
    [IsTemplate]              BIT        NOT NULL,
    CONSTRAINT [PK_Ticket_TicketRecId] PRIMARY KEY CLUSTERED ([TicketRecId] ASC),
    CONSTRAINT [FK_Ticket_BoardRecId] FOREIGN KEY ([BoardRecId]) REFERENCES [Support].[Board] ([BoardRecId]),
);
GO

CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecIdStateRecIdIsDeletedIsTemplate_Include]
ON [Support].[Ticket] ([BoardRecId],[StateRecId],[IsDeleted],[IsTemplate],)
GO

CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecId_IsDeleted_IsTemplate_Includes]
ON [Support].[Ticket] ([BoardRecId], [IsDeleted], [IsTemplate], [ContactRecId], [ContactSourceRecId])
INCLUDE ([StateRecId]);
GO

CREATE TABLE [dbo].[State] (
    [StateRecId]     INT                IDENTITY (1, 1) NOT NULL,
    [BoardRecId]     INT                NOT NULL,
    [StateTypeRecId] INT                NOT NULL,
    [Name]           VARCHAR (50)       NOT NULL,
    [SortOrder]      SMALLINT           NOT NULL,
    [IsDefault]      BIT                NOT NULL,
    [IsDeleted]      BIT                DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_State_StateRecId] PRIMARY KEY CLUSTERED ([StateRecId] ASC),
    CONSTRAINT [FK_State_BoardRecId] FOREIGN KEY ([BoardRecId]) REFERENCES [dbo].[Board] ([BoardRecId]),
    CONSTRAINT [FK_State_StateTypeRecId] FOREIGN KEY ([StateTypeRecId]) REFERENCES [dbo].[StateType] ([StateTypeRecId]),
);
GO

CREATE TABLE [dbo].[StateType] (
    [StateTypeRecId] INT                IDENTITY (1, 1) NOT NULL,
    [Name]           VARCHAR (50)       NOT NULL,
    [IsDeleted]      BIT                DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_StateType_StateTypeRecId] PRIMARY KEY CLUSTERED ([StateTypeRecId] ASC),
);
GO

CREATE TABLE [dbo].[Board] (
    [BoardRecId]     INT                IDENTITY (1, 1) NOT NULL,
    [TeamRecId]      INT                NOT NULL,
    [Name]           VARCHAR (50)       NOT NULL,
    [IsExternal]     BIT                DEFAULT ((0)) NOT NULL,
    [IsDefault]      BIT                DEFAULT ((0)) NOT NULL,
    [IsDeleted]      BIT                DEFAULT ((0)) NOT NULL,
    [UpdatedDateUTC] DATETIMEOFFSET (0) DEFAULT (SYSUTCDATETIME()) NOT NULL,
    CONSTRAINT [PK_Board_BoardRecId] PRIMARY KEY CLUSTERED ([BoardRecId] ASC),
    CONSTRAINT [FK_Board_TeamRecId] FOREIGN KEY ([TeamRecId]) REFERENCES [dbo].[Team] ([TeamRecId]),
);
GO
CREATE NONCLUSTERED INDEX [nc_TicketType_BoardRecId]
    ON [dbo].[Board]([BoardRecId] ASC);
GO

使用@BoardRecId IN 的简化计划（SELECT items FROM dbo.Split(...) - 40+ 秒
使用@BoardRecId = @BoardRecId 的简化计划- 7 秒
使用@BoardRecId = 17 和 dbo.Split(...) 的简化计划- 12 秒

这些持续时间都不是最佳的，但第 2 和第 3 比 40+ 秒要好得多，所以只是想弄清楚如何充分利用糟糕的情况，并希望有人能在这里提供灵丹妙药。