在使用上设置NOCOUNT

小开

我想在某种程度上这是一个DBA vs.开发人员的问题。

作为一个开发人员，我会说除非你绝对有必要，否则不要使用它——因为使用它会破坏你的ADO。NET代码(如Microsoft文档所示)。

我想作为一名DBA，您应该站在另一边——只要有可能就使用它，除非您真的必须阻止它的使用。

同样，如果你的开发者使用了ADO返回的“RecordsAffected”。NET的ExecuteNonQuery方法调用，如果每个人都使用SET NOCOUNT ON，你就麻烦了，因为在这种情况下，ExecuteNonQuery总是返回0。

也可以看看Peter Bromberg的博客，看看他的位置。

所以归根结底是谁来制定标准:-)

马克

小开

最佳答案

好了，现在我做了我的研究，事情是这样的:

在TDS协议中，SET NOCOUNT ON只保存每个查询9字节，而文本“SET NOCOUNT ON”本身是一个巨大的14字节。我曾经认为123 row(s) affected是在一个单独的网络包中以纯文本从服务器返回的，但事实并非如此。它实际上是嵌入在响应中的一个名为DONE_IN_PROC的小结构。它不是一个单独的网络数据包，因此不会浪费往返。

我认为您几乎总是可以坚持默认的计数行为，而不用担心性能。但是在某些情况下，预先计算行数会影响性能，例如只向前游标。在这种情况下，NOCOUNT可能是必需的。除此之外，绝对没有必要遵循“尽可能使用NOCOUNT”的座右铭。

下面是关于SET NOCOUNT设置不重要的非常详细的分析

小开

关于破坏NHibernate的触发器，我有第一手的经验。基本上，当NH执行UPDATE时，它预计受影响的行数是一定的。通过将SET NOCOUNT ON添加到触发器，您可以获得NH期望的行数，从而解决问题。所以，如果你使用NH，我绝对建议关闭触发器。

至于在SPs中的使用，这是个人喜好的问题。我总是关闭行计数，但话说回来，这两种方法都没有真正有力的论据。

另一方面，您应该真正考虑远离基于sp的体系结构，这样您甚至不会遇到这个问题。

小开

如果你说你可能有不同的客户端，经典的ADO有问题，如果SET NOCOUNT没有设置为ON。

我经常遇到的一个问题是:如果一个存储过程执行了许多语句(因此返回了许多“受影响的xxx行”消息)，ADO似乎不处理这个问题，并抛出错误"无法更改以命令对象为源的记录集对象的ActiveConnection属性。"

所以我通常主张设置它，除非有真的真的好的理由不这样做。你可能已经找到了非常非常好的理由，我需要进一步阅读。

小开

为了找到关于NOCOUNT的真实基准数据，我花了很多时间进行挖掘，所以我想分享一个简短的总结。

如果您的存储过程使用游标执行许多非常快速的操作而没有返回结果，那么设置NOCOUNT OFF所需的时间大约是设置为ON所需时间的10倍。这是最坏的情况。
如果您的存储过程只执行一个快速操作而没有返回结果，则设置NOCOUNT ON 可能将产生约3%的性能提升。这将与典型的插入或更新过程一致。(关于为什么这样做并不总是更快的一些讨论，请参阅这个答案的评论。)
如果您的存储过程返回结果(即您选择某个东西)，性能差异将随着结果集的大小成比例地减小。

小开

冒着让事情变得更复杂的风险，我推荐一个与我上面看到的略有不同的规则:

总是在proc的顶部设置NOCOUNT ON，在你在proc中做任何工作之前，但是也总是再次SET NOCOUNT OFF，在从存储的proc返回任何记录集之前。

所以“通常不要指望，除非你实际返回一个结果集”。我不知道这可以破坏任何客户端代码的任何方式，这意味着客户端代码永远不需要知道任何关于过程内部的信息，而且它不是特别繁重。

小开

我不知道如何在客户端和SQL之间测试SET NOCOUNT ON，所以我测试了其他SET命令的类似行为“SET TRANSACTION ISOLATION LEVEL READ unimcommitted”

我从我的连接发送了一个命令，改变SQL的默认行为(READ COMMITTED)，它被更改为下一个命令。当我在存储过程中更改隔离级别时，它不会改变下一个命令的连接行为

目前的结论,

在存储过程中更改设置不会更改连接默认设置。
通过使用ADOCOnnection发送命令更改设置将更改默认行为。

我认为这与其他SET命令相关，如“SET NOCOUNT ON”

小开

SET NOCOUNT ON;

这一行代码用于SQL中不返回在执行查询时受影响的数字行。如果我们不需要受影响的行数，我们可以使用它，因为这将有助于节省内存使用并提高查询的执行速度。

小开

当SET NOCOUNT为ON时，计数(指示受Transact-SQL语句影响的行数)不会返回。当SET NOCOUNT为OFF时，返回计数。它与任何SELECT, INSERT, UPDATE, DELETE语句一起使用。

SET NOCOUNT的设置在执行或运行时设置，而不是在解析时设置。

SET NOCOUNT ON可提高SP (storage procedure)性能。

语法:SET NOCOUNT {ON | OFF}

SET NOCOUNT ON示例:

enter image description here

SET NOCOUNT OFF示例:

enter image description here

小开

If(设置no count== off)

{ 然后，它将保存受影响记录数量的数据降低性能｝其他的｛它不会跟踪变更记录因此提高绩效｝ }

小开

我知道这是个很老的问题。只是更新一下。

使用“SET NOCOUNT ON”的最佳方法是将其作为SP中的第一个语句，并在最后一个SELECT语句之前再次将其设置为OFF。

小开

SET NOCOUNT ON; 以上代码将在DML/DDL命令执行后，停止sql server引擎生成的消息到前端结果窗口

我们为什么这样做? 由于SQL server引擎获取状态并生成消息需要消耗一定的资源，因此对SQL server引擎来说是一种过载。因此我们将noncount消息设置为。

小开

有时候，即使是最简单的事情也能产生影响。这些简单的项之一是SET NOCOUNT ON，它应该是每个存储过程的一部分。这一行放在存储过程顶部的代码关闭了SQL Server在每条T-SQL语句执行后发送回客户端的消息。这将对所有SELECT， INSERT， UPDATE和DELETE语句执行。当您在查询窗口中运行T-SQL语句时，拥有这些信息非常方便，但是当运行存储过程时，就不需要将这些信息传递回客户机了。

通过从网络中移除这种额外的开销，可以极大地提高数据库和应用程序的整体性能。

如果你仍然需要得到正在执行的T-SQL语句影响的行数，你仍然可以使用@@ROWCOUNT选项。通过发出SET NOCOUNT ON，此函数(@@ROWCOUNT)仍然可以工作，并且仍然可以在存储过程中使用，以确定有多少行受到语句的影响。

小开

我想验证自己“SET NOCOUNT ON”不保存网络包或往返

我在另一个主机上使用测试SQLServer 2017(我使用虚拟机) 创建表ttable1 (n int); 插入ttable1值(1),(2),(3),(4),(5)、(6)、(7) 去 创建procNoCount过程作为开始设置nocount on 更新ttable1 set n=10-n 结束 <代码> / <代码> 创建过程procNormal 作为开始更新ttable1 set n=10-n 结束 然后我用“Wireshark”工具跟踪1433端口上的数据包: '捕获过滤器'按钮-> '端口1433'

exec procNoCount

这是响应报文: 0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 42 d0 ce 40 00 40 06 84 0d c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e5 9c be fb 85 01 50 18 0030 02 b4 e6 0e 00 00 04 01 00 1a 00 35 01 00 79 00 0040 00 00 00 00 fe 00 00 00 e0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 < /代码> <代码> exec procNormal < /代码> 

这是响应报文: 0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 4f d0 ea 40 00 40 06 83 e4 c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e8 b1 be fb 8a 35 50 18 0030 03 02 e6 1b 00 00 04 01 00 27 00 35 01 00 ff 11 0040 00 c5 00 07 00 00 00 00 00 00 79 00 00 00 00 00 0050 fe 00 00 e0 00 00 00 00 00 00 00 00 00 00 00 00 < /代码> 在第40行，我可以看到'07'，这是'受影响的行'的数量。它包含在响应包中。

 .没有额外的数据包


然而，它有13个额外的字节可以节省，但可能并不比减少列名更值得。从“管理部门”到“总经理”)


所以我认为没有理由用它来提高性能


< p >但
正如其他人提到的，它可以破坏ADO。网
我还无意中发现了一个使用python的问题:
MSSQL2008 - Pyodbc -以前的SQL不是查询 < / p >


所以这可能还是个好习惯……


                                                
                            
                                
                                    
                                
                            
                            
                                
                                    
                                        小开
                                        
                                    
                                
                                
                                                                        
                                                                                    SET NOCOUNT ON真正有用的一个地方是在循环或游标中执行查询的地方。这会导致大量的网络流量。


CREATE PROCEDURE NoCountOn
AS
set nocount on
DECLARE @num INT = 10000
while @num > 0
begin
update MyTable SET SomeColumn=SomeColumn
set @num = @num - 1
end
GO




CREATE PROCEDURE NoCountOff
AS
set nocount off
DECLARE @num INT = 10000
while @num > 0
begin
update MyTable SET SomeColumn=SomeColumn
set @num = @num - 1
end
GO



在SSMS中打开客户端统计数据，EXEC NoCountOn和EXEC NoCountOff的运行显示在NoCountOff上有额外的390KB流量:





在循环或游标中执行查询可能并不理想，但我们也不生活在理想的世界中:)
                                                                            
                                
                            
                        
                                                
                            
                                
                                    
                                
                            
                            
                                
                                    
                                        小开
                                        
                                    
                                
                                
                                                                        
                                                                                    SET NOCOUNT ON甚至允许像这样访问受影响的行:


SET NOCOUNT ON


DECLARE @test TABLE (ID int)


INSERT INTO @test
VALUES (1),(2),(3)


DECLARE @affectedRows int = -99


DELETE top (1)
FROM @test
SET @affectedRows = @@rowcount


SELECT @affectedRows as affectedRows



结果



affectedRows
  

1



消息



命令执行成功。
  

完成时间:2020-06-18T16:20:16.9686874+02:00

                                                                            
                                
                            
                        
                                                
                            
                                
                                    
                                
                            
                            
                                
                                    
                                        小开
                                        
                                    
                                
                                
                                                                        
                                                                                    
SET NOCOUNT ON-它将显示“命令已成功完成”。
SET NOCOUNT OFF-它将显示"(No。受影响的);