笔记240 配置“对即时负载的优化” 对于ad-hoc 类型语句的缓存优化适用于 SQL2008 或以上

笔记240 配置“对即时负载的优化” 对于ad-hoc 类型语句的缓存优化适用于 SQL2008 或以上

-- 配置“对即时负载的优化” 对于ad-hoc 类型语句的缓存优化适用于 SQL2008 或以上

-- 前言：
--        在第一次执行查询或者存储过程时，会创建执行计划并存储在 SQLServer 的过程缓存内存中。在很多时候，我们会执行一些简单的程序，仅仅执行一次，而为这些查询创建存储过程是非常浪费内存资源的。由于内存不足，可能会导致你的缓存溢出，从而影响性能。在之前，这是一个大问题，为了纠正这个问题。微软在 SQLServer 2008 中引入了对即时查询负载的优化功能。这个功能在也依旧可用。是基于实例级别的。
--        很多开发人员直接在生产环境运行和测试查询，如果没有得到期望的结果，会更改查询然后再次执行，这会对过程缓存造成很大压力。所以尽量不要这样做

-- 准备工作：
-- 在开始之前，在测试服务器清空缓存，但是切记不要在生产环境这样做：
--1 、先看看有多少数据保存在缓存中：


SELECT   CP. usecounts AS CountOfQueryExecution ,
        CP . cacheobjtype AS CacheObjectType ,
        CP . objtype AS ObjectType ,
        ST . text AS QueryText
FROM     sys.dm_exec_cached_plans AS CP
        CROSS APPLY sys.dm_exec_sql_text (plan_handle ) AS ST
WHERE    CP. usecounts > 0
GO

--2 、清空缓存和缓冲池：
DBCC FREEPROCCACHE
GO

--3 、如果想检查是否清空成功，可以再次执行步骤中的语句：
SELECT   CP. usecounts AS CountOfQueryExecution ,
        CP . cacheobjtype AS CacheObjectType ,
        CP . objtype AS ObjectType ,
        ST . text AS QueryText
FROM     sys.dm_exec_cached_plans AS CP
        CROSS APPLY sys.dm_exec_sql_text (plan_handle ) AS ST
WHERE    CP. usecounts > 0
GO


-- 步骤：
--1 、执行下面语句：
USE AdventureWorks
GO
SELECT   *
FROM     Sales. SalesOrderDetail
WHERE    SalesOrderDetailID = 43659
GO

--2 、检查在运行了上面语句后是否有计划缓存，再次执行之前查询计划缓存的语句
SELECT   CP. usecounts AS CountOfQueryExecution ,
        CP . cacheobjtype AS CacheObjectType ,
        CP . objtype AS ObjectType ,
        ST . text AS QueryText
FROM     sys.dm_exec_cached_plans AS CP
        CROSS APPLY sys.dm_exec_sql_text (plan_handle ) AS ST
WHERE    CP. usecounts > 0
GO

--3 、下面是结果，当然，也可以在 where 条件中用like 来减少查找的数据量：
-- 也可以使用 ctrl+alt+a来开启活动监视器来查找运行时间长的查询。


--4 、现在来把 Optimize for Ad hoc Workloads设为：
EXEC sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE
GO

--5 、然后再次清空缓存：
DBCC FREEPROCCACHE
GO

--6 、再次执行语句：
USE AdventureWorks
GO
SELECT   *
FROM     Sales. SalesOrderDetail
WHERE    SalesOrderDetailID = 43659
GO


--7 、可以执行下面的语句检查是否有新的缓存进入：
SELECT   CP. usecounts AS CountOfQueryExecution ,
        CP . cacheobjtype AS CacheObjectType ,
        CP . objtype AS ObjectType ,
        ST . text AS QueryText
FROM     sys.dm_exec_cached_plans AS CP
        CROSS APPLY sys.dm_exec_sql_text (plan_handle ) AS ST
WHERE    CP. usecounts > 0
        AND ST. text LIKE '%SELECT  *  FROM    Sales.SalesOrderDetail  WHERE   SalesOrderDetailID = 43659  %'
        AND CP. cacheobjtype = 'Compiled Plan'
GO


--8 、你会发现里面没有数据，现在再次执行下面语句：
USE AdventureWorks
GO
SELECT   *
FROM     Sales. SalesOrderDetail
WHERE    SalesOrderDetailID = 43659
GO


--9 、使用以下查询检查：
-- 缓存起来了

-- 分析：
-- 当新查询执行时， query_hash值会在内存中生成，而不是整个执行计划，当相同的查询第二次执行的时候， SQLServer 会查找是否已经存在这个 query_hash ，如果不存在，执行计划将保存在缓存中。这样就使得仅执行一次的查询将不会保存执行计划到缓存中。所以强烈建议打开这个配置。这个配置不造成任何负面影响，但是可以节省计划缓存的空间。


-- 一般情况下，当你执行查询，将会产生执行计划并保存在过程缓存中，所以当你执行步骤的查询是，会看到服务器有很多计划缓存，但是当执行第六步后的查询是，就发现没有。对于即席查询，如果只执行一次，何必需要缓存呢？
-- 有些系统的计划缓存达到 GB 以上，开启后可能减少一半空间。



-- 另外，如果你好奇即席查询占用了多少空间，可以使用下面的语句：
SELECT   SUM ( size_in_bytes) AS TotalByteConsumedByAdHoc
FROM     sys.dm_exec_cached_plans
WHERE    objtype = 'Adhoc'
        AND usecounts = 1

-- 总的来讲就是 :SQL2008或以上可以配置即席第一次查询语句的时候不缓存执行计划 , 这个查询语句是 SQL语句
-- 不是存储过程、函数、触发器 , 只有第二次查询的时候才缓存
-- 减轻服务器缓存 /内存压力