将 SqlClient 默认设置为 ARITHABORT ON。

Question

将 SqlClient 默认设置为 ARITHABORT ON。

sql-serversql-server-2008configurationcompatibility-levelado.net

38

首先要说的是：我正在使用带有兼容性级别80的MS SQL Server 2008，并通过.Net的System.Data.SqlClient.SqlConnection连接到它。出于性能原因，我创建了一个索引视图。结果是，对视图中引用的表进行更新时需要使用ARITHABORT ON。然而，分析器显示SqlClient连接时使用的是ARITHABORT OFF，因此对这些表的更新失败了。是否有一个中央配置设置可以使SqlClient使用ARITHABORT ON？我能找到的最好方法是在每次打开连接时手动执行该操作，但是更新现有的代码库以实现这一点将是一项相当大的任务，所以我很想找到更好的方法。

- Peter Taylor

6个回答

6

选项1

除了Sankar的解决方案之外，将服务器级别上的算术中止设置应用于所有连接也可以起作用：

EXEC sys.sp_configure N'user options', N'64'
GO
RECONFIGURE WITH OVERRIDE
GO

从 SQL 2014 起，对于所有连接都应该设置为开启：

您应始终将 ARITHABORT 设置为 ON 在您的登录会话中。设置 ARITHABORT 为 OFF 可能会对查询优化产生负面影响，导致性能问题。

因此，这似乎是理想的解决方案。

选项2

如果选项1不可行，并且您在大多数 SQL 查询中使用存储过程（建议如此，请参阅存储过程 vs. 内联SQL），则只需在每个相关存储过程中启用该选项：

CREATE PROCEDURE ...
AS 
BEGIN
   SET ARITHABORT ON
   SELECT ...
END
GO

我相信这里最好的真正解决方案就是简单地编辑您的代码，因为它是错误的，任何其他修复方法只不过是权宜之计。

- LowlyDBA - John M

我认为在 SQL Server 上设置它并没有帮助，因为 .net 连接是以 set ArithAbort off 开始的。我希望能在 .net / C# 方面做些什么。我设置了赏金，因为我看到了这个建议。 - Henrik Staun Poulsen

1.NET/C#这边是Sankar介绍的内容，所以这些基本上是唯一的选择。 - LowlyDBA - John M

我尝试了选项1，但没有任何效果。新的会话仍然显示为arithabort = 0。虽然我没有遇到任何问题，但只是想提前解决潜在的问题。 - Mark Freeman

4

我在这方面并不是专家，但你可以尝试以下的方法。

String sConnectionstring;
sConnectionstring = "Initial Catalog=Pubs;Integrated Security=true;Data Source=DCC2516";

SqlConnection Conn = new SqlConnection(sConnectionstring);

SqlCommand blah = new SqlCommand("SET ARITHABORT ON", Conn);
blah.ExecuteNonQuery();


SqlCommand cmd = new SqlCommand();
// Int32 rowsAffected;

cmd.CommandText = "dbo.xmltext_import";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = Conn;
Conn.Open();
//Console.Write ("Connection is open");
//rowsAffected = 
cmd.ExecuteNonQuery();
Conn.Close();

参考：http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d9e3e8ba-4948-4419-bb6b-dd5208bd7547/

- Sankar Reddy

是的，我所说的就是手动执行。问题在于，我正在处理的代码库在数据库访问层面上积累了相当多的技术债务，所以我需要重新编写几百个方法来以这种方式进行操作。 - Peter Taylor

2

没有设置可以强制 SqlClient 始终设置 ARITHABORT，您必须按照您所描述的方式进行设置。

有趣的是，根据Microsoft 关于 SET ARITHABORT 的文档：

您应该始终在登录会话中将 ARITHABORT 设置为 ON。将 ARITHABORT 设置为 OFF 可能会对查询优化产生负面影响，导致性能问题。

然而，.Net 连接默认情况下硬编码为关闭此设置？

另外，当诊断此设置时，您必须非常小心。不同的设置选项将导致相同查询的不同查询计划。您的 .Net 代码可能会遇到性能问题（SET ARITHABORT OFF），但当您在 SSMS 中运行相同的 TSQL 查询（默认情况下 SET ARITHABORT ON）时，可能会正常工作。这是因为 .Net 查询计划不会被重用，而是生成一个新的计划。这可能会消除参数嗅探问题，并提供更好的性能。

- Andy Jones

1@HenrikStaunPoulsen - 除非你被困在使用2000年（或2000兼容级别）的情况下，否则这并没有任何区别。在较新的版本中，ANSI_WARNINGS已经被默认设置为打开，并且像索引视图之类的功能也可以正常工作。 - Martin Smith

1请注意，.Net并没有硬编码设置ARITHABORT为off。SSMS默认将其设置为on。.Net只是连接并使用服务器/数据库的默认设置。您可以在MS Connect上找到关于用户对SSMS默认行为的投诉问题。请注意ARITHABORT文档页面上的警告。 - Bacon Bits

2

如果可以节省任何人的时间，在我的情况下（Entity Framework Core 2.0.3，ASP.Net Core API，SQL Server 2008 R2）：

EF Core 2.0上没有拦截器（我认为它们很快会在2.1上可用）
无论是更改全局DB设置还是设置user_options对我来说都是不可接受的（它们确实有效 - 我已经测试过），但我不能冒着影响其他应用程序的风险。

从EF Core进行的即席查询，在顶部加上SET ARITHABORT ON;是行不通的。

最后，对我有效的解决方案是：将一个存储过程与在EXEC之前用分号分隔的SET选项一起作为原始查询调用，就像这样：

// C# EF Core
int result = _context.Database.ExecuteSqlCommand($@"
SET ARITHABORT ON;
EXEC MyUpdateTableStoredProc
             @Param1 = {value1}
");

- Chris Amelinckx

有趣。感谢您发布了关于使用EF Core的这些细微差别。只是好奇：您在这里做的事情本质上是我在回答的备选方法部分的ELSE子节中提到的包装器选项吗？我只是想知道，因为您提到了我的回答中的其他建议要么不起作用，要么由于其他限制而不可行，但没有提到包装器选项。 - Solomon Rutzky

@SolomonRutzky 这相当于该选项，但细微差别在于它仅限于执行存储过程。在我的情况下，如果我在原始更新查询前加上 SET OPTION（手动或通过包装器），它不起作用。如果我将 SET OPTION 放在存储过程内部，它也不起作用。唯一的方法是在同一批处理中执行 SET OPTION，然后执行存储过程。这就是为什么我选择定制特定调用而不是创建包装器的原因。很快我们会升级到 SQL Server 2016，届时我可以整理这个问题。感谢您的答案，有助于排除特定场景。 - Chris Amelinckx

0

在Solomon Rutzy的回答的基础上，针对EF6进行扩展：

using System.Data;
using System.Data.Common;

namespace project.Data.Models
{
    abstract class ProjectDBContextBase: DbContext
    {
        internal ProjectDBContextBase(string nameOrConnectionString) : base(nameOrConnectionString)
        {
            this.Database.Connection.StateChange += new StateChangeEventHandler(OnStateChange);
        }

        protected static void OnStateChange(object sender, StateChangeEventArgs args)
        {
            if (args.OriginalState == ConnectionState.Closed
                && args.CurrentState == ConnectionState.Open)
            {
                using (DbCommand _Command = ((DbConnection)sender).CreateCommand())
                {
                    _Command.CommandType = CommandType.Text;
                    _Command.CommandText = "SET ARITHABORT ON;";
                    _Command.ExecuteNonQuery();
                }
            }
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        ...

这里使用的是System.Data.Common的DbCommand而不是SqlCommand，以及DbConnection而不是SqlConnection。

一个SQL Profiler跟踪确认，在连接打开之前发送了SET ARITHABORT ON，在事务中执行任何其他命令之前。

- CapNCook

我不认为事件处理程序会在执行后续查询之前给你任何保证，对吧？但是我打算还是使用它... - Shockwaver

@Shockwaver，如果连接在后续命令中仍然保持打开状态，SET ARITHABORT将保持开启。如果连接关闭然后重新打开，事件将再次触发，在执行任何其他命令之前。我希望我理解了你的问题。 - CapNCook

我的意思是不同的。如果你打开连接然后发送一个查询，我认为你不能保证事件处理程序（其中包含ARITHABORT查询）会在其他查询之前执行。这取决于处理程序的调用方式，虽然我没有深入研究EF代码，但我可能是错的。 - Shockwaver

- Solomon Rutzky · Accepted Answer

表面上的首选方法

我曾以为这些方法已经被其他人测试过了，尤其是根据一些评论。但我的测试表明，即使通过.NET SqlClient连接，这两种方法确实在数据库层面上起作用。这已经被其他人测试和验证过了。

服务器范围内

您可以将user options服务器配置设置为当前它与64（ARITHABORT的值）按位OR的任何内容。如果您不使用按位 OR（|），而是进行直接赋值（=），则会清除已启用的任何其他现有选项。

DECLARE @Value INT;

SELECT @Value = CONVERT(INT, [value_in_use]) --[config_value] | 64
FROM   sys.configurations sc
WHERE  sc.[name] = N'user options';

IF ((@Value & 64) <> 64)
BEGIN
  PRINT 'Enabling ARITHABORT...';
  SET @Value = (@Value | 64);

  EXEC sp_configure N'user options', @Value;
  RECONFIGURE;
END;

EXEC sp_configure N'user options'; -- verify current state

数据库级别

可以通过ALTER DATABASE SET来针对每个数据库进行设置：

USE [master];

IF (EXISTS(
     SELECT *
     FROM   sys.databases db
     WHERE  db.[name] = N'{database_name}'
     AND    db.[is_arithabort_on] = 0
   ))
BEGIN
  PRINT 'Enabling ARITHABORT...';

  ALTER DATABASE [{database_name}] SET ARITHABORT ON WITH NO_WAIT;
END;

替代方法

不太好的消息是，我在这个主题上进行了很多搜索，只发现多年来许多其他人也对这个主题进行了大量搜索，并且没有办法配置SqlClient的行为。一些MSDN文档暗示可以通过ConnectionString来完成，但没有关键字可以修改这些设置。另一个文档暗示可以通过客户端网络配置/配置管理器进行更改，但似乎也不可能。因此，非常遗憾地，您需要手动执行SET ARITHABORT ON;。以下是一些考虑的方法：

如果您正在使用Entity Framework 6（或更新版本），您可以尝试以下任一方法：

使用Database.ExecuteSqlCommand： context.Database.ExecuteSqlCommand("SET ARITHABORT ON;");
理想情况下，这应该在打开数据库连接后执行一次，而不是每个查询都执行。通过以下方式创建一个拦截器：

这将允许您在执行SQL之前修改它，这样您只需在前面加上：SET ARITHABORT ON;。这种方法的缺点是它会针对每个查询执行一次，除非您存储一个本地变量来捕获是否已执行的状态，并在每次测试时检查（这实际上并不需要太多额外工作，但使用ExecuteSqlCommand可能更容易）。无论哪种方法都可以让您在一个地方处理此事，而不需要更改任何现有的代码。另外，您还可以创建一个类似于以下方法的包装方法来实现此功能：

public static SqlDataReader ExecuteReaderWithSetting(SqlCommand CommandToExec)
{
  CommandToExec.CommandText = "SET ARITHABORT ON;\n" + CommandToExec.CommandText;

  return CommandToExec.ExecuteReader();
}

然后只需将当前的_Reader = _Command.ExecuteReader();引用更改为_Reader = ExecuteReaderWithSetting(_Command);。这样做还可以在一个位置处理设置，同时只需要进行最小和简单的代码更改，大部分可以通过查找和替换完成。更好的是（另外第二部分），由于这是一个连接级别的设置，它不需要在每个SqlCommand.Execute__()调用中执行。所以，不要为ExecuteReader()创建一个包装器，而是为Connection.Open()创建一个包装器。

public static void OpenAndSetArithAbort(SqlConnection MyConnection)
{
  using (SqlCommand _Command = MyConnection.CreateCommand())
  {
    _Command.CommandType = CommandType.Text;
    _Command.CommandText = "SET ARITHABORT ON;";

    MyConnection.Open();

    _Command.ExecuteNonQuery();
  }

  return;
}

然后只需将现有的_Connection.Open();引用替换为OpenAndSetArithAbort(_Connection);。上述两种想法都可以以更面向对象的方式实现，通过创建一个继承自SqlCommand或SqlConnection的类来实现。或者更好的方法（另外第三部分），您可以为Connection StateChange创建一个事件处理程序，并在连接从“Closed”变为“Open”时设置属性，如下所示：

protected static void OnStateChange(object sender, StateChangeEventArgs args)
{
    if (args.OriginalState == ConnectionState.Closed
        && args.CurrentState == ConnectionState.Open)
    {
        using (SqlCommand _Command = ((SqlConnection)sender).CreateCommand())
        {
            _Command.CommandType = CommandType.Text;
            _Command.CommandText = "SET ARITHABORT ON;";

            _Command.ExecuteNonQuery();
        }
    }
}

有了这个设置，你只需要在每个创建SqlConnection实例的地方添加以下内容：

_Connection.StateChange += new StateChangeEventHandler(OnStateChange);

无需对现有代码进行更改。我刚刚在一个小的控制台应用程序中尝试了这种方法，通过打印SELECT SESSIONPROPERTY('ARITHABORT');的结果进行测试。它返回1，但如果禁用事件处理程序，则返回0。

为了完整起见，这里有一些不起作用的事情（要么根本不起作用，要么效果不佳）：

{{link1：Logon Triggers}}：即使在同一会话中运行，即使在明确启动的事务中运行，触发器仍然是一个子进程，因此其设置（SET 命令、本地临时表等）仅在其内部有效，不会在该子进程结束后保留。
在每个存储过程开头添加 SET ARITHABORT ON;：
- 对于现有项目来说，这需要大量工作，特别是随着存储过程数量的增加
- 这对于即席查询没有帮助