第十七章配置SQLServer(3)配置“对即时负载的优化”
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了第十七章配置SQLServer(3)配置“对即时负载的优化”,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3987字,纯文字阅读大概需要6分钟。
内容图文
![第十七章配置SQLServer(3)配置“对即时负载的优化”](/upload/InfoBanner/zyjiaocheng/533/e3c38e820f8f40c3aa8690b20eb1c49e.jpg)
前言: 在第一次执行查询或者存储过程时,会创建执行计划并存储在SQLServer的过程缓存内存中。在很多时候,我们会执行一些简单的程序,仅仅执行一次,而为这些查询创建存储过程是非常浪费内存资源的。由于内存不足,可能会导致你的缓存溢出,从而影响性能。
前言:
在第一次执行查询或者存储过程时,会创建执行计划并存储在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 GO
结果如下:
2、 清空缓存和缓冲池:
DBCC FREEPROCCACHE GO
3、 如果想检查是否清空成功,可以再次执行步骤1中的语句:
步骤:
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设为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、 使用以下查询检查:
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
10、这次就出现了下面的截图:
分析:
当新查询执行时,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
内容总结
以上是互联网集市为您收集整理的第十七章配置SQLServer(3)配置“对即时负载的优化”全部内容,希望文章能够帮你解决第十七章配置SQLServer(3)配置“对即时负载的优化”所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。