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?
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 post
http://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.
You should always turn ArithAbort ON in your logon session for performance reasons. I just experience this issue with multiple procs on a 2008 R2 database and found that Microsoft updated the SQL server documentation for 2012 to state as such.
http://msdn.microsoft.com/en-us/library/ms190306.aspx
Always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization, leading to performance issues.
⚠️Warning
The default ARITHABORT setting for SQL Server Management Studio is ON.
Client applications setting ARITHABORT to OFF might receive different query plans, making it difficult to troubleshoot poorly performing queries. That is, the same query might execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio, always match the client ARITHABORT setting.
[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.]
When ARITHABORT is OFF, indexes on (persisted) computed columns are not used. In general Microsoft recommends to always turn it ON. The only reason it's OFF by default (in some cases) is backwards compatibility.
