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

By: Ranga Babu   |   Updated: 2016-06-13   |   Comments (7)   |   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:

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

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

View all my tips