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