Disable parameter sniffing for better performance - good idea?

Question

I have an optimized query in SQL Server 2012 that takes consistently ~1-3 seconds to run.

It works fast when I run it in SSMS, but when application does it (through sp_executesql), it is really slow, about 13 minutes for larger data.

After some digging and reading Slow in the Application, Fast in SSMS?, I concluded that there is a trouble with parameter sniffing (there is one parameter).

I disabled it with OPTION (QUERYTRACEON 4136) and it works fast with sp_executesql.

Is it a good idea (should I keep the option to disable parameter sniffing) or should I tune query so it works with sp_executesql and parameter sniffing?

Answer 1

Query/index tuning is always a good idea but independent of parameter sniffing. Ideally, every execution plan would optimal and reusable but trade-offs must be made when the optimal plan varies depending on the supplied values (non-trivial queries).

You could use the OPTIMZIE FOR UNKNOWN query hint (or local variables) for non-trivial queries so that the plan is generated based on average density estimates rather than the stat histogram of the provided parameter value. This will generate a more stable plan that can be cached and reused. However, this compromise plan could be less efficient for certain parameter values and very costly is some cases. Another option is the OPTIMZIE FOR hint with specific values, which is appropriate when the plan generated for unknown values isn't satisfactory.

OPTION RECOMPLE avoids plan caching and the risk of reusing an inappropriate plan at the cost of compilation upon each execution. That is not a concern for infrequently executed queries but can be very expensive for an OLTP workload where the query is executed many times per second. Consequently, you need to consider your workload, variation in parameter values, and execution frequency to determine the optimal approach for your workload.