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

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

  1 -- 配置“对即时负载的优化” 对于ad-hoc 类型语句的缓存优化适用于 SQL2008 或以上
  2 
  3 -- 前言:
  4 --        在第一次执行查询或者存储过程时,会创建执行计划并存储在 SQLServer 的过程缓存内存中。在很多时候,我们会执行一些简单的程序,仅仅执行一次,而为这些查询创建存储过程是非常浪费内存资源的。由于内存不足,可能会导致你的缓存溢出,从而影响性能。在之前,这是一个大问题,为了纠正这个问题。微软在 SQLServer 2008 中引入了对即时查询负载的优化功能。这个功能在也依旧可用。是基于实例级别的。
  5 --        很多开发人员直接在生产环境运行和测试查询,如果没有得到期望的结果,会更改查询然后再次执行,这会对过程缓存造成很大压力。所以尽量不要这样做
  6 
  7 -- 准备工作:
  8 -- 在开始之前,在测试服务器清空缓存,但是切记不要在生产环境这样做:
  9 --1 、先看看有多少数据保存在缓存中:
 10 
 11 
 12 SELECT   CP. usecounts AS CountOfQueryExecution ,
 13         CP . cacheobjtype AS CacheObjectType ,
 14         CP . objtype AS ObjectType ,
 15         ST . text AS QueryText
 16 FROM     sys.dm_exec_cached_plans AS CP
 17         CROSS APPLY sys.dm_exec_sql_text (plan_handle ) AS ST
 18 WHERE    CP. usecounts > 0
 19 GO
 20 
 21 --2 、清空缓存和缓冲池:
 22 DBCC FREEPROCCACHE
 23 GO
 24 
 25 --3 、如果想检查是否清空成功,可以再次执行步骤中的语句:
 26 SELECT   CP. usecounts AS CountOfQueryExecution ,
 27         CP . cacheobjtype AS CacheObjectType ,
 28         CP . objtype AS ObjectType ,
 29         ST . text AS QueryText
 30 FROM     sys.dm_exec_cached_plans AS CP
 31         CROSS APPLY sys.dm_exec_sql_text (plan_handle ) AS ST
 32 WHERE    CP. usecounts > 0
 33 GO
 34 
 35 
 36 -- 步骤:
 37 --1 、执行下面语句:
 38 USE AdventureWorks
 39 GO
 40 SELECT   *
 41 FROM     Sales. SalesOrderDetail
 42 WHERE    SalesOrderDetailID = 43659
 43 GO
 44 
 45 --2 、检查在运行了上面语句后是否有计划缓存,再次执行之前查询计划缓存的语句
 46 SELECT   CP. usecounts AS CountOfQueryExecution ,
 47         CP . cacheobjtype AS CacheObjectType ,
 48         CP . objtype AS ObjectType ,
 49         ST . text AS QueryText
 50 FROM     sys.dm_exec_cached_plans AS CP
 51         CROSS APPLY sys.dm_exec_sql_text (plan_handle ) AS ST
 52 WHERE    CP. usecounts > 0
 53 GO
 54 
 55 --3 、下面是结果,当然,也可以在 where 条件中用like 来减少查找的数据量:
 56 -- 也可以使用 ctrl+alt+a来开启活动监视器来查找运行时间长的查询。
 57 
 58 
 59 --4 、现在来把 Optimize for Ad hoc Workloads设为:
 60 EXEC sp_configure 'optimize for ad hoc workloads', 1
 61 RECONFIGURE
 62 GO
 63 
 64 --5 、然后再次清空缓存:
 65 DBCC FREEPROCCACHE
 66 GO
 67 
 68 --6 、再次执行语句:
 69 USE AdventureWorks
 70 GO
 71 SELECT   *
 72 FROM     Sales. SalesOrderDetail
 73 WHERE    SalesOrderDetailID = 43659
 74 GO
 75 
 76 
 77 --7 、可以执行下面的语句检查是否有新的缓存进入:
 78 SELECT   CP. usecounts AS CountOfQueryExecution ,
 79         CP . cacheobjtype AS CacheObjectType ,
 80         CP . objtype AS ObjectType ,
 81         ST . text AS QueryText
 82 FROM     sys.dm_exec_cached_plans AS CP
 83         CROSS APPLY sys.dm_exec_sql_text (plan_handle ) AS ST
 84 WHERE    CP. usecounts > 0
 85         AND ST. text LIKE '%SELECT  *  FROM    Sales.SalesOrderDetail  WHERE   SalesOrderDetailID = 43659  %'
 86         AND CP. cacheobjtype = 'Compiled Plan'
 87 GO
 88 
 89 
 90 --8 、你会发现里面没有数据,现在再次执行下面语句:
 91 USE AdventureWorks
 92 GO
 93 SELECT   *
 94 FROM     Sales. SalesOrderDetail
 95 WHERE    SalesOrderDetailID = 43659
 96 GO
 97 
 98 
 99 --9 、使用以下查询检查:
100 -- 缓存起来了
101 
102 -- 分析:
103 -- 当新查询执行时, query_hash值会在内存中生成,而不是整个执行计划,当相同的查询第二次执行的时候, SQLServer 会查找是否已经存在这个 query_hash ,如果不存在,执行计划将保存在缓存中。这样就使得仅执行一次的查询将不会保存执行计划到缓存中。所以强烈建议打开这个配置。这个配置不造成任何负面影响,但是可以节省计划缓存的空间。
104 
105 
106 -- 一般情况下,当你执行查询,将会产生执行计划并保存在过程缓存中,所以当你执行步骤的查询是,会看到服务器有很多计划缓存,但是当执行第六步后的查询是,就发现没有。对于即席查询,如果只执行一次,何必需要缓存呢?
107 -- 有些系统的计划缓存达到 GB 以上,开启后可能减少一半空间。
108 
109 
110 
111 -- 另外,如果你好奇即席查询占用了多少空间,可以使用下面的语句:
112 SELECT   SUM ( size_in_bytes) AS TotalByteConsumedByAdHoc
113 FROM     sys.dm_exec_cached_plans
114 WHERE    objtype = 'Adhoc'
115         AND usecounts = 1
116 
117 -- 总的来讲就是 :SQL2008或以上可以配置即席第一次查询语句的时候不缓存执行计划 , 这个查询语句是 SQL语句
118 -- 不是存储过程、函数、触发器 , 只有第二次查询的时候才缓存
119 -- 减轻服务器缓存 /内存压力
120   

 

posted @ 2013-08-04 19:54 桦仔 阅读(...) 评论(...)  编辑 收藏