SQL Server stored procedure runs fast in SSMS and slow in application

By:   |   Comments (9)   |   Related: > Performance Tuning


Our users complained about slowness in a few areas of our application. We ran Profiler and found that a few SQL Server stored procedures were taking more than 6-10 seconds. I manually ran those same stored procedures in SQL Server Management Studio (SSMS) and they executed in less than second. Why are there differences?


First thing that came into my mind was the execution plan of the SQL Server stored procedure (MyProc). We can check the execution plan using the below query.

select o.object_id, s.plan_handle, h.query_plan 
from sys.objects o 
inner join sys.dm_exec_procedure_stats s on o.object_id = s.object_id
cross apply sys.dm_exec_query_plan(s.plan_handle) h
where o.object_id = object_id('MyProc')

Here we can see there two different execution plans for the same procedure.

Two different SQL Server execution plans for the same procedure

Now let's compare the attributes for these two plans by using the below query with the plan_handle values from above.

select * from sys.dm_exec_plan_attributes (0x0500060085C0FC594001ED010B0000000000000000000000)
select * from sys.dm_exec_plan_attributes (0x0500060085C0FC5940415E46040000000000000000000000)

We can see the attribute set_options is different for the two plans.

SQL Server attribute set_options is different for the two plans

Analyzing the SET OPTIONS

Refer to this MSDN article for the below table:



Parallel Plan 2
NoBrowseTable Indicates that the plan does not use a work table to implement a FOR BROWSE operation. 512
TriggerOneRow Indicates that the plan contains single row optimization for AFTER trigger delta tables. 1024
ResyncQuery Indicates that the query was submitted by internal system stored procedures. 2048
LanguageID 65536
UPON Indicates that the database option PARAMETERIZATION was set to FORCED when the plan was compiled. 131072
ROWCOUNT - Applies To: SQL Server 2012 to SQL Server 2016 262144

Our first plan has a set_options value of 249. By using the above table we have to subtract the immediate value which is less than or equal to the value.  Here is example of how to do this:

  • 249 - 128 (ANSI_NULL_DFLT_ON) = 121
  • 121 - 64 (QUOTED_IDENTIFIER) = 57
  • 57 - 32 (ANSI_NULLS) = 25
  • 25 - 16 (ANSI_WARNINGS) = 9
  • 1 - 1 (ANSI_PADDING) = 0

So we have these options enabled for the first plan with a set_option value of 249.

  • ANSI_NULLS (32)
  • 128 + 64 + 32 + 16 + 8 + 1 = 249

We have these options enabled for the second plan with a set_option value of 4345.

  • ARITH_ABORT (4096)
  • ANSI_NULLS (32)
  • 4096 + 128 + 64 + 32 + 16 + 8 + 1 = 4345

The difference in two plans was ARITH_ABORT.

I ran the procedure in SSMS with the below hints to mimic the behavior of both query plan settings.  The one with ARITHABORT ON executes in 1 second and the one with ARITHABORT OFF executes in 10 seconds. By default the queries executed in SSMS had ARITHABORT ON and the queries that come from application have ARITHABORT OFF. This is what caused the difference in execution time.

EXEC MyProc  


You can check the SSMS SET options by going to Tools > Options > Query Execution > SQL Server > Advanced.


Although this may not be the only reason why queries run faster in SSMS and slower in the application, it was the case in my scenario.  Next time you have differences take a look at the SET options to see if this is causing the issue.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ranga Babu Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, July 19, 2023 - 3:40:41 AM - Praveen Back To Top (91406)
I have tried to exec proc in that way, but for 7lakh records in both options its getting only 2mins to load.
actually I have proc which runs in SSMS in 2mins, but through ADF DF when try to exec same proc its taking >2 hours to run. kindly assist if you came across with that kind of issue.

Friday, July 7, 2023 - 11:45:43 AM - Wozzarvl Back To Top (91369)
Nice article, It help me a lot to understand why is slow my storedProceduce I combined it with the fix of parameter snifffing and now is running so fast

Monday, October 12, 2020 - 6:53:10 AM - Ajay Back To Top (86629)
Awesome article Ranga, It resolved our issue. Thank you so much...

Tuesday, July 16, 2019 - 12:02:14 AM - Ranga Back To Top (81758)

Hi David Huggett,

That may be the case. a new plan is created and the stored procedure is compiled with new parameters. may be a case of parameter sniffing.



Wednesday, June 26, 2019 - 5:42:57 AM - David Huggett Back To Top (81589)

Please see http://www.sommarskog.se/query-plan-mysteries.html - particularly this text from section 5.1:

"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."

Tuesday, January 23, 2018 - 7:08:17 AM - Ranga Back To Top (75018)

@Matheus: Good to hear that your issue is resolved

Monday, January 22, 2018 - 5:38:13 PM - Matheus Back To Top (75008)

You have no idea how helpful this was. A stored procedure decided to take forever to execute in a production database and ARITHABORT was to blame for it, now solved


Thursday, October 5, 2017 - 5:37:17 PM - Joshua Cargile Back To Top (66945)


GREAT stuff - mystery solved! I too found that ARITHABORT OFF was the culprit with one of my execution plans. I set ARITHABORT ON at the database level and no longer have this problem. If anyone else needs to do that, the command is: ALTER DATABASE [yourdbname] SET ARITHABORT ON WITH NO_WAIT;. Beware this will take a little while if you have a lot of sprocs/UDFs, as (I believe) SQL Server will have to recreate all of your execution plans.

Thursday, December 1, 2016 - 9:53:28 AM - Ranga N Back To Top (44873)

Awesome tip Ranga :)

The way you explained was nice!!


get free sql tips
agree to terms