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

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 2

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