.Net Application Using Inefficient Query Plan - caused by ARITHABORT setting? 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.
You are probably running into parameter sniffing issues. The fact that you execute the query with a different ARITHABORT setting makes SQL Server create a new plan and not reuse the existing plan as that setting is part of the cache key.

Have a look at Slow in the application, fast in SSMS, it has a lot of information such as this:


  We have seen that there is a large number of different cache keys, so
  potentially there can be very many plans for a single stored procedure. But we have also learnt that many of the SET options that
  are cache keys are legacy options that you should never change.
  
  In practice, the most important SET option is ARITHABORT, because the
  default for this option is different in an application and in SQL
  Server Management Studio. This explains why you can spot a slow query
  in your application, and then run it at good speed in SSMS. The
  application uses a plan which was compiled for a different set of
  sniffed parameter values than the actual values, whereas when you run
  the query in SSMS, it is likely that there is no plan for ARITHABORT
  ON in the cache, so SQL Server will build a plan that fits with your
  current parameter values.


If you can change the stored procedure I would try to fix it there rather than with plan guides. The possible solutions are also explained in the linked article but basically you have these options:


OPTION (RECOMPILE) / WITH RECOMPILE
Add the "optimal" index
Drop the index you don't want the plan to use
Use an index hint to force use of any of the other indexes.
The query hint OPTIMIZE FOR.
Copy the parameters to a local variable.
Change the application behaviour.