One of our applications is causing a problem in that there is a stored procedure that returns fine in SSMS, in under 1 sec, but in the application its taking anything up to 10 minutes, depending on the parameters used. Any combination of parameters work fine in SSMS and the execution plan looks good to me.
However, when profiled the Application is clearly using a different, less efficient plan. The SqlClient used to connect has the ARITHABORT setting, set to OFF, which when replicated in SSMS I run into the same performance issues.
I'm guessing ARITHABORT OFF will not allow the optimizer to use the cached plan? Or does it run a separate plan?
According to the Apps guys, there is no way to change the SQLClient connection to use ARITHABORT ON
I'm guessing the ARITHABORT setting is a bit of a misnomer here, and it's actually a parameter sniffing thing caused by the optimizer not using the good plan?
I need to force it to use a good plan regardless, so how best to address the issue? Do I create a plan guide or optimize the stored proc in some way?
This is SQL Server 2008R2 SP2.