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


By:   |   Updated: 2016-06-13   |   Comments (6)   |   Related: More > Performance Tuning

Azure SQL Database Performance Monitoring and Optimization

Free MSSQLTips Webinar: Azure SQL Database Performance Monitoring and Optimization

Many organizations have already deployed or plan to deploy databases in the cloud, both in Infrastructure as a Service (IaaS) and Platform as a Service (PaaS) implementations. People who move to the cloud might think everything's completely hands-off, but monitoring and optimization is even more critical because you share resources and have no control over the infrastructure. So, how do you address your SQL Server performance challenges?


Problem

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?

Solution

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:

Option

Value

ANSI_PADDING 1
Parallel Plan 2
FORCEPLAN 4
CONCAT_NULL_YIELDS_NULL 8
ANSI_WARNINGS 16
ANSI_NULLS 32
QUOTED_IDENTIFIER 64
ANSI_NULL_DFLT_ON 128
ANSI_NULL_DFLT_OFF 256
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
ARITH_ABORT 4096
NUMERIC_ROUNDABORT 8192
DATEFIRST 16384
DATEFORMAT 32768
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
  • 9 - 8 (CONCAT_NULL_YIELDS_NULL) = 1
  • 1 - 1 (ANSI_PADDING) = 0

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

  • ANSI_NULL_DFLT_ON (128)
  • QUOTED_IDENTIFIER (64)
  • ANSI_NULLS (32)
  • ANSI_WARNINGS (16)
  • CONCAT_NULL_YIELDS_NULL (8)
  • ANSI_PADDING (1)
  • 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_NULL_DFLT_ON (128)
  • QUOTED_IDENTIFIER (64)
  • ANSI_NULLS (32)
  • ANSI_WARNINGS (16)
  • CONCAT_NULL_YIELDS_NULL (8)
  • ANSI_PADDING (1)
  • 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.

SET ARITHABORT ON 
EXEC MyProc  

SET ARITHABORT OFF 
EXEC MyProc

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

Conclusion

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


Last Updated: 2016-06-13


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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.

Regards,

Ranga


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

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

@Matheus: Good to hear that your issue is resolved


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

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 05, 2017 - 5:37:17 PM - Joshua Cargile Back To Top

 

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 01, 2016 - 9:53:28 AM - Ranga N Back To Top

Awesome tip Ranga :)

The way you explained was nice!!

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools