[sql server] 执行计划的缓存和重新使用

最新推荐文章于 2021-01-19 20:23:36 发布

永生天地

最新推荐文章于 2021-01-19 20:23:36 发布

阅读量3.1k

点赞数

[Microsoft] - [sqlserver] 专栏收录该内容

114 篇文章 0 订阅

订阅专栏

本文介绍SQL Server 2005中的执行计划缓存机制，包括执行计划的组成部分、执行计划的重用、老化与重新编译的过程。此外还讨论了执行计划在不同情况下的有效性以及如何通过SQL Server Profiler进行跟踪。

摘要由CSDN通过智能技术生成

执行计划的缓存和重新使用

SQL Server 2005 有一个用于存储执行计划和数据缓冲区的内存池。池内分配给执行计划或数据缓冲区的百分比随系统状态动态波动。内存池中用于存储执行计划的部分称为过程缓存。

SQL Server 2005 执行计划包含下列主要组件：

查询计划

执行计划的主体是一个重入的只读数据结构，可由任意数量的用户使用。这称为查询计划。查询计划中不存储用户上下文。内存中永远不会有两个或多个查询计划副本：一个副本用于所有的串行执行，另一个用于所有的并行执行。并行副本覆盖所有的并行执行，与并行执行的并行度无关。
执行上下文

每个正在执行查询的用户都有一个包含其执行专用数据（如参数值）的数据结构。此数据结构称为执行上下文。执行上下文数据结构可以重新使用。如果用户执行查询而其中的一个结构未使用，将会用新用户的上下文重新初始化该结构。

在 SQL Server 2005 中执行任何 SQL 语句时，关系引擎将首先查看过程缓存中是否有用于同一 SQL 语句的现有执行计划。SQL Server 2005 将重新使用找到的任何现有计划，从而节省重新编译 SQL 语句的开销。如果没有现有执行计划，则 SQL Server 2005 将为查询生成新的执行计划。

SQL Server 2005 有一个高效的算法，可查找用于任何特定 SQL 语句的现有执行计划。在大多数系统中，这种扫描所使用的最小资源比通过重新使用现有计划而不是编译每个 SQL 语句所节省的资源要少。

该算法将新的 SQL 语句与缓存内现有的未用执行计划相匹配，并要求所有的对象引用完全合法。例如，这两个 SELECT 语句中的第一个语句与现有计划不匹配，而第二个语句则匹配：

	复制代码
SELECT * FROM Person.Contact SELECT * FROM AdventureWorks.Person.Contact

在 SQL Server 2000 和 SQL Server 2005 实例中，个别执行计划重新使用的概率比在 SQL Server 6.5 及更早版本中高。

执行计划的老化

生成执行计划后，它处于过程缓存中。只有当需要空间时，SQL Server 2005 才使旧的未用计划从缓存老化掉。每个查询计划和执行环境都有相关的成本因子，可表明编译结构所需的费用。这些数据结构还有一个年龄字段。对象每由连接引用一次，其年龄字段便按编译成本因子递增。例如，如果查询计划的成本因子为 8 并且被引用了两次，则其年龄变为 16。惰性写入器进程定期扫描过程缓存中的对象列表。然后，惰性写入器减少每个对象的年龄字段，每扫描一次减少 1。在本例中，查询计划的年龄经过 16 次过程缓存扫描后减为 0，除非其他用户引用了该计划。如果满足下面三个条件，惰性写入器进程将释放对象：

内存管理器需要内存而所有可用内存都正在使用。
对象的年龄字段是 0。
对象在当前没有被连接引用。

因为每次引用对象时其年龄字段都会增加，所以经常被引用的对象的年龄字段不会减为 0，也不会从缓存老化掉。不经常被引用的对象将很快满足释放条件，但是不会真被释放，除非其他对象有内存需求。

重新编译执行计划

根据数据库的新状态，数据库内的某些更改可能会导致执行计划效率低下或不再有效。SQL Server 检测这些使执行计划无效的更改，并将计划标记为无效。此后，必须为执行查询的下一个连接重新编译新的计划。导致计划无效的情况包括：

对查询所引用的表或视图进行更改（ALTER TABLE 和 ALTER VIEW）。
对执行计划所使用的任何索引进行更改。
对执行计划所使用的统计信息进行更新，该更新可能是从语句（如 UPDATE STATISTICS）中显示生成，也可能是自动生成的。
删除执行计划所使用的索引。
显式调用 sp_recompile。
对键的大量更改（其他用户对由查询引用的表使用 INSERT 或 DELETE 语句所产生的修改）。
对于带触发器的表，插入的或删除的表内的行数显著增长。

为了使语句正确，或要获得可能更快的查询执行计划，大多数都需要进行重新编译。

在 SQL Server 2000 中，如果批处理中的某条语句导致了重新编译，则不管是通过存储过程、触发器、特殊批处理提交，还是通过准备好的语句提交，都将重新编译整个批处理。在 SQL Server 2005 中，只有在批处理中导致重新编译的语句才会被重新编译。因为这个差异，在 SQL Server 2000 中将对重新编译计数，而在 SQL Server 2005 中则不需要。而且，在 SQL Server 2005 中有更多类型的重新编译，因为它扩展了功能集。

语句级重新编译有助于提高性能，因为在大多数情况下，只有少数语句导致了重新编译并造成相关损失（指 CPU 时间和锁）。因此，避免了批处理中其他不必重新编译的语句的这些损失。

在 SQL Server 2005 中，SQL Server Profiler SP:Recompile 跟踪事件报告语句级重新编译。在 SQL Server 2000 中，此跟踪事件只报告批处理重新编译。而且，在 SQL Server 2005 中，将填充此事件的 TextData 列。因此，不再需要 SQL Server 2000 中所使用的方法：必须跟踪 SP:StmtStarting 或 SP:StmtCompleted 以获得导致重新编译的 Transact-SQL 文本。

SQL Server 2005 也添加了一个新跟踪事件，称为 SQL:StmtRecompile，它报告语句级重新编译。它还可用于跟踪和调试重新编译。只为存储过程和触发器生成 SP:Recompile ，而为存储过程、触发器、特殊批处理、使用 sp_executesql 执行的批处理、准备好的查询以及动态 SQL 生成 SQL:StmtRecompile。

SP:Recompile 和 SQL:StmtRecompile 的 EventSubClass 列都包含一个整数代码，用以指明重新编译的原因。下表包含每个代码号的意思。

EventSubClass 值	说明
1	架构已更改。
2	统计信息已更改。
3	编译已延迟。
4	SET 选项已更改。
5	临时表已更改。
6	远程行集已更改。
7	FOR BROWSE 权限已更改。
8	查询通知环境已更改。
9	分区视图已更改。
10	游标选项已更改。
11	已请求 OPTION (RECOMPILE)。

注意：
当 AUTO_UPDATE_STATISTICS 数据库选项被设置为 ON 时，如果查询以表或索引视图为目标，而表或索引视图的统计信息自上次执行后已更新或基数已发生很大变化，查询将被重新编译。此行为适用于标准用户定义表、临时表以及由 DML 触发器创建的 inserted 和 deleted 表。如果过多的重新编译影响到查询的性能，请考虑将此设置更改为 OFF。当 AUTO_UPDATE_STATISTICS 数据库选项设置为 OFF 时，不会发生基于统计信息或基数变化的重新编译。请注意，在 SQL Server 2000 中，即使此设置为 OFF，查询仍然会基于 DML 触发器 inserted 和 deleted 表的基数变化进行重新编译。有关禁用 AUTO_UPDATE_STATISTICS 的详细信息，请参阅索引统计信息。

注意：

当 AUTO_UPDATE_STATISTICS 数据库选项被设置为 ON 时，如果查询以表或索引视图为目标，而表或索引视图的统计信息自上次执行后已更新或基数已发生很大变化，查询将被重新编译。此行为适用于标准用户定义表、临时表以及由 DML 触发器创建的 inserted 和 deleted 表。如果过多的重新编译影响到查询的性能，请考虑将此设置更改为 OFF。当 AUTO_UPDATE_STATISTICS 数据库选项设置为 OFF 时，不会发生基于统计信息或基数变化的重新编译。请注意，在 SQL Server 2000 中，即使此设置为 OFF，查询仍然会基于 DML 触发器 inserted 和 deleted 表的基数变化进行重新编译。有关禁用 AUTO_UPDATE_STATISTICS 的详细信息，请参阅索引统计信息。