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...