SQL Server ARITHABORT [英] SQL Server ARITHABORT

问题描述

我正在与一个刚刚从 SQL 2000 升级到 SQL 2008 的客户合作，他们的视图查询时间增加了很多.

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.

我查看了这些视图，并没有发现它们有什么问题.当我直接在服务器上运行视图时，时间还可以.当我通过 Management Studio 远程运行时，时间从 2 秒变为大约 30 秒.

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.

因此，我通过将 ARITHABORT 设置为 ON(基于一些文章)在测试副本上进行了实验，并且时间也远程下降了.

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.

所以，设置 ARITHABORT 似乎是答案，但在申请实时数据库之前，我想了解原因.我知道这与零划分的严重程度有关，但为什么它有助于查看查询时间?

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,

我认为在 SQL Server 2000 中，如果您将 ARITHABORT 设置为 OFF，则查询优化器在制定查询执行计划时不会考虑索引视图索引.因此，如果最佳计划使用视图索引，那就很重要了.不知道是不是这样，但是你看查询计划的时候，可以具体看一下更快的计划是否提到了一个视图索引.

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.

我不知道 ARITHABORT 与索引视图有关的具体原因，但 SET 选项会影响很多事情，并且 ARITHABORT 的情况几乎不稳定.您可以查看此链接.

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.

某些这种行为受到兼容性级别的影响也不是不可能的.如果任何升级后的数据库设置为 80 或 90 级，您可能会看到是否真的需要.

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.

这篇关于SQL Server ARITHABORT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！