How to make SqlConnection to use "SET ARITHABORT ON" by default?

5
votes

Recently, we identified that one of our poor performing page was due to the difference in ARITHABORT option setting between SQL Server Instance and SQL Client (i.e., ADO.NET). We could resolve this issue by executing a following command as soon as SQLConnection gets opened -

SqlCommand comm = new SqlCommand("SET ARITHABORT ON", objSqlConn);
comm.ExecuteNonQuery();

There are many articles over the internet which says when the query takes long to execute from application, but executes quickly in SSMS then it is most probably the issue of poor indexing, parameter sniffing or ARTHABORT option difference. In my case, it was the third one which I could reconfirm by checking the query execution plan as well.

Though the issue is resolved, I am curious to know why I need to run this command explicitly in order to make the query performance in sync with SQL server.

Also, if it is a known thing since years, then Microsoft must have thought about setting this option to ON by default for SqlConnection. After all, SQL Server and .NET client are their own products.

Any idea of how to make SET ARITHABORT ON by default while executing queries using ADO.NET?

1
c#.netsql-serverperformanceado.net
Matching the settings on the server has two possible effects - either you start using a better query plan that was cached previously when executed by something else with ARITHABORT ON (such as ssms) or if there was no matching plan cache for ON you are causing the creation of a new, better one. See sommarskog.se/query-plan-mysteries.html – Alex K.

1 Answers

1
votes

arithabort should be on by default unless someone has been changing things within the server instance. So the first thing to do is ask your dba. And note that this is a connection setting, so setting it when the connection is opened is a more effective method (which implies providing your own open method).

All of this is covered here