SQL Server 执行计划缓存

efx4 10年前

原文出处： pursuer.chen  

概述

了解执行计划对数据库性能分析很重要，其中涉及到了语句性能分析与存储，这也是写这篇文章的目的，在了解执行计划之前先要了解一些基础知识，所以文章前面会讲一些概念，学起来会比较枯燥，但是这些基础知识非常重要。

 

目录

1. 概述
2. 基础概念
2.1 怎样缓存执行计划
2.2 SQL Server自动删除执行计划
2.3 重新编译执行计划
3. 测试
3.1 执行计划相关系统视图
3.2 手动清空缓存执行计划
3.3 测试索引更改对执行计划的影响
3.4 测试增加字段对执行计划的影响
4. 总结

基础概念

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

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

  • 查询计划

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

  • 执行上下文

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

怎样缓存执行计划

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

SQL Server自动删除执行计划

 

什么情况下会删除执行计划

在没有人工手动清除缓存的情况下，如果出现内存不足的情况下SQL Server会自动清除一部分没被利用到的缓存计划。

所有缓存的最大大小取决于max server memory的大小。

怎样判断需要删除的执行计划

果存在内存不足的情况，数据库引擎将使用基于开销的方法来确定从过程缓存中删除哪些执行计划。怎样确定一个执行计划的开销呢，对于一个第一次执行的执行计划SQL Server将它的开销值设为0，被多次执行过的执行计划SQL Server将它的开销值设置为原始编译开销，所以数据库引擎会重复检查每个执行计划的状态并将删除当前开销为零的执行计划。如果存在内存不足的情况，当前开销为零的执行计划不会自动被删除，而只有在数据库引擎检查该执行计划并发现其当前开销为零时，才会删除该计划。当检查执行计划时，如果当前没有查询使用该计划，则数据库引擎将降低当前开销以将其推向零。

数据库引擎会重复检查执行计划，直至删除了足够多的执行计划，以满足内存需求为止。如果存在内存不足的情况，执行计划可多次对其开销进行增加或降低。如果内存不足的情况已经消失，数据库引擎将不再降低未使用执行计划的当前开销，并且所有执行计划都将保留在过程缓存中，即使其开销为零也是如此。

重新编译执行计划

 

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

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

 

测试

 

执行计划相关系统视图 

--1.缓存的每一个对象返回一行，包括缓存计划的类型、缓存引用的对象、缓存计划占用的空间、被使用次数、以及创建时间等  SELECT * FROM sys.syscacheobjects;  --2.缓存的每个查询计划返回一行，包括执行计划被使用的次数、执行计划的大小、内存地址、执行计划的类型、语句等  SELECT * FROM sys.dm_exec_cached_plans;  GO  ---3.返回由指定的 sql_handle 标识的 SQL 批处理的文本  /*其中sql_handle来自:  sys.dm_exec_query_stats  sys.dm_exec_requests  sys.dm_exec_cursors  sys.dm_exec_xml_handles  sys.dm_exec_query_memory_grants  sys.dm_exec_connections  plan_handle来自:sys.dm_exec_cached_plans  */   SELECT * FROM sys.dm_exec_sql_text(sql_handle | plan_handle);  GO  --4.以 XML 格式返回计划句柄指定的批查询的显示计划，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄  SELECT * FROM sys.dm_exec_query_plan(plan_handle);  GO  --5.每个计划属性返回一行，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄  SELECT * FROM sys.dm_exec_plan_attributes(plan_handle);  GO  --6.针对每个 Transact-SQL 执行计划、公共语言运行时 (CLR) 执行计划和与计划关联的游标返回一行,，主要接受来自sys.dm_exec_cached_plans的plan_handle句柄  SELECT * FROM sys.dm_exec_cached_plan_dependent_objects(plan_handle);    --7.返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。在从缓存删除计划时，也将从该视图中删除对应行。*/  --该系统视图针对每一个缓存中的执行计划统计其执行时间、物理、逻辑操作等信息  SELECT * FROM sys.dm_exec_query_stats

手动清空缓存执行计划 

--清空缓存中的执行计划  DBCC FREEPROCCACHE; -- ( plan_handle | sql_handle | pool_name )  GO  -- 清空制定数据库的执行计划  DBCC FLUSHPROCINDB(<dbid>);  GO  ---清空缓存中的数据  DBCC DROPCLEANBUFFERS;    ---清空特定缓存存储区中的执行计划  DBCC FREESYSTEMCACHE(<cachestore>) -- 'ALL', pool_name, 'Object Plans', 'SQL Plans', 'Bound Trees'  GO

测试索引更改对执行计划的影响 

---清空制定数据库执行计划  DECLARE @DBID INT  SET @DBID=DB_ID()  DBCC FLUSHPROCINDB(@DBID);  GO    ---创建测试数据库  CREATE TABLE TPlan  (ID INT PRIMARY KEY IDENTITY(1,1),  Name NVARCHAR(20) NOT NULL,  Istate INT NOT NULL,  Idate DATETIME DEFAULT(GETDATE())  )  GO  ---创建索引  CREATE INDEX IX_TPlan_NAME ON TPlan  (Name  )  GO  INSERT INTO TPlan(Name,Istate)  VALUES('1',1),('2',2),('3',3)    GO  SELECT NAME FROM TPlan  GO  SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjects  WHERE DBID=DB_ID()

( 查看大图 )
使用Profiler监控

使用SQL:StmtRecompile监控，如果是监控存储过程则使用：SP:Recompile

（ 查看大图 ）
修改索引

在索引中添加字段 

DROP INDEX [IX_TPlan_NAME] ON [dbo].[TPlan] WITH ( ONLINE = OFF )  GO  USE [Study]  GO  CREATE NONCLUSTERED INDEX [IX_TPlan_NAME] ON [dbo].[TPlan]   (      [Name] ASC  )  INCLUDE ( [Istate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]  GO
再执行查询 
SELECT NAME FROM TPlan

（ 查看大图 ）

测试增加字段对执行计划的影响

 

增加查询非相关字段 
ALTER TABLE [dbo].[TPlan]   ADD Number INT

（ 查看大图 ）

删除查询有关的索引也同样会导致执行计划重编译，这里就不截图贴出来了。

查看执行计划 
SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjects  WHERE DBID=DB_ID()

（ 查看大图 ）

执行计划中显示了该执行计划被调用了两次，在随机丛书中写的是会重新编译新的执行计划，如果是这样的话那这里的值应该是1才对。

( 查看大图 )

猜测：SQL Server在架构更改的时候通过检测执行计划已经对原先的执行计划进行了编译，所以在新的查询中还是使用了第一次查询的执行计划。

如果有谁知道结果麻烦告知。

 

总结

如果文章对大家有帮助，希望大家能给个推荐，谢谢！！！