SQL Server ARITHABORT

Tim,

I think that in SQL Server 2000, if you had set ARITHABORT OFF, the query optimizer wouldn't consider indexed view indexes in developing a query execution plan. So if the best plan uses a view index, it would matter. I don't know if this is still the case, but when you look at the query plans, you could specifically look at whether the faster plan mentions a view index.

I don't know the specific reason ARITHABORT has to do with indexed views, but SET options affect a number of things, and the situation with ARITHABORT has hardly been stable. You might check out this link.

It's also not out of the question that some of this behavior is affected by the compatibility level. If any of the upgraded databases were set at level 80 or 90, you might see if that was really needed.

Please read this posthttp://www.sommarskog.se/query-plan-mysteries.html

I tend to think that the ARITHABORT setting is a red herring. Do your query plans differ between the test and the production systems? Are your tables IDENTICAL in the data that they contain, and are your statistics up to date on both servers, with the same indexes? I would check that first.