I figured out a parameter sniffing issue and after doing experiments have decided to introduce a code (query) change in app code that uses optimize for unknown hint.

Now, I need to make sure the situation won't come up again until this code change is completed and deployed. So I thought about temporarily creating a sql agent job that executes the relevant query with the parameters required to create an accepatable plan for every kind of parameter value like:

...OPTION (OPTIMIZE FOR (@xyz = 123));

The question is, will execution of this plan in an agent job after any index & statistics maintenance jobs even hit the same query, if the same query coming from the app's code does not yet have the query hint inside of it? If the agent job's queryplan won't match the original query because of the hint, there is not use of it...

  • 1
    You may want to consider plan guides. That being said: is optimize for unknown the first thing you tried? Because there are a lot of downsides. – Erik Darling Mar 29 '17 at 15:09
  • I considered other solutions like OPTION RECOMPLIE or manipulating the query before execution to cause a different plan for special customers etc. But this solution in this particular case (query) works like a charm, at least on one of my systems, the "unknown" plans are tiny less efficient than the plans complied for specifig data/parameters but compared to the bad parameter sniffing-issued plans they are fantastic. – Magier Mar 29 '17 at 15:15
  • I'd probably want to keep testing that. Best of luck! – Erik Darling Mar 29 '17 at 15:17
  • @sp_BlitzErik Very interesting! Do I have to leave away the sp_executesql command in the plan guide statements? Can't manage to create one using it. Fear it does not work without it... – Magier Mar 29 '17 at 15:34
  • I don't believe so, no. – Erik Darling Mar 29 '17 at 15:36

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.