--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),('',2),('',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()