Stored procedure slow from application but fast in ssms

I will let @Erland Sommarskog answer to this question with authority but just quoting few lines from the same blog

Before I go into the real solutions, let me first point out that adding SET ARITHABORT ON to your procedure is not a solution. It will seem to work when you try it. But that is only because you recreated the procedure which forced a new compilation and then the next invocation sniffed the current set of parameters. SET ARITHABORT ON is only a placebo, and not even a good one. The problem will most likely come back. It will not even help you avoid the confusion with different performance in the application and SSMS, because the overall cache entry will still have ARITHABORT OFF as its plan attribute.

So, don't put SET ARITHABORT ON in your stored procedures. Overall, I strongly discourage from you using any of the SET commands that are cache keys in your code.

If you ask me personally this is a "good workaround" and works for most cases. If you have SQL Server 2016 or above using Query store you can force query to use plan which runs fast and that would not need ARITHABORT ON. Before that using plan guides is the solution to get over such things.

PS: For ordinary Joe this has mostly been the solution.

Hi @Surendra Adhikari ,

Agree with @Shashank Singh . And the reply is what I want to say.
I think the quote in the reply from @Erland Sommarskog 's article has clearly explained your doubts.

Note:
The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF might receive different query plans, making it difficult to troubleshoot poorly performing queries. That is, the same query might execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio, always match the client ARITHABORT setting.

And more information : set-arithabort-transact-sql

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hi @Surendra Adhikari ,

Is the reply helpful?

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.