Grant Fritchey (9/29/2016)
Sounds like you're doing a good job. Keep it up. Focus on the metrics to drive your decisions. Take a look at Gail Shaw's blog post on "catch-all" queries. That will help in your situation.
Thanks.. it means a lot coming from you 🙂
I already took a look at Gail post a few year ago keep checking for an alternative to dynamic SQL but so far nothing can actually replace it...
There is the problem of parameter sniffing and I had my problems... A query that took 3secs to run when using OPTION RECOMPILE took only 10ms.... I turned on parameterization forced but the memory that the plans took was just too much for the server... so I when back to OPTION RECOMPILE on dynamic SQL...
I'm hopping SQL 2016 with the parameter sniffing database configuration can solve this problem... or in the end, what SQL does is add the OPTION RECOMPILE at the end of every query execution 😀 (just being mean here 🙂 ).
Regards,
Pedro