优化SQL Server的内存占用之执行缓存

MsSQL 前端之家
前端之家收集整理的这篇文章主要介绍了优化SQL Server的内存占用之执行缓存，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

首先说明一下sql Server内存占用由哪几部分组成。sql Server占用的内存主要由三部分组成：数据缓存(Data Buffer)、执行缓存(Procedure Cache)、以及sql Server引擎程序。sql Server引擎程序所占用缓存一般相对变化不大，则我们进行内存调优的主要着眼点在数据缓存和执行缓存的控制上。本文主要介绍一下执行缓存的调优。数据缓存的调优将在另外的文章中介绍。 对于减少执行缓存的占用，主要可以通过使用参数化查询减少内存占用。
1、使用参数化查询减少执行缓存占用
我们通过如下例子来说明一下使用参数化查询对缓存占用的影响。为方便试验，我们使用了一台没有其它负载的sql Server进行如下实验。
下面的脚本循环执行一个简单的查询，共执行10000次。 首先，我们清空一下sql Server已经占用的缓存：
dbcc freeproccache 然后，执行脚本：
<div class="codetitle"><a style="CURSOR: pointer" data="6647" class="copybut" id="copybut6647" onclick="doCopy('code6647')"> 代码如下:

<div class="codebody" id="code6647">
DECLARE @t datetime
SET @t = getdate()
SET NOCOUNT ON
DECLARE @i INT,@count INT,@sql nvarchar(4000) SET @i = 20000
WHILE @i <= 30000
BEGIN
SET @sql = 'SELECT @count=count() FROM P_Order WHERE MobileNo = ' + cast( @i as varchar(10) )
EXEC sp_executesql @sql,N'@count INT OUTPUT',@count OUTPUT
SET @i = @i + 1
END
PRINT DATEDIFF( second,@t,current_timestamp )

输出：
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
11 使用了11秒完成10000次查询。
我们看一下sql Server缓存中所占用的查询计划：
Select Count() CNT,sum(size_in_bytes) TotalSize
From sys.dm_exec_cached_plans 查询结果：共有2628条执行计划缓存在sql Server中。它们所占用的缓存达到：
92172288字节 = 90012KB = 87 MB。 我们也可以使用dbcc memorystatus 命令来检查sql Server的执行缓存和数据缓存占用。
执行结果如下：
<SPAN style="FONT-FAMILY: Calibri">@H_30132@@H301_32@

执行缓存占用了90088KB，有2629个查询计划在缓存里，有1489页空闲内存（每页8KB）可以被数据缓存和其他请求所使用。 我们现在修改一下前面的脚本，然后重新执行一下dbcc freeproccache。再执行一遍修改后的脚本：
代码如下:
DECLARE @t datetime
SET @t = getdate()
SET NOCOUNT ON
DECLARE @i INT,@sql nvarchar(4000) SET @i = 20000
WHILE @i <= 30000
BEGIN
SET @sql = 'select @count=count(*) FROM P_Order WHERE MobileNo = @i'
EXEC sp_executesql @sql,N'@count int output,@i int',@count OUTPUT,@i
SET @i = @i + 1
END
PRINT DATEDIFF( second,current_timestamp )

输出：
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
1
即这次只用1秒钟即完成了10000次查询。
我们再看一下sys.dm_exec_cached_plans中的查询计划：
Select Count(*) CNT,sum(size_in_bytes) TotalSize From sys.dm_exec_cached_plans 查询结果：共有4条执行计划被缓存。它们共占用内存： 172032字节 = 168KB。
如果执行dbcc memorystatus，则得到结果：

有12875页空闲内存(每页8KB)可以被数据缓存所使用。 到这里，我们已经看到了一个反差相当明显的结果。在现实中，这个例子中的前者，正是经常被使用的一种执行sql脚本的方式（例如：在程序中通过合并字符串方式拼成一条sql语句，然后通过ADO.NET或者ADO方式传入sql Server执行）。 解释一下原因：
我们知道，sql语句在执行前首先将被编译并通过查询优化引擎进行优化，从而得到优化后的执行计划，然后按照执行计划被执行。对于整体相似、仅仅是参数不同的sql语句，sql Server可以重用执行计划。但对于不同的sql语句，sql Server并不能重复使用以前的执行计划，而是需要重新编译出一个新的执行计划。同时，sql Server在内存足够使用的情况下，此时并不主动清除以前保存的查询计划（注：对于长时间不再使用的查询计划，sql Server也会定期清理）。这样，不同的sql语句执行方式，就将会大大影响sql Server中存储的查询计划数目。如果限定了sql Server最大可用内存，则过多无用的执行计划占用，将导致sql Server可用内存减少，从而在执行查询时尤其是大的查询时与磁盘发生更多的内存页交换。如果没有限定最大可用内存，则sql Server由于可用内存减少，从而会占用更多内存。 对此，我们一般可以通过两种方式实现参数化查询：一是尽可能使用存储过程执行sql语句（这在现实中已经成为sql Server DBA的一条原则），二是使用sp_executesql 方式执行单个sql语句（注意不要像上面的第一个例子那样使用sp_executesql)。 在现实的同一个软件系统中，大量的负载类型往往是类似的，所区别的也只是每次传入的具体参数值的不同。所以使用参数化查询是必要和可能的。另外，通过这个例子我们也看到，由于使用了参数化查询，不仅仅是优化了sql Server内存占用，而且由于能够重复使用前面被编译的执行计划，使后面的执行不需要再次编译，最终执行10000次查询总共只使用了1秒钟时间。 2、检查并分析sql Server执行缓存中的执行计划
通过上面的介绍，我们可以看到sql缓存所占用的内存大小。也知道了sql Server执行缓存中的内容主要是各种sql语句的执行计划。则要对缓存进行优化，就可以通过具体分析缓存中的执行计划，看看哪些是有用的、哪些是无用的执行计划来分析和定位问题。 通过查询DMV: sys.dm_exec_cached_plans,可以了解数据库中的缓存情况，包括被使用的次数、缓存类型、占用的内存大小等。
SELECT usecounts,cacheobjtype,objtype,size_in_bytes,plan_handle
FROM sys.dm_exec_cached_plans
@H_301_32@@H_301_32@

通过缓存计划的301_32@可以查询到该执行计划详细信息，包括所对应的sql@H_301_32@语句：

SELECT TOP 100 usecounts,

objtype,

p.size_in_bytes,

[sql].[text]

FROM sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql

ORDER BY usecounts

@H_301_32@@H_301_32@

我们可以选择针对那些执行计划占用较大内存、而被重用次数较少的sql@H_301_32@语句进行重点分析。看其调用方式是否合理。另外，也可以对执行计划被重复使用次数较多的sql@H_301_32@语句进行分析，看其执行计划是否已经经过优化。进一步，通过对查询计划的分析，还可以根据需要找到系统中最占用301_32@、cpu@H_301_32@时间、执行次数最多的一些sql@H_301_32@语句，然后进行相应的调优分析。篇幅所限，这里不对此进行过多介绍。读者可以查阅联机丛书中的：301_32@内容得到相关帮助。

附：

301_32@关于301_32@，可以查看微软的知识库301_32@

301_32@关于301_32@和sql_text@H_301_32@，请参阅联机丛书。

内存占用执行缓存

猜你在找的MsSQL相关文章

SQL Server2012高可用之日志传送测试
（一）日志传送架构 （1.1）相关服务器 主服务器 ：用于生产的服务器，上面运行这生产SQL ...
SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制（发布订阅）后，生产数据...
SQL Server2012高可用之事物复制(发布订阅)测试
（一）测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式，其中主库可执行读写...
使用zabbix监控sql server的发布订阅
（一）背景个人在使用sql server时，用到了sql server的发布订阅来做主从同步，类似MySQL的...
SQL学习（四）集合运算
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * fro...
SQL学习（五） 高级处理
5.1 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。 SEL...
SQL学习(一）初识数据库
datawhale组队学习SQL task01
SQL学习(二）基础查询与排序
datawhale数据库组队学习第二天打卡
SQL Server 2005 返回修改后的数据
今天在公司看到同事写了个SQL2005的新特性的文章，觉得很实用，在这里和大家分享下。原著作...
SQL Server 数据库最小宕机迁移方案
一、目的 在做SQL Server数据库维护的时候，当上司要求我们把几十G的数据文件搬动到其它服...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问