[置顶] 第十七章——配置SQLServer（3）——配置“对即时负载的优化”

心已赠人 2022-03-28 07:31 29阅读 0赞

# 前言： #

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

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

# 准备工作： #

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

1、 先看看有多少数据保存在缓存中：

结果如下：

![1365011795_4096.png][]

2、 清空缓存和缓冲池：

DBCC FREEPROCCACHE 
    GO

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

![1365011799_5720.png][]

# 步骤： #

1、 执行下面语句：

USE AdventureWorks
    GO
    SELECT  *
    FROM    Sales.SalesOrderDetail
    WHERE   SalesOrderDetailID = 43659
    GO

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

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

![1365011803_3543.png][]

4、 现在来把Optimize for Ad hoc Workloads设为1：

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、 使用以下查询检查：

10、这次就出现了下面的截图：

![1365011809_4331.png][]

# 分析： #

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

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

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

SELECT  SUM(size_in_bytes) AS TotalByteConsumedByAdHoc
    FROM    sys.dm_exec_cached_plans
    WHERE   objtype = 'Adhoc'
            AND usecounts = 1

[1365011795_4096.png]: https://img-my.csdn.net/uploads/201304/04/1365011795_4096.png
[1365011799_5720.png]: https://img-my.csdn.net/uploads/201304/04/1365011799_5720.png
[1365011803_3543.png]: https://img-my.csdn.net/uploads/201304/04/1365011803_3543.png
[1365011809_4331.png]: https://img-my.csdn.net/uploads/201304/04/1365011809_4331.png