Can parameter sniffing happen even after disabling it?
1
vote
2
answers
742
views
I'm having a very strange issue with an EF linq query, the query runs a dynamic sql, something like this:
EXEC sp_executesql N'select 1 from X',N'@parameter1 int',@parameter1 = 123
The query was taking forever to finish (over an hour...) so my first test was to run it manually with an option (recompile)
hint and it ran instantly so to me that would be a case of parameter sniffing, but the strange thing is that I did the test of clearing the entire instance plan cache and I keep getting terrible estimates and the query takes forever, even disabling parameter sniffing on SQL Server I keep getting the same problem.
Not sure if it might be somehow related but the query is using a view and the view is using the FOR XML PATH
function to extract some data so maybe that could be affecting the estimates?
Any ideas on what could be causing that strange behavior? Here is a extract of the plan where the estimates go way off, even after updating statistics and clearing the plan from cache I keep getting the same problem, the only thing that fixes the issue is the recompile hint

Asked by PhillysDBA
(11 rep)
Jun 15, 2021, 02:52 PM
Last activity: Jun 15, 2021, 11:58 PM
Last activity: Jun 15, 2021, 11:58 PM