3

This question already has an answer here:

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.

marked as duplicate by Paul White sql-server Nov 16 '16 at 21:30

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Parameter sniffing would be my guess too. Could you use either optimize for or recompile? If the procedure is executed often, recompile of course increases cpu usage. – James Z May 20 '16 at 10:55
  • The query is run very often with a wild array of different parameter options. It is the search component of an application processing system... I was thinking about optimize for. Can I ask, if the proc has default parameter values, should it not be optimized already for those defaults? – Molenpad May 20 '16 at 11:02
  • 3
    It will be optimized for whatever are the parameter values for the first call when the plan is made -- so if you know good values (that will be good also in the future), you could use those. If the procedure contains stuff like value = @variable or @variable is null you might want to consider changing it into dynamic SQL with sp_executesql. Or if the good plan depends on parameter values, you might want to create several procedures that are called based on parameter values (e.g. is a date range short or long) – James Z May 20 '16 at 11:07
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.