SQL Server 中 SET NOCOUNT 的作用和影响

慧都小妮子

于 2023-07-28 11:54:01 发布

阅读量1k

点赞数

文章标签： java 服务器网络数据库管理

本文链接：https://blog.csdn.net/m0_67129275/article/details/131977431

版权

在本文中，我们探讨了 SET NOCOUNT 在 SQL Server 中的作用和重要性，讨论它如何减少客户端的处理负载并提高查询执行时间。此外，我们还研究了需要使用 SET NOCOUNT OFF 的场景，并强调了不使用 SET NOCOUNT 时可能出现的潜在问题。

了解 SQL Server 中的 SET NOCOUNT

SET NOCOUNT 是什么的解释

每次执行 DML 命令时，都会向客户端发回一条简短消息，指示受查询影响的行数。

下载 SQL Server 工作室

该SET NOCOUNT语句在 SQL Server 中用于控制执行 SQL 语句后默认返回的“X rows受影响”消息的生成。当SET NOCOUNT ON启用时，它会禁止消息在结果集中返回，当SET NOCOUNT OFF设置或未指定时，消息将包含在结果集中。

SQL Server 中 SET NOCOUNT ON 的作用

使用的主要目的SET NOCOUNT ON是提高SQL Server查询和存储过程的性能和效率。SET NOCOUNT ON以下是常用的几个原因：

减少网络流量：当从客户端应用程序执行 SQL 语句时，行计数消息可能会产生额外的网络流量，因为它需要从服务器传输到客户端。通过使用SET NOCOUNT ON，您可以减少传输的数据量并提高整体网络性能。
减少客户端应用程序的负载：通过使用SET NOCOUNT ON，可以抑制这些行计数消息，从而使 SQL Server 和客户端应用程序之间的通信更加简化。这样，客户端应用程序可以只专注于检索实际结果数据，而不需要解析或处理行计数信息。
缩短执行时间：返回每个受影响行的行计数可能会增加处理开销，特别是对于涉及大量行的查询或操作。通过抑制行计数消息，您可以通过消除此额外处理来缩短执行时间。
增强结果集的可读性：在批处理或脚本中执行多个 T-SQL 语句或存储过程时，行计数消息可能会使结果集变得混乱，并使实际结果的解释变得更加困难。通过使用SET NOCOUNT ON，您可以保持结果集干净并专注于相关数据。

注意
设置SET NOCOUNT ON不会禁用 T-SQL 语句的实际执行或影响结果的准确性。它仅抑制行计数消息。

不使用 SET NOCOUNT ON 的影响

当 SQL Server 中未使用 SET NOCOUNT 时，行计数消息将包含在每个执行语句的结果集中，这会增加网络流量、使结果集混乱并使数据处理复杂化。此外，不使用 SET NOCOUNT 也可能导致缓冲区溢出问题。

缓冲区溢出问题

行计数消息必须由客户端应用程序处理。在为每个 T-SQL 语句生成和传输行计数消息，并且执行大量语句或处理大量结果集时，这些消息的累积可能会达到缓冲区的容量限制。

因此，缓冲区无法处理传入的行计数消息，导致查询执行暂停，直到客户端读取所有累积的行计数消息。一旦客户端消耗完所有累积的行计数消息，SQL Server 就会恢复执行，因为输出缓冲区中现在有可用内存。

要解决此问题，您可以使用该SET NOCOUNT ON选项来抑制行计数消息的生成。这有效地减少了缓冲结果所需的内存，从而显着降低了缓冲区溢出的风险。

必须设置 SET NOCOUNT ON 的情况

在某些情况下，使用SET NOCOUNT ON是绝对必要的。让我们考虑设计一个依赖异步处理的高性能中间层系统的情况，通过 SqlClient 的 BeginExecuteXXX 方法利用线程池。在这种情况下，会出现一个与行计数有关的关键问题。

它的工作原理如下： BeginExecute 方法设计为在服务器返回第一个响应数据包后立即完成。但是，当我们调用 EndExecuteXXX 方法时，它会等待非查询请求完成，然后才考虑调用完成。请务必注意，每个行计数响应都被视为单独的响应。

现在，让我们考虑一种具有中等复杂程序的情况。第一个行计数可能会在 10 毫秒内收到，而整个调用最多需要 500 毫秒才能完成。问题就出在这里：异步提交的请求回调不是在 500 毫秒后发生，而是在仅仅 5 毫秒后发生。但是，回调会在剩余的 495 毫秒内卡在 EndExecuteXXX 方法中。因此，异步调用会过早完成，并最终阻塞 EndExecuteNonQuery 调用中线程池中的线程。这一系列不幸的事件会导致线程池饥饿。

好消息是，SET NOCOUNT ON在这些特定场景中实施可以产生重大影响。据报道，只需适当利用，高性能系统即可将吞吐量从每秒数百个调用提高到每秒数千个调用SET NOCOUNT ON。这个微小但重要的变化确保异步调用得到最佳处理，避免过早完成和线程阻塞，最终导致更高效和可扩展的系统。

可能需要 SET NOCOUNT OFF 的情况

虽然通常建议用于SET NOCOUNT ON性能优化和减少网络流量，但在某些情况下设置SET NOCOUNT OFF变得至关重要。这里有一些例子：

与遗留代码的兼容性：如果您正在使用依赖于接收结果集中的行计数消息的遗留应用程序或代码，您可能需要进行设置SET NOCOUNT OFF以保持兼容性并确保应用程序按预期运行。
所需的行计数信息：在某些情况下，客户端应用程序或下游进程可能依赖于行计数消息提供的行计数信息。如果此信息对于应用程序的逻辑或报告目的至关重要，您可能需要进行设置SET NOCOUNT OFF以在结果集中包含行计数消息。
调试和故障排除：在调查性能问题或对特定查询进行故障排除时，访问行计数消息以了解每个执行语句的影响会很有帮助。在这种情况下，设置SET NOCOUNT OFF允许您分析行计数信息以用于诊断目的。
特定报告要求：如果您的报告框架或工具依赖于行计数消息来生成准确的报告或指标，您可能需要使用它SET NOCOUNT OFF来确保所需的行计数信息可用于报告目的。

过去，在某些情况下必须强制设置NOCOUNT OFF，特别是在 BDE（Borland 数据库引擎）等较旧的技术中。

使用 SET NOCOUNT 的最佳实践

SET NOCOUNT ON为了优化性能，Microsoft 建议有选择地在会话级别使用以防止传输这些行计数消息。这对于包含多个不返回太多实际数据的语句的存储过程特别有用。通过消除这些消息，可以显着提高性能，因为网络流量和客户端负载会大大减少。

一般来说，除非需要，否则建议避免发送行计数消息。然而，适应依赖并有时滥用这些消息的遗留应用程序可能会带来挑战。

使用 SET NOCOUNT ON 可以获得哪些性能优势？

使用的性能优势SET NOCOUNT ON可以根据具体情况而有所不同。性能优势的程度取决于过程中执行的查询的数量和频率等因素。例如，如果一个过程使用游标执行大量查询并将其结果合并到最终查询输出中，或者如果该过程包含多个不会产生大量数据的语句，则与使用游标相比，性能最多可以提高十倍NOCOUNT OFF。这一改进主要是由于网络流量的减少。

但是，如果过程仅包含一两个查询，则通过使用实现的性能增益SET NOCOUNT ON将不太明显，通常小于百分之五。

为什么在实例级别启用 NOCOUNT 不是个好方案

如今，您可以在实例级别启用NOCOUNT，并且现代 ORM（对象关系映射）框架完全能够有效地处理它。以下查询设置SET NOCOUNT ON实例级别的行为。

EXEC sys.sp_configure 'user options', '512';
RECONFIGURE

注意
该user options设置是位掩码，请相应处理。

但是，由于以下几个原因，在实例级别启用 NOCOUNT 可能被认为是一个坏主意：

覆盖设置的可能性：如果用户在单个会话中指定 NOCOUNT ON/OFF，他们可以覆盖在实例级别配置的行为。

兼容性问题：在实例级别启用 NOCOUNT 可能会对依赖行计数消息的旧应用程序或组件产生影响。如果这些应用程序期望并依赖于返回的行计数消息，则更改实例级设置可能会导致兼容性问题或意外行为。

意外后果：更改实例级别设置以启用 NOCOUNT 可能会影响修改后启动的所有用户会话。如果某些组件或过程未设计用于处理行计数消息的缺失，则可能会产生意想不到的后果。彻底测试和评估这一变化对现有系统的影响至关重要。

ORM 或框架兼容性： ORM 框架或其他数据库相关工具可能对行计数消息的可用性有特定的要求或假设。在实例级别启用 NOCOUNT 可能会破坏这些框架的功能并导致兼容性问题。

对特定场景的有限控制：在实例级别启用 NOCOUNT 会影响该实例上的所有会话和数据库。在处理需要或需要行计数消息以用于报告、监视或其他目的的特定场景或数据库时，这种粒度的缺乏可能会产生问题。

我们的建议

我们建议不要在实例级别启用 NOCOUNT，而是有选择地SET NOCOUNT ON在相关存储过程、触发器或查询中使用。这允许对何时抑制行计数消息进行更细粒度的控制，确保兼容性、维护预期行为并避免由全局实例级更改引起的潜在问题。显式设置NOCOUNT ON为高效查询执行和客户端处理添加了额外的保证。

通过SET NOCOUNT ON在每个存储过程、触发器和动态执行批处理的开头添加，您可以遵循一致的方法，并有助于避免因未显式设置而可能出现的任何潜在问题。这是优化 SQL Server 查询、提高性能并确保应用程序和用户获得流畅体验的主动措施。

dbForge Studio for SQL Server 提供了一个内置的T-SQL 分析器，可以检查 SQL 代码是否存在潜在问题、错误或违反最佳实践的情况。它包括两个专门设计用于识别 SET NOCOUNT 命令的潜在误用的静态代码分析规则：

PF008：存储过程和触发器中的 DML 之前没有 SET NOCOUNT ON
此规则检查存储过程和触发器中的 DML 语句之前未使用 SET NOCOUNT ON 命令的实例。它有助于确保在执行 DML 操作之前应用适当的 SET NOCOUNT ON 命令。
PF009：使用了 SET NOCOUNT OFF 选项
此规则验证是否正确使用了 SET NOCOUNT OFF 选项。它确保仅在必要时才使用 SET NOCOUNT OFF 命令，并考虑到它对网络流量和性能的潜在影响。

这些 T-SQL 分析规则为识别和解决与 SQL Server 代码中使用 SET NOCOUNT 相关的任何潜在问题提供了宝贵的帮助。

结论

SET NOCOUNT ON命令通过减少网络流量、减少客户端负载并增强查询执行来优化 SQL Server 性能，发挥着至关重要的作用。借助其内置的 T-SQL 分析器，dbForge Studio for SQL Server可以针对 SET NOCOUNT 命令的潜在误用提供有价值的见解，从而确保代码合规性和性能优化。