优化SQL Server的内存占用之执行缓存
前端之家收集整理的这篇文章主要介绍了
优化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">
执行缓存占用了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_execute
sql @
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_execute
sql 方式执行单个
sql语句（注意不要像上面的第一个例子那样使用sp_execute
sql)。 在现实的同一个软件系统中，大量的
负载类型往往是类似的，所区别的也只是每次传入的具体参数值的不同。所以使用参数化
查询是必要和可能的。另外，通过这个例子我们也看到，由于使用了参数化
查询，不仅仅是优化了
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
通过缓存计划的可以查询到该执行计划详细信息，包括所对应的sql语句：
@H_
404_87@SELECT TOP 100 usecounts,
@H_
404_87@ objtype,
@H_
404_87@ p.size_in_bytes,
@H_
404_87@ [
sql].[text]
@H_
404_87@FROM sys.dm_exec_cached_plans p
@H_
404_87@OUTER APPLY sys.dm_exec_
sql_text (p.plan_handle)
sql
@H_
404_87@ORDER BY usecounts
我们可以选择针对那些执行计划占用较大内存、而被重用次数较少的sql语句进行重点分析。看其调用方式是否合理。另外，也可以对执行计划被重复使用次数较多的sql语句进行分析，看其执行计划是否已经经过优化。进一步，通过对查询计划的分析，还可以根据需要找到系统中最占用、cpu时间、执行次数最多的一些sql语句，然后进行相应的调优分析。篇幅所限，这里不对此进行过多介绍。读者可以查阅联机丛书中的：内容得到相关帮助。
附：
@H_
404_87@
关于，可以查看微软的知识库
关于和sql_text，请参阅联机丛书。
原文链接：https://www.f2er.com/mssql/63842.html
