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

核心提示： 对此，我们一般可以通过两种方式实现参数化查询：一是尽可能使用存储过程执行SQL语句（这在现实中已经成为SQL Server DBA的一条原则），优化SQL Server的内存占用之执行缓存(4)，二是使用sp_executesql 方式执行单个SQL语句（注意不要像上面的第一个例子那样使用

对此，我们一般可以通过两种方式实现参数化查询：一是尽可能使用存储过程执行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

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

　　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

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

附：

1:关于DBCC MEMORY， 可以查看微软的知识库: http://support.microsoft.com/kb/907877/EN-US

2:关于sys.dm_exec_cached_plans和sys.dm_exec_sql_text，请参阅联机丛书。