Somewhat similar to this -- Why does sp_executesql use a different query plan? -- i.e. a C# app, using EF to SQL, but with a much simpler query.

Here's the sample, as captured by Profiler and XEvents:

exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[WidgetID] AS [WidgetID], 
    [Extent1].[WidgetName] AS [WidgetName], 
    [Extent1].[WidgetCode] AS [WidgetCode], 
    [Extent1].[CreatedDate] AS [CreatedDate]
FROM [dbo].[WidgetHeader] AS [Extent1]
WHERE ([Extent1].[WidgetCode] = @p__linq__0)
    AND ([Extent1].[CreatedDate] IS NULL)'
, N'@p__linq__0 varchar(8000)'
, @p__linq__0 = '12345'

The symptoms are "sporadically slow performance" -- i.e. sometimes it runs pretty fast, but sometimes it's slow, and we've coordinated the slowness with the end-user experience in the corresponding website.

One issue is that the field [WidgetCode] is a varchar(20), but as you can see EF passes it down as a varchar(8000). At least it's not assuming Nvarchar (Unicode) when it's not. But I don't know enough about EF to know if this is normal and inconsequential -- it seems like it shouldn't matter.

I've monitored the plan cache and there's no plan variation -- it seems like we're only getting one consistent plan. Yet, we still see slow runs and fast runs corresponding with slow or faster performance of the application.

Does this sound like parameter sniffing, or is that "barking up the wrong tree"?

Other notes:

  • SQL 2016 Standard, SP1
  • Table [WidgetHeader] has 100k rows
  • Column [WidgetCode] has about 40k distinct values, so "selectivity" is 40%.
  • We have a nonclustered index on [WidgetCode] and [CreatedDate] which INCLUDEs all the columns in this SELECT query, so the plan is a simple index seek.

If not parameter sniffing, what kind of issues should I try looking for?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.