1

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

screenshot of execution plan in SSMS

2 Answers 2

4

Disabling parameter sniffing is not the same thing as option recompile. Disable parameter sniffing means that the optimizer "don't know" the values of the parameters and either use density in the statistics or the hardcoded values for selectivity (something like 30% for BETWEEN etc. - whatever the percentages actually are for each operator).

2
  • Thanks for the explanation, and what about running the query right after I clear the entire plan cache? I keep getting the bad plan, only using recompile hint gets the good plan and I'm the only one using the environment
    – PhillysDBA
    Jun 15, 2021 at 16:53
  • Why you get the bad plan after clearing out the cache? We can't say without a lot more info (like the proc code and actual exec plan, preferably for both good and bad plan). If you still have parameter sniffing turned off, then that mystery is solved. If not, then perhaps sniffing the values don't turns out to be good for you. For instance if you modify a value of a parm in the proc code, then OPTION(RECOMPILE) will pick up that modified value while without it you get what you passed into the proc. Etc. I think that Josh covered most in his reply. Jun 16, 2021 at 6:53
2

To answer the question from the title: no, you won't get parameter sniffing after you've disabled it using the database scoped configuration option:

ALTER DATABASE SCOPED CONFIGURATION 
SET PARAMETER_SNIFFING = OFF;

However, that doesn't mean you'll get a good execution plan. It means that SQL Server will use the density vector or a fixed "guess," depending on the operator being used (equals, greater than, between, etc) rather than sniffing the parameters and compiling a plan based on that value. A lot of detail on that can be found in Erik Darling's post Yet Another Post About Local Variables.

You mentioned that you still get the bad plan after clearing the plan cache for the entire instance, but that you get a good plan with OPTION (RECOMPILE) query hint. The most likely reason for this is that your query is getting the additional advantages of the parameter embedding optimization with the hint. This is described in detail on Paul White's article Parameter Sniffing, Embedding, and the RECOMPILE Options:

Query parameters are replaced with literal constant values during query parsing.

The parser is capable of surprisingly complex simplifications, and subsequent query optimization may refine things even further.

Based on the screenshot of the plan fragment you included in the question, it's hard to say exactly what good that replacement process is doing for you. If you want suggestions on how to improve the query without OPTION (RECOMPILE), you would probably want to ask another question with a lot more detail about the schema, indexes, execution plans, etc (per Asking query performance questions).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.