SQL Server参数嗅探问题：OPTION(RECOMPILE)无效 特定参数查询超时

问题背景 #

我有一个运行在SQL Server 2016上的ASP.NET MVC 5 Web应用，会执行如下参数化动态查询：

select
    P.BusinessTypeId BusinessTypeId,
    BT.description BusinessTypeDescription,
    P.ProductFamilyId ProductFamilyId,
    P.ProductId ProductId,
    coalesce(PD.BrandedNm, P.nm) ProductNm,
    P.description ProductDescription,
    PLT.LifeTypeId LifeTypeId,
    LT.Description LifeTypeDescription,
    S.SellerId SellerId,
    S.SellerNm SellerNm,
    P.HasProductSubtypeCd HasProductSubtypeCd,
    P.HasProdsubCommissionCd HasProdsubCommissionCd,
    P.PensionTypeId PensionTypeId,
    PT.Description PensionTypeDescription,
    P.IsSinglePremiumOnlyCd IsSinglePremiumOnlyCd,
    P.PensionSubtypeCd PensionSubtypeCd,
    P.PrsaPaymentTypeCd PrsaPaymentTypeCd,
    PS.ProductSubtypeId ProductSubtypeId,
    PS.Description ProductSubtypeDescription,
    p.AdviceDrivenProduct AdviceDrivenProduct
FROM
    seller S
    inner join ProductDistribution PD on S.DistributionId = PD.DistributionId
    inner join Product P on PD.ProductFamilyId = P.ProductFamilyId and PD.ProductId = P.ProductId
    inner join ProductLifeType PLT on P.ProductFamilyId = PLT.ProductFamilyId and P.ProductId = PLT.ProductId
    inner join BusinessType BT on P.BusinessTypeId = BT.BusinessTypeId
    inner join LifeType LT on PLT.LifeTypeId = LT.LifeTypeId
    left join PensionType PT on P.PensionTypeId = PT.PensionTypeId
    left join SpecialDistributionSeller SDS on S.SellerId = SDS.SellerId
    left join ProductSubtype PS on P.ProductFamilyId = PS.ProductFamilyId and P.ProductId = PS.ProductId
where
    S.SellerId = @sellerIdParam
    and SDS.SpecialDistributionSeq IS NULL
    and P.StatusCd = 'O'
    and (P.AvailabilityCd = 'A'
        OR (P.AvailabilityCd = 'P' AND @posUserTypeId = '2')
        OR (P.AvailabilityCd = 'I' and @posUserTypeId != '2'))
    and exists (
        select ProductCommissionSeq
        from ProductCommission PC
            inner join CommissionTemplate CT on PC.CommissionTemplateSeq = CT.CommissionTemplateSeq
        where PC.ProductFamilyId = P.ProductFamilyId
            and PC.ProductId = P.ProductId
            and (CT.AvailabilityCd = 'A'
                or (CT.AvailabilityCd = 'P' and @posUserTypeId ='2')
                or (CT.AvailabilityCd = 'I' and @posUserTypeId !='2'))
            and (PC.DistributionId = S.DistributionId
                or PC.CommissionGroupSeq IN (select CommissionGroupSeq from CommissionGroupSeller where SellerId = @sellerIdParam))
    )

该查询包含两个VARCHAR类型参数：

• @sellerIdParam：长度为5
• @posUserTypeId：长度为2

异常现象 #

绝大多数参数组合下查询都能瞬间执行完成，仅在如下参数组合下触发System.Data.SqlClient默认30秒超时：

• @sellerIdParam = "RR39"
• @posUserTypeId = "1"
  而参数组合@sellerIdParam = "RR30"、@posUserTypeId = "1"时查询完全正常，可立即返回结果。
  初步判断为参数嗅探问题，尝试在查询末尾添加OPTION (RECOMPILE)提示后无改善，该方案此前处理同类问题均生效。

补充验证信息 #

• SSMS运行验证：两组参数在SSMS中均能瞬间执行完成，测试时使用如下参数声明语句：

DECLARE @sellerIdParam VARCHAR(4) = 'RR39'-- 或 'RR30';
DECLARE @posUserTypeId VARCHAR(1) = '1';

• 数据量对比：两组参数返回的结果均为100条左右，结构、内容几乎完全一致，无明显数据量差异。
• 代码排查：查询调用使用的C#通用方法已在上百个其他查询中稳定运行，且本查询其他参数组合均正常，暂排除代码问题，方法实现如下：

public DbDataReader ExecuteReader(string commandText, IDictionary<string, object> parameters)
{
    var sw = new Stopwatch();
    sw.Start();

    DbConnection conn = null;

    try
    {
        var result = this.Factory.CreateConnection();
        result.ConnectionString = this.ConnectionString;
        conn = result;
        var cmd = conn.CreateCommand();
        cmd.CommandText = commandText;
        foreach (var item in parameters)
        {
            var param = this.Factory.CreateParameter();
            if (param == null)
            {
                return null;
            }

            param.ParameterName = item.Key;
            param.Value = item.Value ?? DBNull.Value;
            cmd.Parameters.Add(param);
        }

        conn.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
    catch (Exception)
    {
        if (conn != null)
        {
           conn.Close();
           conn.Dispose();
        }

        throw;
    }
    finally
    {
        sw.Stop();

        Log.Debug(() => Helpers.SqlInfo(commandText, parameters, sw.Elapsed));
    }
}

• 执行计划：已获取两组参数对应的执行计划用于问题分析。

相关表结构 #

Seller表定义 #

CREATE TABLE [dbo].[Seller](
    [SellerId] [varchar](5) NOT NULL,
    [DistributionId] [varchar](2) NOT NULL,
    [InspId] [varchar](4) NULL,
    [AreaId] [varchar](7) NULL,
    [EmpId] [varchar](4) NULL,
    [TypeCd] [varchar](1) NULL,
    [SubdivisionCd] [varchar](6) NULL,
    [SellerNm] [varchar](70) NULL,
    [Addr1Txt] [varchar](30) NULL,
    [Addr2Txt] [varchar](30) NULL,
    [Addr3Txt] [varchar](30) NULL,
    [MasterSellerId] [varchar](4) NULL,
    [EmailAddrTxt] [varchar](100) NULL,
    [PosUwTypeCd] [varchar](1) NULL,
    [LastUpdDt] [datetime2](7) NULL,
    [LastUpdId] [varchar](8) NULL,
    [ESignatureAllowedCd] [varchar](1) NULL,
    [AppointmentDate] [datetime2](7) NULL,
    [Status] [varchar](1) NULL,
    [IsSiebelSeller] [varchar](1) NULL,
    [Addr4Txt] [varchar](30) NULL,
    [CrmPrimaryOwnerCd] [varchar](8) NULL,
    [InstAgentRoleCd] [varchar](3) NULL,
    [PriceMatchAllowedCd] [varchar](1) NOT NULL,
    [LooserValidationBusType] [varchar](15) NULL,
    [CommissionScreen2BusType] [varchar](15) NULL,
    [CentralBankAuthorisedInd] [varchar](1) NULL,
    [NewAmlAllowedCd] [varchar](1) NULL,
    [BusinessName] [varchar](150) NULL,
    [MagnumPureAllowedCd] [varchar](1) NULL,
 CONSTRAINT [Seller_PK] PRIMARY KEY CLUSTERED 
(
    [SellerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Seller] ADD  DEFAULT (NULL) FOR [ESignatureAllowedCd]
GO

ALTER TABLE [dbo].[Seller] ADD  DEFAULT ('Y') FOR [PriceMatchAllowedCd]
GO

ALTER TABLE [dbo].[Seller] ADD  DEFAULT (NULL) FOR [LooserValidationBusType]
GO

ALTER TABLE [dbo].[Seller] ADD  DEFAULT (NULL) FOR [CommissionScreen2BusType]
GO

ALTER TABLE [dbo].[Seller] ADD  DEFAULT (NULL) FOR [CentralBankAuthorisedInd]
GO

ALTER TABLE [dbo].[Seller]  WITH CHECK ADD  CONSTRAINT [SellerDistrib_FK] FOREIGN KEY([DistributionId])
REFERENCES [dbo].[Distribution] ([DistributionId])
GO

ALTER TABLE [dbo].[Seller] CHECK CONSTRAINT [SellerDistrib_FK]
GO

CommissionGroupSeller表定义 #

CREATE TABLE [dbo].[CommissionGroupSeller](
    [CommissionGroupSeq] [smallint] NOT NULL,
    [SellerId] [varchar](5) NOT NULL,
    [CreateId] [varchar](4) NOT NULL,
    [CreateDt] [datetime2](7) NOT NULL,
 CONSTRAINT [COMGRPSEL_PK] PRIMARY KEY CLUSTERED 
(
    [CommissionGroupSeq] ASC,
    [SellerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CommissionGroupSeller]  WITH CHECK ADD  CONSTRAINT [ComGrpSel_CommGroup_Fk] FOREIGN KEY([CommissionGroupSeq])
REFERENCES [dbo].[CommissionGroup] ([CommissionGroupSeq])
GO

ALTER TABLE [dbo].[CommissionGroupSeller] CHECK CONSTRAINT [ComGrpSel_CommGroup_Fk]
GO

ALTER TABLE [dbo].[CommissionGroupSeller]  WITH CHECK ADD  CONSTRAINT [ComGrpSel_Seller_Fk] FOREIGN KEY([SellerId])
REFERENCES [dbo].[Seller] ([SellerId])
GO

ALTER TABLE [dbo].[CommissionGroupSeller] CHECK CONSTRAINT [ComGrpSel_Seller_Fk]
GO

求助需求 #

目前始终无法将该异常参数组合下的查询耗时降低到合理范围，恳请提供可行的解决方案。

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