Document

T-SQL注意事项（1）——SET NOCOUNT ON的去与留

用了一段时间T-SQL之后，哪怕自己没用过，也多多少少看过SSMS中的SET NOCOUNT ON命令，很多性能优化文章中都有提到这个东西，它们建议尽可能使用这个命令减少网络传输的压力，那么今天来看看它是否是个鸡肋。

 

SET NOCOUNT的作用

首先来看看这个命令的作用，根据官方说明：阻止在结果集中返回显示受 Transact-SQL 语句或存储过程影响的行计数的消息。在说明中的“备注”部分有这么一段话，注意红字部分：
当 SET NOCOUNT 为 ON 时，将不向客户端发送存储过程中每个语句的 DONE_IN_PROC 消息。  如果存储过程中包含一些并不返回许多实际数据的语句，或者如果过程包含 Transact-SQL 循环，网络通信流量便会大量减少，因此，将 SET NOCOUNT 设置为 ON 可显著提高性能。  

如果大家对上面的DONE_IN_PROC有兴趣，可以看看这篇文章: https://msdn.microsoft.com/en-us/library/dd340553.aspx，但是我觉得没必要过于纠结。

下面创建一个测试例子，并做演示：

 

 

[sql] view plain copy
 
  1. USE tempdb  
  2. GO  
  3.    
  4. IF object_id('NocountDemo', 'U') IS NOT NULL  
  5.     DROP TABLE NocountDemo  
  6. GO  
  7.    
  8. CREATE TABLE NocountDemo(  
  9.     id INT identity(1, 1),  
  10.     NAME VARCHAR(64)  
  11.     )  
  12. GO  
  13.    
  14. /*  
  15. 插入测试数据  
  16. */  
  17. INSERT INTO NocountDemo  
  18. SELECT NAME  
  19. FROM master..spt_values  
  20.    
  21. /*  
  22. 常规使用  
  23. */  
  24. SELECT *  
  25. FROM NocountDemo  
  26. GO  
  27.    
  28. /*  
  29. 使用SET NOCOUNT选项  
  30. */  
  31. SET NOCOUNT ON;  
  32.    
  33. SELECT *  
  34. FROM NocountDemo  
  35.    
  36. SET NOCOUNT OFF;  

 

首先看看默认情况，也就是SET NOCOUNT OFF的结果：

 

 

 

然后看看开启NOCOUNT选项的结果：

 

 

 

 

 

默认情况下，任何SQL语句成功完成后都会返回一些信息，而NOCOUNT用于控制是否返回影响行数，需要提醒的是，哪怕不是真正的SQL语句，比如开启包含实际执行计划功能，当执行计划成功完成后（注意是完成），也会返回影响行数到客户端（也就是这里的SSMS）。

但是从实操层面，大家是否几乎没有关注过这个信息？确实，是否成功执行完很多时候不需要查看这个信息，一些SELECT语句自然会返回数据结果。所以实际上这个信息常常是非必要的。

作为最佳实践，一般建议不返回影响行数，特别是存储过程，不过有时候它又有存在价值，比如应用程序嵌入的SQL语句的调试。

在很多性能优化的文章中（甚至前面提到的官方文档）都提到，为了减轻网络压力，建议启用这个设置（注意一点，这个设置和其他很多设置不同，它默认是“开启”的，也就是说我们需要做“关闭”操作，而很多操作是需要做“开启”操作）。这个原因貌似很合理，不过作为DBA的习惯，我更愿意深究底层原理，所以下面再看个简单例子，插入一条数据后，循环更新100万次，并获取时间差：

 

 

[sql] view plain copy
 
  1. SET NOCOUNT OFF;  
  2.   
  3. DECLARE @i INT = 1;  
  4. DECLARE @x TABLE (a INT);  
  5.   
  6. INSERT @x (a)  
  7. VALUES (1);  
  8.   
  9. SELECT SYSDATETIME() AS [开始时间];  
  10.   
  11. WHILE @i < 1000000  
  12. BEGIN  
  13.     UPDATE @x  
  14.     SET a = 1;  
  15.   
  16.     SET @i += 1;  
  17. END  
  18.   
  19. SELECT SYSDATETIME() AS [结束时间];  

 

 

在我本机上执行情况如下图：

 

 

 

本文重点关注的是影响行数，所以我们先看影响行数的情况，选择结果集中的【消息】页

 

从右边的滚动条可以大概预估这个行数应该很多。实际上每次insert都有一行，外加循环外层的SELECTSYSDATETIME()  ，总共10000002行，然后关闭返回影响行数，即使用SET NOCOUNT命令重新执行：

 

 

 

为了避免有人觉得测试没考虑并发问题，我使用SQLQueryStress工具分别对上面两套语句模拟200个线程执行100次（本机配置太低无法执行太多次）：

默认情况反复执行多次：

 

 

开启NOCOUNT的情况下：

 

 

对比一下时间，差异时间不明显，如果多执行几次可能会出现反而更慢的情况，难道网上说的是假的？先别下定论，那问题在哪里？我们再回过头看看别人的描述里面的关键字“网络传输”，貌似发现问题了，因为一直以来都在单机上面测试，而且检查一下SQL Server配置管理器中的网络协议，Shared Memory是开启了，也就是说数据都在内存中传输，跟“网络”没什么关系：

 

 

 

那么看来问题就是这个原因，现在随便找台机器再试一下，我在国际版的Azure上开了个SQL Azure（开VM再装SQL Server实在耗时而且费用不少），如果没有条件的可以找些测试服务器甚至别人的电脑试试。

现在我在本机直接连到美国的SQL Azure上，然后再次分别执行上面的两个语句（为了避免时间过久，把100万次改为10万次）：

 

 

 

 

 

 

 

 

 

时间对比之后发现，其实相差不大，我们不妨静下来想一下，即使100万次，产生的数据也就几十上百Bytes或者KB，对今时今日的硬件来说不可能出现明显的性能提升，之所以网上有这个说法，很可能是当初的硬件资源存在局限，无法满足今天看起来不算大的数据量。但是不管如何，个人看来，这些确实也有一定的消耗，作为编程习惯，在非必要的情况下还是建议不返回，毕竟真的没什么人用。

 

问题提炼

对于这个问题，我想到了两件事情：如何对待别人的“善意”和减少网络传输

 

1.如何对待别人的“善意”

 

最近上下班路上在看一本书，看完再分享一下，书上多处地方提到一个句子：One thing doesn’t fit all。说白了就是“放之四海而皆准”的反义词。我个人挺赞同这个观点，为什么非要用一个产品去实现所有功能呢？今时今日大量系统的架构都使用了很多混合技术，这也证明了这个观点的可行性。那么回归这个问题，当初别人提出这个优化或编程建议时，确实可能存在网络性能问题、甚至客户端（本例中的SSMS）的内存资源压力从而造成性能压力，正如20年前Oracle优化书籍中提到的一个表索引数不要超过5个、索引叶子节点不要缓存到内存这类建议一样，当年的硬件资源确实没办法很好支持这些特性。再看今天，软硬件已经有了长足的发展，很多当初是对的设置今天看起来已经无关重要甚至是错误的。所以在对待很多所谓的“军规”、“铁律”时，还是要自己实测一下或者论证一下。从本例中看，它不会有什么严重的后果和风险，所以是可以测试的，但是有些风险比较大的最好慎重测试。我个人认为，作为一些编程规范，不妨保留下来，因为它和下面这个有关系。在编程的时候要有减少资源使用的惯性思维。

那什么时候有用呢？前面提到了——查错

在我初为DBA的时候，有一次一个开发问我：为什么明明插入了一条数据到一个表里面，并且成功了，表却没有数据。一开始我以为是回滚，问她要完整的语句，拿过来之后发现就一个简单的insert命令，没有显式事务。然后我自己执行了一下，确实没数据，再看一下影响行数，竟然有两条(1 行受影响)，这就引起我的注意了，检查表触发器，果然有一个触发器，并且功能就是一旦有插入动作，马上触发删除。也就是来一个死一个，来两个死一双。

基于这些原因，我认为具体问题具体分析才是最有意义的，哪来那么多所谓的意见建议，不考虑具体环境的建议都是耍流氓。

 

2.减少网络传输  

之所以当初有这个说法，很大程度是因为网络传输压力。那么我们借助这个问题，引申其他资源合理使用的场景，这里说的是网络问题，那就说网络问题吧，单纯从数据库层面来说，通常网络压力在哪里呢？据本人了解，大概在这些方面：

a)        需要传输的数据量，这是真正的网络压力。量越大压力越明显，那么通常我们要做的就是在返回数据时，尽可能控制数据量，比如不要在非必要情况下使用SELECT *，尽可能在离开数据层的时候就把数据量控制下来。

b)        其他功能传输的信息，比如SQL Server一些高可用或者负载分离功能，就拿复制功能来说，为了使订阅服务器的数据与发布服务器的数据一致，发布服务器会根据实际配置实时或定时发送事务日志到订阅端重做，日志产生越多，需要传输的量就越多，对于这部分你能干预的地方就很少，非要做的话，可以在配置发布项时只选择需要同步的行与列。

c)        SQL语句，这部分实际上也不大，但是正如很多编码规范中说的，尽可能使用存储过程，其中一个理由就是直接传输SQL代码不仅不安全，而且量一大的话，也是有开销的，有些SQL代码有还几百KB，对于使用频繁的系统而言，这也是一笔开销。当然不是说禁用，具体情况具体分析吧。另外多说一句，对于超过8KB的SQL代码，SQL Server不缓存执行计划，意味着你要每次重编译可能完全一样的SQL代码，从而造成CPU、内存的压力。

d)        需要导入、导出数据库的其他格式文件，如TXT、Excel等，某些系统需要传输一些文件到服务器再进行导入或者导出数据到文件然后通过某些方式传输出数据库服务器之外，这部分可以通过修改业务逻辑来降低，但是能降低程度可能不高，那么对于这种情况，可以考虑把数据库服务器和应用程序放在一个局域网中，然后把文件最终需要传输的发生地从数据库服务器移到应用程序所在的服务器，由于应用程序容易横向扩展，所以可以通过一些技术把应用程序的负载降低，这样即使文件传输的量不能降低，最起码对数据库层服务器的资源争用能有一定的缓解。

 

总的来说，我个人建议保留这个功能的常态化关闭、排错时开启的说法。最重要的作用实际还是警示，让使用者时刻注意对资源的合理使用。

posted @ 2017-12-04 10:31  从未被超越  阅读(340)  评论(0)  编辑  收藏  举报