SQL Server 执行计划缓存

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

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