SQL Query Runs Fast in SSMS but Slow in Your App? Here's Why!

ARITHABORT

The main culprit or I would say rescuer is a SQL Server setting called ARTIHABORT! ARITHABORT is a SQL Server session-level setting that controls how SQL Server handles arithmetic errors, such as division by zero or overflow errors.

• When ARITHABORT = ON, SQL Server immediately terminates a query if an arithmetic error occurs.
• When ARITHABORT = OFF, SQL Server continues execution and returns a warning instead of terminating the query.

Based on ARITHABORT ON or OFF, SQL Server generates different plans. A suboptimal execution plan be chosen when running from the application, causing a performance difference. Also, with ARITHABORT OFF, SQL Server may not use the same indexed path as with ARITHABORT ON, leading to slower performance. As ARITHABORT controls the arithmetic operation flow, it significantly affect the execution plan path.

In SSMS, it is set to ON by default. But from application, we need to explicitly set it to ON to get the same query execution time. From ADO.NET, you can set it like below before executing any query:

using (SqlCommand comm = new SqlCommand("SET ARITHABORT ON", connection))
{
    comm.ExecuteNonQuery();
}

Or, set it at the beginning of any Stored Procedure as

SET ARITHABORT ON;

This will get you the same execution time in all cases. But then again, SQL Server or any other relational database is an overly complex and vast arena to handle. Sometimes, you may have to check with OPTION (RECOMPILE) to get all environments a fresh set of plans. Thing about parameter sniffing as well.