First of all, I know a lot has been said about setting the ARITHABORT option to on or off. I just cannot find the answer in my case.
Situation:
We have a webshop with a product database (products, catalogs, features etc..). The query, in a stored procedure, to get the product list is not a simple one. The stored procedure used to get this list (and other metadata) contains multiple select queries so
it returns multiple datatables (reader.NextResult()). One datatable for products, one datatable for the product filters and two more.
This query worked fine, took half of a second to complete. But then it stopped working in our ASP.NET web application, all I got was a timeout. Using the SQL Profiler I checked for the statement used by the web application. I copied it to Management Studio
and to my suprise... no timeout. But the web application was still timing out.
I search the web for related problems and some websites told me to use the "SET ARITHABORT ON" option, because SSMS does the same trick. So I placed this in our code and everything was working fine again. Website returned the results in a few seconds. Made
my a happy man.
After a few days everything stopped working again. My "solution" was removing "SET ARITHABORT ON", so essentially turning it off again. Everything was working fine as before. Few days later, timeouts again. Turning ARITHABORT to ON was again the fix for this
problem. Of course it did not stop here because after a while I had to set the option to off after more returning timeouts.
Code did not change, neither did the stored procedure. Only the data in the database. I am completely lost.
About the stored procedure: it contains some left and inner joins with about six tables and some averages and sums.
When debugging, I see the database returns almost all records with a second, and then it is just waiting for the last ones. So just setting a higher timeout would not do the trick. Because most of the data is returned instantly.
What do I miss? What do I not know about returning data from MSSQL?
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 If neither SET ARITHABORT nor SET ARITHIGNORE is set, Microsoft® SQL Server™ returns NULL and returns a warning message after the query is executed.
When an INSERT, DELETE or UPDATE statement encounters an arithmetic error (overflow, divide-by-zero, or a domain error) during expression evaluation when SET ARITHABORT is OFF, SQL Server inserts or updates a NULL value. If the target column is not nullable,
the insert or update action fails and the user receives an error.
If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.
The setting of SET ARITHABORT is set at execute or run time and not at parse time.
SET ARITHABORT must be ON when creating or manipulating indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more
information about required SET option settings with indexed views and indexes on computed columns, see Considerations When Using SET Statements in
SET.
So you can try to set the SqlCommand's TimeOut to a larger number having a try with that.
When I do sp_recompile on this stored procedure, I get this weird problem again. Then, when I alter the stored procedure by only adding a space, timing is fine again.
Ascathon
0 Points
5 Posts
Unexplainable timeouts and ARITHABORT
Jan 20, 2013 04:48 PM|LINK
First of all, I know a lot has been said about setting the ARITHABORT option to on or off. I just cannot find the answer in my case.
Situation:
We have a webshop with a product database (products, catalogs, features etc..). The query, in a stored procedure, to get the product list is not a simple one. The stored procedure used to get this list (and other metadata) contains multiple select queries so it returns multiple datatables (reader.NextResult()). One datatable for products, one datatable for the product filters and two more.
This query worked fine, took half of a second to complete. But then it stopped working in our ASP.NET web application, all I got was a timeout. Using the SQL Profiler I checked for the statement used by the web application. I copied it to Management Studio and to my suprise... no timeout. But the web application was still timing out.
I search the web for related problems and some websites told me to use the "SET ARITHABORT ON" option, because SSMS does the same trick. So I placed this in our code and everything was working fine again. Website returned the results in a few seconds. Made my a happy man.
After a few days everything stopped working again. My "solution" was removing "SET ARITHABORT ON", so essentially turning it off again. Everything was working fine as before. Few days later, timeouts again. Turning ARITHABORT to ON was again the fix for this problem. Of course it did not stop here because after a while I had to set the option to off after more returning timeouts.
Code did not change, neither did the stored procedure. Only the data in the database. I am completely lost.
About the stored procedure: it contains some left and inner joins with about six tables and some averages and sums.
When debugging, I see the database returns almost all records with a second, and then it is just waiting for the last ones. So just setting a higher timeout would not do the trick. Because most of the data is returned instantly.
What do I miss? What do I not know about returning data from MSSQL?
(MSSQL 2008, .NET 3.5)
Ascathon
0 Points
5 Posts
Re: Unexplainable timeouts and ARITHABORT
Jan 21, 2013 06:25 AM|LINK
The Messages tab in SSMS gives the following warning: Warning: Null value is eliminated by an aggregate or other SET operation.
Could this be part of the problem?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Unexplainable timeouts and ARITHABORT
Jan 22, 2013 01:45 AM|LINK
Hi,
As far as I see, From MSDN:
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 If neither SET ARITHABORT nor SET ARITHIGNORE is set, Microsoft® SQL Server™ returns NULL and returns a warning message after the query is executed.
When an INSERT, DELETE or UPDATE statement encounters an arithmetic error (overflow, divide-by-zero, or a domain error) during expression evaluation when SET ARITHABORT is OFF, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.
If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.
The setting of SET ARITHABORT is set at execute or run time and not at parse time.
SET ARITHABORT must be ON when creating or manipulating indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see Considerations When Using SET Statements in SET.
So you can try to set the SqlCommand's TimeOut to a larger number having a try with that.
Ascathon
0 Points
5 Posts
Re: Unexplainable timeouts and ARITHABORT
Jan 23, 2013 06:59 AM|LINK
I got a lead.
When I do sp_recompile on this stored procedure, I get this weird problem again. Then, when I alter the stored procedure by only adding a space, timing is fine again.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Unexplainable timeouts and ARITHABORT
Jan 23, 2013 07:53 AM|LINK
Can you show us your solved solution?
Ascathon
0 Points
5 Posts
Re: Unexplainable timeouts and ARITHABORT
Jan 23, 2013 09:17 AM|LINK
At the moment it's only a lead to find a solution. So far, I haven't one yet. I will certainly tell when I do have a solution.
What does the ALTER statement do, even if I don't change the sproc code. Clear metadata? Refresh/drop execution plans?
Should I look into parameter sniffing?
It only tell's me it doesn't have anything to do with ARITHABORT, ARITHIGNORE or any of those options.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Unexplainable timeouts and ARITHABORT
Jan 24, 2013 12:26 AM|LINK
Alter statement will let you change your existing SP, whether you change it or not.
Ascathon
0 Points
5 Posts
Re: Unexplainable timeouts and ARITHABORT
Jan 24, 2013 12:58 PM|LINK
I understand. ;)
I mean, does it do anything to statistics and query plans.
For now, I added WITH RECOMPILE, no problems so far. Not happy with this solution, but the business continues.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Unexplainable timeouts and ARITHABORT
Jan 24, 2013 11:55 PM|LINK
No.