SQL Server 不会完成大量 SQL 语句的执行

本文可帮助你解决执行大量返回多个结果集的 SQL 语句时所发生的问题。

原始产品版本：SQL Server
原始 KB 数： 827575

现象

执行大量返回多个结果集的 SQL 语句时，Microsoft SQL Server 可能会在执行批处理中的所有语句之前停止处理批处理。 此行为的影响取决于批处理语句执行的操作。 例如，如果批处理在开始时启动事务并在末尾提交事务，则可能不会发生提交。 此行为会导致锁的保留时间超过预期。 这还可能导致关闭连接时回滚事务。 如果批处理未启动事务，则问题的症状可能是未执行某些语句。

以下是此问题的可能影响。 效果各不相同，具体取决于批包含的内容。

  • 请考虑从应用程序执行一批数据库查询语句。 如果一批数据库查询语句由开头和COMMIT TRANSACTION末尾的语句BEGIN TRANSACTION组成，则即使控件返回到应用程序，提交操作也可能不会发生。 这是一个问题，因为可能持有的锁可能会导致挂起的事务，并且可能未被注意到。

    在此方案中，由于事务从未在批处理中提交，因此该事务将保持挂起状态，并在与 SQL Server 断开连接时回滚。

  • 如果使用应用程序程序接口（API）开始并提交事务，可能会看到以下行为：

    • 如果使用 API 将通知发送到服务器以启动事务，然后执行批处理，SQL 可能只处理批处理的一部分，然后将该控件返回到应用程序。
    • 在此步骤之后，如果使用 API 提交事务，则只提交已处理的批处理的一部分。 不会发生错误。

    例如，使用 ODBC SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) 调用以启动事务，然后使用 SQLEndTran(SQL_COMMIT) 它提交事务。

原因

处理批处理结果时，SQL Server 使用来自批处理的结果填充连接输出缓冲区。 客户端应用程序必须处理这些结果。 如果要执行包含多个结果集的大型批处理（生成结果的多个语句），SQL Server 将填充该输出缓冲区，直到达到内部限制，并且直到客户端应用程序开始使用这些结果才能继续。 当客户端开始使用结果集时，SQL Server 将再次执行批处理，因为输出缓冲区中现在有可用内存。 此行为是特意这样设计的。

在许多情况下，使用命名管道协议或共享内存（LPC）协议连接到 SQL Server 时遇到此问题。 这是因为 SQL Server 可用于不同协议的内部缓冲区大小。

解决方法

若要解决此问题，请执行以下步骤：

  1. 确保客户端应用程序使用所有输出结果集。 一旦客户端使用所有输出结果集，SQL Server 就会完成执行批处理。

    • 如果使用 Open Database Connectivity （ODBC） 连接到 SQL Server，则可以调用该方法 SQLMoreResults ，直到方法报告没有更多结果集。
    • 如果使用 OLE DB 连接到 SQL Server，可以重复调用 IMultipleResults：：GetResult 方法，直到返回 DB_S_NORESULT。

  2. 将语句 SET NOCOUNT ON 添加到批处理的开头。 如果在存储过程内执行批处理，请将该语句添加到存储过程定义的开头。 这可以防止 SQL Server 返回一个附加的结果集，该结果集显示主结果集之后处理的行数。 因此，它可以减少将数据输出到服务器的输出缓冲区。 但是，这不保证问题不会发生。 它只会增加从服务器返回的数据足够小以适合一批结果集的可能性。

建议客户端应用程序始终使用来自 SQL Server 的所有结果集，而不考虑正在执行的批处理的大小。 如果不处理此数据，并且在结果集批处理中出现错误之前返回成功的结果集，则客户端可能不会发现服务器错误。 客户端应用程序应完全处理结果集，以确保正确执行。

问题重现步骤

  1. 使用 SQL Server Management Studio（SSMS）连接到 SQL Server，并创建示例 发布数据库。

  2. 使用相对大量的数据库查询语句创建 SQL 存储过程 pubs ，如下所示：

    CREATE PROC bigBatch AS
BEGIN TRANSACTION
UPDATE authors SET au_fname = 'newname1' WHERE au_id='172-32-1176'
UPDATE authors SET au_fname = 'newname2' WHERE au_id='172-32-1176'
UPDATE authors SET au_fname = 'newname3' WHERE au_id='172-32-1176'
-- Add more UPDATE statements here ... 
UPDATE authors SET au_fname = 'newname1000' WHERE au_id='172-32-1176'
COMMIT TRANSACTION

  3. 在对象资源管理器中，选择“管理>扩展事件”。

  4. 右键单击“ 会话”，然后选择“ 新建会话向导”。

  5. 使用 TSQL_SPs 会话模板创建新的事件会话。

  6. 启动会话并观看实时数据。 有关详细信息，请参阅 快速入门：SQL Server 中的扩展事件。

  7. 使用 ODBC 或 OLE DB 连接到 SQL Server，运行 bigBatch，然后分析事件会话的实时数据。

使用 ODBC 连接

若要使用 ODBC 连接到 SQL Server，请执行以下步骤：

  1. 创建，然后使用连接到 SQL Server 的数据库配置数据源名称（DSN pubs ）。
  2. 打开 随数据访问 （MDAC） SDK 安装一起提供的 ODBC 测试 工具示例。
  3. 在 Conn 菜单上，选择“ 完全连接”。
  4. 在 “完全连接 ”对话框中，选择在步骤 1 中创建的 DSN。
  5. 确保与 SQL Server 的连接成功。
  6. 在 Stmt 菜单上，选择“SQLExecDirect”。
  7. 在 StatementText 框中，键入 {call bigBatch}，然后选择“ 确定”。

在 XEvent 实时数据中，你注意到存储过程的处理未完成。 但是，ODBC 测试工具指示执行成功。 若要提取所有结果集并导致批处理在服务器上完成，请选择“结果”菜单上的“全部数据”。

使用 OLE DB 进行连接

若要使用 OLE DB 连接到 SQL Server，请执行以下步骤：

  1. 打开 MDAC SDK 提供的 OLE DB RowsetViewer 工具示例。
  2. 使用“完全连接”选项连接到 SQL Server pubs 数据库。
  3. 在 “命令 ”菜单上，指向 ICommand，然后选择“ 执行”。
  4. 在 Cmd 文本框中 ，键入 {call bigBatch}。
  5. 在 REFIID 列表中选择IID_IMultipleResults，然后选择“属性”。
  6. 在 “ICommandProperties：：SetProperties ”对话框中，选择 DBPROP_IMultipleResults，将值更改为 VARIANT_TRUE ，然后选择“ 确定”。
  7. 选择“确定”。

在 XEvent 实时数据中，你注意到存储过程的处理未完成。 但是，RowsetViewer 工具显示操作成功。 若要检索所有结果集，请右键单击左窗格中的 MultipleResults 对象，指向 IMultipleResults，然后选择 GetResult。 重复，直到使用完所有结果集。

参考

  • Last updated on