SQL Server stored procedure runs fast in SSMS and slow in application
By: Ranga Babu | Comments (9) | Related: More > Performance Tuning
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.

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.

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
- For more details about ARITHABORT please check this MSDN article SET ARITHABORT (Transact-SQL)
- It is not only SET options, but there might be several reasons for queries executing fast in SSMS and slow from an application. I found a great article by Erland Sommarskog which explains other possibilities of this problem. Refer this link: Slow in the Application, Fast in SSMS? Understanding Performance Mysteries
About the author

View all my tips