SQL Server 执行计划缓存(1)(2)

测试

--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 

手动清空缓存执行计划

---清空制定数据库执行计划 
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在架构更改的时候通过检测执行计划已经对原先的执行计划进行了编译，所以在新的查询中还是使用了第一次查询的执行计划。

博文地址：http://www.cnblogs.com/chenmh/archive/2015/04/20/4438086.html
 

---

---