以下的文章主要向大家讲述的是正确优化SQL Server内存占用的执行缓存的实际操作方案，使用参数化查询来对执行缓存占用进行减少。我们主要是通过如下例子来说明一下正确使用参数化查询对缓存占用的影响。
为方便试验，我们使用了一台没有其它负载的SQL Server进行如下实验。
下面的脚本循环执行一个简单的查询，共执行10000次。首先，我们清空一下SQL Server已经占用的缓存：
dbcc freeproccache然后，执行脚本：
)输出：DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
11使用了11秒完成10000次查询。我们看一下SQL Server缓存中所占用的查询计划：SELECT Count(*) CNT,sum(size_in_bytes) TotalSizeFrom sys.dm_exec_cached_plans查询结果：共有2628条执行计划缓存在SQL Server中。它们所占用的缓存达到：
92172288字节 = 90012KB = 87 MB。我们也可以使用dbcc memorystatus 命令来检查SQL Server的执行缓存和数据缓存占用。执行结果如下：
执行缓存占用了90088KB，有2629个查询计划在缓存里，有1489页空闲内存（每页8KB）可以被数据缓存和其他请求所使用。我们现在修改一下前面的脚本，然后重新执行一下dbcc freeproccache。
再执行一遍修改后的脚本：
输出：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 Server内存占用，从而得到优化后的执行计划，然后按照执行计划被执行。对于整体相似、仅仅是参数不同的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秒钟时间。
【编辑推荐】
如何解决MSSQL占用过多内存的简单方法介绍
一,看看是不是本身你的数据库数据多,如果是这样的话,数据库又用得频繁,那占用CPU多是正常的.只有优化程序代码或数SQL本身,比如一些改为储存过程来实现二,试着升级一下SQL,可以升级到SP3之类的.网上有下载的
win10内存占用高解决win10内存不足占用高怎么办
解决方法：方法一：1、结束“占用内存高且不再使用”的进程。 2、在“任务管理器”界面中，切换到“进程”选项卡，点击“内存”属性项，让所有进程按占用内存大小进行排序，选中占用内存高且不再使用进程，点击“结束任务”按钮。 方法二：利用“360加速球”清理系统。 点击“360加速球”，从其“加速”界面中，勾选要结束的进程，点击“一键加速”按钮即可结束占用内存的进程，从而加速Win10运行速度。 方法三：增大“虚拟内存”，将硬件充当内存来更流程的运行程序。 虽然虚拟内存比物理内存在速度上要慢许多，但对于释放物理内存是有效果的。 1、右击Win10桌面左下角的“Windows”按钮，从其扩展面板中选择“系统”项进入。 2、从打开的“系统”窗口中，点击左上角的“高级系统设置”按钮。 3、并从打开的“系统属性”窗口中，切换到“高级”选项卡，点击“性能”栏目中的“设置”按钮。 4、待打开“性能选项”窗口后，切换到“高级”选项卡，点击“虚拟内存”栏目中的“设置”按钮。 5、最后在打开的“虚拟内存”界面中，根据实际需要手动设置每个磁盘的虚拟内存，建议将虚拟内存设置为实际物理内存的2至4倍大小。 6、最后根据实际情况，保存相关设置，重启一下计算机，让有关“虚拟内存”的相关设置生效。 方法四：利用“360优化加速”功能来提升Win10正式版运行速度。 1、打开 “360安全卫士”-“优化加速”程序主界面，确保勾选“系统加速”项，点击“立即扫描”按钮。 2、待扫描完成后，勾选要进行优化的项，点击“立即优化”按钮，即可完成内存的释放以及开机加速、网络优化等功能。 3、此外，我们还需要关闭“SuperFetch”（超级预读服务）。 4、按“Windows+R”打开“运行”窗口，输入“进入”服务“窗口。 5、从打开的“系统服务”窗口中，找到“SuperFetch”右击选择“停止”项，或者在选中此服务的情况下，点击工具栏中的“停止此服务”按钮将此服务停止掉。 即可有效减少Win10系统的内存占用量。
如何了解 MS SQL-Server7.0 性能分析参数
当你怀疑计算机硬件是影响 SQL Server 运行性能的主要原因时，可以通过 SQL Server Performance Monitor 监视相应硬件的负载，以证实你的猜测并找出系统瓶颈。 以下将介绍一些常用的分析对象及其参数。 Memory: Page Faults / sec 参数： 如果该值偶尔走高，表明当时有线程竞争内存。 如果持续很高，则内存可能是瓶颈。 Process: Working Set 参数： SQL Server 的该参数应该非常接近分配给 SQL Server 的内存值。 在 SQL Server 设定中，如果将“set working set size”置为 0，则 Windows NT 会决定 SQL Server 的工作集的大小。 如果将“set working set size”置为 1，则强制工作集大小为 SQL Server 的分配内存大小。 一般情况下最好不要改变“set working set size”的缺省值。 Process:%Processor Time 参数： 如果该参数值持续超过 95%，表明瓶颈是 CPU。 可以考虑增加一个处理器或换一个更快的处理器。 Processor:%Privileged Time 参数： 如果该参数值和“Physical Disk”参数值一直很高，表明 I/O 有问题。 可考虑更换更快的硬盘系统。 另外设置 Tempdb in RAM，减低“max async IO”、“max lazy writer IO”等措施都会降低该值。 Processor:%User Time 参数： 表示耗费 CPU 的数据库操作，如排序、执行 aggregate functions 等。 如果该值很高，可考虑增加索引、尽量使用简单的表联接、水平分割大表格等方法来降低该值。 Physical Queue Length 参数： 该值应不超过磁盘数的 1.5 至 2 倍。 要提高性能，可增加磁盘。 注意：一个 Raid Disk 实际有多个磁盘。 SQLServer:Cache Hit Ratio 参数： 该值越高越好。 如果持续低于 80%，应考虑增加内存。 注意该参数值是从 SQL Server 启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。
发表评论