文档彩票走势图>>SQL Prompt教程>>SQL Prompt使用教程：更改过程或触发器中的SET选项将导致重新编译（上）

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

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

点击下载SQL Prompt正式版

有时，由于某种显而易见的原因，您将有一个存储过程或触发器间歇地花费更长的时间运行。您已经检查了索引，排除了诸如参数嗅探之类的问题，但是间歇性的性能问题仍然存在。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标准选项。在进行开发和测试时，值得将它们设置为与生产系统连接所使用的相同。这些设置仅反映建立连接时的执行设置。如果随后在连接中的批次中更改设置，则这些设置将用于后续批次。

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