更改过程或触发器中的SET选项将导致重新编译-创新互联

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。本教程介绍了SQL Prompt的性能规则PE012，该规则将建议您是否在存储过程或触发器中检测到SET语句的使用，这可能会导致不必要的重新编译，尽管问题涉及其他类型的批处理。

有时，由于某种显而易见的原因，您将有一个存储过程或触发器间歇地花费更长的时间运行。您已经检查了索引，排除了诸如参数嗅探之类的问题，但是间歇性的性能问题仍然存在。SET为了更改执行设置，是否可以像您在批处理中发出语句那样简单呢？如果这样做，则可能是由于SQL Server需要重新编译该过程或重复触发而导致了该问题。

重新编译没有什么特别的错误，实际上，强制执行某些查询在每次执行时重新编译是很常见的，正是为了避免与参数嗅探、滥用Execute()或包罗万象的查询有关的不良性能问题。但是，如果重新编译变得过多，尤其是对于频繁或昂贵的查询，则可能会成为问题，值得调查原因，我将向您展示如何使用扩展事件。

什么是重新编译？

当SQL Server执行临时批处理或查询或诸如存储过程或触发器之类的对象时，SQL Server将为每个批处理或对象以及该批处理或对象中的每个查询编译针对当前状态进行优化的执行计划数据库，其对象及其数据。SQL Server的优化器设计此计划需要花费时间和资源，但是必须在代码可以传递到执行引擎之前完成。幸运的是，我们倾向于重复执行相同的查询或过程，可能使用不同的参数，因此SQL Server将其生成的大多数计划存储在计划缓存中，并且无论我们使用什么参数值，都将确保所有计划都可以安全地重用。当我们再次执行相同的批处理或对象时，只要有可能，它将简单地重用其缓存的计划。

但是，有时我们会重新执行存储过程，或者重新提交批处理或查询优化器之前已见过的缓存，并且针对该优化器在缓存中具有优化的计划，但是由于某些原因，它无法重用该计划并编译一个新的。这是重新编译，并且由于各种原因而发生。如果执行引擎检测到表已更改或其统计信息已发生重大变化，它将自动发生，这时它将标记要重新编译访问该表的查询的所有缓存计划。下次运行其中一个查询时，优化器将生成新计划，而旧计划将被删除。

我们还可以通过将OPTION (RECOMPILE)提示附加到查询来强制优化器不断重新编译计划。该查询的计划可能仍在高速缓存中，但不会被重用。通常这样做是为了处理由于参数嗅探，使用“catch-all”过程，滥用Execute()等等所导致的不稳定性能。

为了节省时间和资源，SQL Server会在可能的情况下进行语句级的重新编译。如果批处理或存储过程中仅一个语句的计划因数据结构或数据的基础更改而无效，或者只有一个语句具有OPTION (RECOMPILE)提示，则仅重新编译受影响的语句的计划，而不重新编译整个批处理或存储。

有时，重新编译既不会因数据结构或数据的更改而自动触发，也不会由于使用提示而被强制执行。我们在同一数据库上重新执行相同的查询，存在一个匹配的缓存计划，因为提交的查询的SQL文本和与该缓存计划相关联的SQL文本完全匹配（包括空格和回车符），但是该计划没有被重用。

再次，有几种可能的原因，我们将不在这里进一步讨论，例如，对未在过程中静态创建的临时表的引用，或者缺少模式验证，而我们将要解决的原因是缓存的计划是使用与提交查询的连接所使用的SET选项不同的SET选项创建的。

“影响计划重用”的SET选项

更改某些SET选项的值（有时称为“影响计划重用”的选项）将更改查询的运行方式及其结果。因此，当优化器检查其缓存计划是否匹配时，它包括检查在编译缓存计划中使用的SET选项是否与发布批次的连接中使用的SET选项匹配。如果它们不匹配，则它将不会重复使用现有计划，而是会编译一个新计划。

这意味着您可以看到多个缓存的计划，除了这些SET选项的细节外，它们基本上是相同的。

这些“计划重用影响”选项，按字母顺序排列，ANSI_DEFAULTS、ANSI_NULL_DFLT_OFF、ANSI_NULL_DFLT_ON、ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、DATEFIRST、DATEFORMAT、FORCEPLAN、LANGUAGE、NO_BROWSETABLE、NUMERIC_ROUNDABORT和QUOTED_IDENTIFIER。

当SQL Server在编译过程中执行“恒定折叠”时，会检测到这些SET语句，并且似乎在旧版本的SQL Server中，每次调用该过程时，将其中某些SET选项更改为某些值可能会导致重新编译。但是，在最新版本的SQL Server中，很少听到此问题。

但是，明智的改变是SET选项，在批处理开始时，甚至在触发器过程内更改选项，可以导致编译新计划，只有在执行完全相同的批处理或对象，具有完全相同的设置时，才可以重新使用该计划。虽然以这种方式重新编译计划很少会引起主要的性能问题，但确实会带来CPU成本，并且可能会引起问题，尤其是对于编译成本高且执行频率高的复杂查询，甚至可能同时出现这两种情况在多语句程序中。

更改连接设置

对于ODBC、ADO或JDBC连接，为连接的默认设置指定任何更改的方法是，在首次建立连接后执行初步的SET语句批处理。连接字符串中没有允许该操作的选项：必须由SET语句完成。在SSMS中，您可以使用“查询”菜单（“查询” >“查询选项”）为连接的执行行为指定高级和ANSI标准选项。在进行开发和测试时，值得将它们设置为与生产系统连接所使用的相同。这些设置仅反映建立连接时的执行设置。如果随后在连接中的批次中更改设置，则这些设置将用于后续批次。

您会注意到，此选项卡（和ANSI选项卡，没有显示）中的SET选项没有涵盖所有“计划-重用-影响”选项。其余的操作必须在通过SET选项语句建立新连接时完成。

通过更改SET选项更改结果

如前所述，会话SET选项的更改在某些情况下可能导致错误或警告，或者导致查询的结果不同。快速演示值得一提，在这里，我将在每批开始时简单地更改几个SET选项的值：

在ARITHABORT设置为ON的情况下，查询遇到0除时，查询将以错误（我们捕获到这个错误）结束，因此返回2行。当我们关闭此选项时，同一查询将返回3行：

如果检查每个批次的计划，除了这些SET选项的值（打开SELECT操作符的属性以查看它们）之外，您将看到它们是相同的：

以下查询将向我们展示计划缓存中的情况（我已经在PhilFactor数据库中完成了此操作，因此您需要进行更改）。

得到这个结果…

由于SET选项设置不同（235和4331），每个批次都有自己的编译计划。您会注意到，该计划的一个属性set_options，为您提供了所有SET选项的位图值，其中大多数选项为on或off。

每次更改这些设置选项中的一个时，您都会看到专门为该选项集创建的新计划，这显然会增加对缓存的要求以及编译计划所花费的CPU时间。如果您对这两个批次执行十次，您将看到使用了适当的计划，而无需重新编译。

在存储过程中更改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一起使用来促进代码重用，或者在应用程序中，我们正确地使用绑定参数。为了谨慎起见，我们使用表变量。