I have a ASP.NET MVC 5 web application that has executes a relavatively simple parameterised dynamic query on a SQL Server 2016 database.
The query:
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))
)
This query has two parameters:
@sellerIdParam@posUserTypeId
These are both VARCHAR data types (5) and (2) length respectively.
For almost all parameter combinations this query completes execution almost instantly. However we have an issue where the combination:
@sellerIdParam- "RR39"@posUserTypeId- "1"
Is timing out on execution (default of 30 seconds using System.Data.SqlClient).
The equivalent query with parameters combination:
@sellerIdParam- "RR30"@posUserTypeId- "1"
Works absolutely fine and returns immediately.
This seems like a classic Parameter Sniffing (spoofing) issue. The best defence for this in this situation from my analysis is amending the OPTION (RECOMPILE) hint to the end of the query. I have tried this and it seems to make no difference. This has worked in the past for similar issues but this time it seems to not make a difference.
Any help would be appreciated as for the life of me I cannot get this query to complete execution in an appropriate amount of time.
EDIT
To answer some questions from the comments:
Have you attempted the same query in SSMS? Yes this completes execution almost immediately with either set of parameters. The exact same query is used with:
DECLARE @sellerIdParam VARCHAR(4) = 'RR39'-- or 'RR30';
DECLARE @posUserTypeId VARCHAR(1) = '1';
Prepended to the query.
Does RR39 have more / less data in the database than RR30? No there is no difference. Both queries return 100 records as well with almost identical data.
C# code (I don't think this is the issue as this same code is used for hundreds of other working queries and works for the same query in most parameter combinations):
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));
}
}
Execution Plan for 'RR39'/'1': https://www.brentozar.com/pastetheplan/?id=SJ8jaNyXt
Execution Plan for 'RR30'/'1': https://www.brentozar.com/pastetheplan/?id=rJquREJ7F
Seller table definition:
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 table definition:
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