SQL Prompt使用教程：更改过程或触发器中的SET选项将导致重新编译（下）

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。

本教程介绍了SQL Prompt的性能规则PE012，该规则将建议您是否在存储过程或触发器中检测到SET语句的使用，这可能会导致不必要的重新编译，尽管问题涉及其他类型的批处理。教程内容较多，分为上下两篇文章，本文是后半部分，内容紧接前文，想要查看前文请点击这里！

点击下载SQL Prompt正式版

在存储过程中更改SET选项

到目前为止，我们仅处理批处理，但是如果由于某种原因要确保使用特定设置执行各个过程该怎么办？

我已经将相同的逻辑封装在三个存储过程中，前两个对我们的两个选项使用了特定的设置，而第三个没有任何SET选项语句。

我对这三个过程分别执行了两次，首先是在所有选项均使用“默认”设置的会话中进行，其中ARITHABORT和ANSI_WARNINGS均处于ON状态（set_options = 4347），然后从前者处于ON状态而后者处于OFF状态的会话中（4331），最后从两个都关闭的会话中（235）。

我们总共看到9个计划，每次从具有不同set_options值的连接执行该计划时，都会为每个过程编译一个新计划。换句话说，如果调用批处理的执行设置与编译该过程的任何执行计划时有效的执行设置不匹配，则会使用新的set选项创建一个新的缓存计划。如果我们使用连接相同set_options值重新执行相同的存储过程，则该计划将被重用。

调用第一个存储过程（显式设置ARITHABORT为ON）始终返回2行，而调用第二个存储过程始终返回3行。在不使用SET语句的情况下调用过程时，它仅取决于调用连接的设置。

如果您更改了过程中的设置，则它们仅在该过程中有效，因此它们不会影响调用该过程的批处理。所有9个计划都显示了用于执行调用批处理的连接的SET选项值。

在过程和触发器中捕捉“影响计划的重用”的SET语句的使用

SQL Prompt中的性能规则（PE012）看起来是否SET在存储过程和触发器（尽管不是批处理）中做出了任何“影响计划重用”的SET语句。您还可以使用SQL Change Automation运行检查，以在数据库构建源中发现此问题。SQL Monitor还支持代码分析。

不过请注意：这种现象不仅适用于过程或触发器，而且还适用于任何临时批处理、使用sp_executesql执行的批、准备好的查询和动态SQL。如果发出“影响计划重用”SET语句，则对于其中任何一个的缓存计划都无法如此轻松地重用，并且在SQL Server的早期版本中，每次使用都会有重新编译的风险。

我们优先使用存储过程和触发器来处理动态Transact-SQL批处理，因为它们更易于重用。它们是参数化的，因此SQL文本永不更改，从而促进了重用。在准备好的批次或过程中更改设置时，设置选项仅用于执行准备好的批次或过程，

批处理也可以重用，但是如果通过sp_executesql或Prepare方法（而不是动态SQL或Execute方法）执行批处理，SQL Server发现这样做更容易。

更糟糕的是，在执行临时批处理时，SET选项中的任何更改都会从该批处理中泄漏出来，从而使连接保留其新设置：您必须显式还原设置，但是在该点之前立即中止该批处理的错误，将无法执行代码。然后，优化器可能需要编译新计划，以针对您在该连接上执行的所有后续批处理和过程的这些新设置。

很难检测到此错误，它增强了以下一般建议：在建立连接后，这些语句必须始终作为初步批处理执行，并且随后避免任何更改。这意味着所有此类SET语句在代码中都是可疑的，应被视为“SQL代码气味”。很难证明它们的合理性。

调查过度重新编译

在扩展事件不可用或过于粗糙的SQL Server版本中，可以使用SQL Server Profiler。尽管SP：Recompile跟踪事件可以仅用于报告过程和触发器的语句级重新编译，但SQL：StmtRecompile也可以用于跟踪和调试重新编译，它可以检测存储过程，触发器，临时批处理的重新编译，使用sp_executesql，准备好的查询和动态SQL执行的批处理。SP:Recompile和SQL:StmtRecompile的event子类列包含一个整数代码，指出重新编译的原因。

通过扩展事件，事情变得更加文明。我们可以获得有关重新编译及其原因的完整报告。这是一个简单的会话，用于报告各个编译。

这样，我们可以获得单个重新编译的详细信息。我通常在sqlserver.username字段上添加会话事件过滤器，以仅针对特定用户（运行测试代码的测试用户的名称）获得重新编译。否则会产生很多噪音。

如果您发现代码中包含涉及“计划重用影响”选项的SET语句，那么这就是代码的味道，您应该调查原因。

您当然可以做一些狡猾而聪明的事情，但是在我从事SQL Server开发的工作中，我从未发现过。这不仅是存储过程或触发器中的不良做法，而且还可能以任何批次执行多次。如果需要设置语言、ANSI选项或错误处理兼容性，则在创建连接并创建单个标准时进行设置。如果这样做失败，则会导致SQL Server执行不必要的重新编译。

当我写这些SET语句的使用是“不好的”时，我并不希望暗示批处理的重新编译一定是不好的：有时它们避免了一些隐匿的性能问题之一，并且它们很少会影响性能只要不沉迷于SQL代码，应用程序的气味就不必要了。例如，当我们创建要重用的批处理时，我们总是通过与参数sp_ExecuteSQL一起使用来促进代码重用，或者在应用程序中，我们正确地使用绑定参数。为了谨慎起见，我们使用表变量。

本教程内容到这里就完结了，由于教程内容较多，分为上下两篇文章，您可以点击这里查看~您也可以下载SQL Prompt试用版免费体验~

相关内容推荐：

SQL Prompt使用教程：更改过程或触发器中的SET选项将导致重新编译（上）