SQL Server ARITHABORT

Question

I'm working with a client who has just upgraded from SQL 2000 to SQL 2008 and their view query times have gone up a lot.

I had a look at the views and couldn't see much wrong with them. When I ran the view directly on the server, the times were OK. When I ran via Management Studio remotely, the time goes from 2secs to about 30secs.

So, I've tried an experiment on the test copy by setting ARITHABORT to ON (based on some articles), and the times go down remotely as well.

So, setting ARITHABORT seems to be the answer, but before applying to the live DB, I'd like to understand why. I get that it's to do with the level of severity of a zero divide, but why should it help with view query times?

Answer 1

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.

Answer 2

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.

Answer 3

[This isn't much of an answer.] I have also just run into this, but even more strangely is that I cannot now reproduce the previously poor performance! Even after setting that option back to OFF, the relevant SQL is now running as fast as it was previously. [I suspect caching has now obviated any differences that setting conferred.]