SQL配置即席查询的优化

前言：

        在第一次执行查询或者存储过程时，会创建执行计划并存储在SQLServer的过程缓存内存中。在很多时候，我们会执行一些简单的程序，仅仅执行一次，而为这些查询创建存储过程是非常浪费内存资源的。由于内存不足，可能会导致你的缓存溢出，从而影响性能。在2005之前，这是一个大问题，为了纠正这个问题。微软在SQLServer 2008中引入了对即时查询负载的优化功能。这个功能在2012也依旧可用。是基于实例级别的。

        很多开发人员直接在生产环境运行和测试查询，如果没有得到期望的结果，会更改查询然后再次执行，这会对过程缓存造成很大压力。所以尽量不要这样做。

在开始之前，在测试服务器清空缓存，但是切记不要在生产环境这样做：

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

结果如图：

---2）清除缓存和缓冲池

dbcc freeproccache

如果想检查是否清空成功，可以再次执行步骤1中的语句：

---3） 执行select 语句，检查在运行了上面语句后是否有计划缓存，再次执行之前查询计划缓存的语句：

--4）把Optimize For Ad Hoc Workioads 设为1
即席查询（ad hoc query）每次都需要重编译，optimize for ad hoc workloads 的原理大概如此：
当你第一次执行 ad hoc 查询的时候，被编译出来的执行计划，sql server 存个一部分。
为什么不全存呢，如果全存，adhoc 查询很频繁，那么不就给 内存带来压力了。 
当你第二次查询的时候，就保存这个执行计划。
当你第三次运行的时候，就可以直接从cache 内读取出来执行计划。

EXEC sp_configure 'show advanced options',1
RECONFIGURE

EXEC sp_configure 'optimize for ad hoc workloads',1
RECONFIGURE 

--5）再次清除缓存和缓冲池

dbcc freeproccache

--6）执行select语句，再查询缓存中的数据（步骤1），结果如图：

分析：

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

        一般情况下，当你执行查询，将会产生执行计划并保存在过程缓存中，所以当你执行步骤1的查询是，会看到服务器有很多计划缓存，但是当执行第六步后的查询是，就发现没有。对于即席查询，如果只执行一次，何必需要缓存呢？

        有些系统的计划缓存达到GB以上，开启后可能减少一半空间。另外，检查即席查询占用了多少空间，可以使用下面的语句：

---1）查看即席查询占用了多少空间

SELECT count(*)as 'Number of Plans',
sum(cast(size_in_bytes as bigint))/1024/1024 AS 'Plan Cache(MB)'
FROM    sys.dm_exec_cached_plans  
WHERE   objtype = 'Adhoc'  
        AND usecounts = 1  

---2）按项目查看
select objtype as 'cached object type',
count (*) as 'Number of Plans',
sum(cast(size_in_bytes as bigint))/1024/1024 AS 'Plan Cache(MB)',
avg(usecounts) as 'Avg use Count'
from sys.dm_exec_cached_plans
group by objtype

原文来自：http://blog.csdn.net/dba_huangzj/article/details/8758183