Risks of changing to ARITHABORT ON

Question

I'm working with a vendor with the arrangement that they provide the core application, and I can build my own extensions as long as I don't modify the core application. It's built in ColdFusion connecting to a SQL Server 2005 database.

Some of the reports I've built depend on views using functions computed from the core tables, and the reports are getting very slow as the tables get larger. To speed up the reports, I want to use indexed views. But after creating an indexed view in my test environment, the core application could no longer insert into the core tables (it returned an error message that ARITHABORT is required to be ON when using indexed views).

So it seems that in order to use indexed views, I need to have the core application SET ARITHABORT ON whenever inserting/updating the core tables. I ran this in my test environment:

ALTER DATABASE MyDatabase SET ARITHABORT ON;

and it seems to work fine. But my vendor says since the application has thousands of queries, there could be a risk that this setting could break one of these queries, and if we have some future unexpected database issue they would insist I restore the default setting.

Are there actual queries that would be broken by SET ARITHABORT ON? Is there any situation where it would be better to keep it OFF?

TL;DR For my new indexed views to work I need to set ARITHABORT ON for the whole database, but my vendor warns it will be at my own risk. Is there actually a risk?

Answer 1

So SET ARITHABORT ON basically says "if a divide by zero error happens or an arithmetic overflow happens abort the query" This is usually desirable behavior and is the default instance wide setting. If this causes issues with your vendor's queries, I would say that they may have been suffering from some coding issues to begin with. I would ask them for more details on why they are concerned here.

Of all of the rules of indexed views, I would call this and many of the set options rules the least controversial.

This would have to be set in the connections that interact with the view. So you would want to work with the vendor and really try and understand their reasoning and try and get them to commit to what they are thinking on the big disagreement here.

That said - Indexed views are a bit of a big deal. They have other rules and they can impact the application and assumptions the vendor's developers had when building and performance testing. You should really have a conversation with them about the business problem you are trying to solve through indexed views and get them involved in the conversation about how to solve the problem.

Answer 2

The obvious risk from making this change is that vendor queries that previously ran correctly could start to throw errors, or return incorrect results. The ARITHABORT setting partly controls whether arithmetic overflow and divide-by-zero errors return a NULL result, terminate the statement with an error, or terminate the batch with an error. How the vendor code might react to code that throws an error instead of returning NULL is not something you would want to experiment with on a production system :)

However, the risk is lower if your database compatibility level is 90 or higher, and sessions run with SET ANSI_WARNINGS ON. This setting must have been ON when you tested the indexed views, but you need to confirm the effective setting used by your vendor application's connections. Management Studio may well be configured to use different SET options from those set by the vendor code when connecting (and you cannot override those with the database or instance defaults). Check with the vendor, and confirm by tracing vendor code using SQL Server Profiler.

Arguably, the bigger risk is that the vendor will refuse to support your installation until the setting is returned to OFF, but you should still try to encourage your vendor to update their code to work with the recommended SET options, so you do not have to choose between performance and running a supported installation. An alternative is to run reports against a copy of the database, of course.

-- Recommended effective settings
SET NUMERIC_ROUNDABORT OFF;
SET ARITHABORT, 
    CONCAT_NULL_YIELDS_NULL, 
    QUOTED_IDENTIFIER, 
    ANSI_NULLS, 
    ANSI_PADDING,
    ANSI_WARNINGS ON;