EF Core Parameter Sniffing in SQL Server

I've been watching Brent Ozar's training videos (the SQL Guru for me) and he talks about parameter sniffing and say EF does this, but for the life of me I can't get an example working. I was expecting to see parameters but it just creates the SQL like this with just equality and not @p1, @p2.

SELECT [p].[Id], [p].[Body], [p0].[Type]  FROM [Posts] AS [p]  INNER JOIN [PostTypes] AS [p0] ON [p].[PostTypeId] = [p0].[Id]  WHERE ([p].[PostTypeId] = 6) AND ([p].[CreationDate] >= '2011-01-01T00:00:00.000')  

I created the DBContext from scaffolding his StackOverflow2013 database and I created a foreign key on the post type to get some sort of JOIN.

Does anyone know how I can get an example of parameter sniffing using EF as this just creates a new query plan every time?

If I call stored proc then I can get parameter sniffing.

My C# Code is as follows

  var result = ctx.Posts      .Include(x => x.PostType)      .Where(x => x.PostTypeId == 6 && x.CreationDate >= new DateTime(2013, 01, 01))          .Select(x => new {             Id = x.Id,             Body = x.Body,              Type = x.PostType.Type           }).ToList();  

https://stackoverflow.com/questions/66825104/ef-core-parameter-sniffing-in-sql-server March 27, 2021 at 06:34AM