Thursday 10 September 2009

Setting ARITHABORT can affect query performance

MSDN documentation defines SET ARITHABORT as follows:
"Terminates a query when an overflow or divide-by-zero error occurs during query execution.
If SET ARITHABORT is ON, these error conditions cause the query or batch to terminate. If the errors occur in a transaction, the transaction is rolled back. If SET ARITHABORT is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation."
Note that this setting can have significant effect on query performance. It is quite possible for a query to run very quickly in SQL Management Studio but very slowly when run from code (e.g. ADO.Net). A possible explanation is that ADO.Net runs with ARITHABORT set to OFF whereas SQL Management Studio runs with it ON.

If a query is running very slowly from code it is worth checking if ARITHABORT should be set to on.