Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (2)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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 Update:


signup button

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



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!!

 


Learn more about SQL Server tools