3

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.

Molenpad
  • 1,814
  • 2
  • 23
  • 41

1 Answers1

6

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:

  1. OPTION (RECOMPILE) / WITH RECOMPILE
  2. Add the "optimal" index
  3. Drop the index you don't want the plan to use
  4. Use an index hint to force use of any of the other indexes.
  5. The query hint OPTIMIZE FOR.
  6. Copy the parameters to a local variable.
  7. Change the application behaviour.
Tom V
  • 15,752
  • 7
  • 66
  • 87