SQL内存优化-最⼤化使⽤内存
1. 重启sql server
第⼀步，打开记事本，输⼊下列2⾏命令：
net stop mssqlserver
net start mssqlserver
将其存为⼀个.bat的⽂件。
第⼆步，在Windows的“任务计划”功能⾥，添加⼀条新的任务计划，让系统在每天的 03:00执⾏⼀次这个.bat 这个批处理⽂件即可。
2.  内存调优
SQL Server占⽤的内存主要由三部分组成：数据缓存(Data Buffer)、执⾏缓存(Procedure Cache)、以及SQL Server引擎程序。SQL Server 引擎程序所占⽤缓存⼀般相对变化不⼤，则我们进⾏内存调优的主要着眼点在数据缓存和执⾏缓存的控制上。
对于减少执⾏缓存的占⽤，主要可以通过使⽤参数化查询减少内存占⽤。
1、使⽤参数化查询减少执⾏缓存占⽤
我们通过如下例⼦来说明⼀下使⽤参数化查询对缓存占⽤的影响。为⽅便试验，我们使⽤了⼀台没有其它负载的SQL Server进⾏如下实验。下⾯的脚本循环执⾏⼀个简单的查询，共执⾏10000次。
⾸先，我们清空⼀下SQL Server已经占⽤的缓存：
dbcc freeproccache
然后，执⾏脚本： 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的执⾏缓存和数据缓存占⽤。
执⾏结果如下：
执⾏缓存占⽤了90088KB，有2629个查询计划在缓存⾥，有1489页空闲内存（每页8KB）可以被数据缓存和其他请求所使⽤。
我们现在修改⼀下前⾯的脚本，然后重新执⾏⼀下dbcc freeproccache。再执⾏⼀遍修改后的脚本：
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 = @i'
EXEC sp_executesql @sql, int output, @i int', @count OUTPUT, @i
SET @i = @i + 1
END
PRINT DATEDIFF( second, @t, 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
通过缓存计划的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) sqlsql语句优化方式
ORDER BY usecounts
Ok，完成了，SQL压⼒有所缓解

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。