Today I had the opportunity to learn more about the database connection settings and how they impact query performance and behavior.
When you connect to SQL Server there are a number of connection settings. A key setting for the performance of your application is Arithabort . Arithabort determines the behavior of SQL Server when it encounters different mathematical errors such as divide by zero, operation against a null value, and casting data to a variable of smaller capacity such as a cast from Int to TinyInt.
All of these events are handled differently based on the way ArithAbort is configured.
You configure ArithAbort with the TSQL statement: SET ARITHABORT ON/OFF
Today I was trying to figure out why a query ran in SQL Server Management Studio in less than a second, and the same query when run through a C# application using ADO.Net always timed out. I was able to identify the culprit with a google search asking why SSMS returned query results when ADO would not. They suggested ArithAbort was not configured the same.
I wrote a query to get all the configuration settings for the current connection.
SELECT
SESSIONPROPERTY ('ARITHABORT') as ArithAbort
,SESSIONPROPERTY ('ANSI_NULLS') as ANSI_NULLS
,SESSIONPROPERTY ('ANSI_PADDING') as ANSI_PADDING
,SESSIONPROPERTY ('ANSI_WARNINGS') as ANSI_WARNINGS
,SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL') as CONCAT_NULL_YIELDS_NULL
,SESSIONPROPERTY ('NUMERIC_ROUNDABORT') as NUMERIC_ROUNDABORT
,SESSIONPROPERTY ('QUOTED_IDENTIFIER') as QUOTED_IDENTIFIER
I then ran this query from SSMS and ADO.net. Comparing the results I found that SQL Server had ArithAbort set to true, and ADO had it set to false. Changing ArithAbort in SQL Serverto false made the query timeout. Setting ArithAbort to true in ADO caused the query to complete in under a second.
The library used to connect to sql server may impact the different connection configuration settings. Take a look at http://msdn.microsoft.com/en-us/library/ms175088.aspx for more information regarding how the connection settings are established.
It turns out this is an old song, much written about, with many postings found in a google search. So, once again the old dog has learned a new trick. Just a final note, if ArithAbort is not set to true there are a lot of things in your database that either won’t work, or work poorly. It would be worth your time to follow up on this setting.
Cheers,
Ben