诊断SQL Server是否有内存压力

我们知道SQL Server会尽可能的获得内存来保证自己的运行，但占用内存是一个渐进的过程。SQL Server启动后，不会瞬间占用很多内存，只有当SQL Server需要更多内存时，它才试图获取更多的内存。当SQL Server不忙时，也不会释放不需要的内存(除非重启SQL Server)。因此SQL Server在启动后，会逐渐占用越来越多的内存，直到无法获取更多内存为止。因此SQL Server服务器上，在OS层面内存看起来总是快耗尽的样子。


为什么SQL Server要这样呢？因为SQL Server在内存中cache了很多数据页面，这些数据页面即使这次不用了，下次仍然有可能被使用。因此即使某次查询已经使用过了这些数据页面，SQL Server仍然会在内存中保留它们，除非有内存压力，才会吧这些过期的数据页面踢出内存。因此如果看起来SQL Server占用了10GB的内存，那么这10GB内存就会被填的满满的，即使里面可能是一些并不需要的数据。


因此如果我们要考察SQL Server是否有内存压力，需要深入SQL Server。
首先来看SQL Server实际的内存占有量'Total Server Memory (KB)'：


SELECT object_name, counter_name, cntr_value AS 'Total Server Memory (KB)'
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Total Server Memory (KB)'
or counter_name = 'Target Server Memory (KB)'


Total Server Memory is what SQL currently has. Target is what it thinks it wants under the current load. So if total is lower than target, either SQL is still building up the memory allocation, or it needs more than it has (ie it's under memory pressure).因此'Target Server Memory (KB)'可能会很大，甚至超出物理内存。
但是并不准确。我就遇到过这种情况，但是内存并没有什么问题。


顺便提一句在Idera中，“SQL Used”就是'Total Server Memory (KB)'， 而“SQL allocated”就是'Target Server Memory (KB)'.Idera的名字真是取的非常容易混淆啊。


下面这个链接有更多的计数器的解释：
http://msdn.microsoft.com/en-us/library/ms190924.aspx


而我们判断是否有内存压力，主要通过另外几个计数器：


Look at Page Life Expectancy, which should be well above the 300 number that most of the stuff online says.  This tells you how long pages are staying in the buffer pool, and a value of 300 equates to 5 minutes.  If you have 120GB of buffer pool and it is churning ever 5 minutes, that equates to 409.6 MB/sec sustained disk I/O for the system which is a lot of disk activity to have to sustain. 


Look at Lazy Writes/sec which tells you that number of times the buffer pool flushed dirty pages to disk outside of the CHECKPOINT process.  This should be near zero.


Look at Free Pages/sec and Free List Stalls/sec.  You don't want to see Free Pages bottom out which will result in a Free List Stall while the buffer pool has to free pages for usage.


Look at Memory Grants Pending which will tell you if you have processes waiting on workspace memory to execute.


同时还有2个OS层面的计数器：
Memory\Pages/sec
Process ( All_processes )\Working Set


我们要使用OS层面的计数器，原因是当SQL Server占用了过多的OS内存时，系统其他运行的程序就会有内存压力。此时就会发生page faulting。因此是否存在page faulting也是是否有内存压力的重要指标。系统层面的内存严重不足也会影响SQL Server的运行，因为SQL Server的很多操作如IO等，也是依赖操作系统来完成的。
Memory \ Pages/sec is the sum of Pages Input/sec and Pages Output/sec and reports the number of requested pages that were not immediately available in RAM and had to be read from the disk (resulting in hard page faults) or that had to be written to the disk to make room in RAM for other pages. Monitoring the working sets of processes enables you to correlate a particular applications memory usage with page faulting. 


具体原理见：
http://technet.microsoft.com/en-us/library/cc958290.aspx


然后是推荐的计数器阀值：

http://www.enduria.eu/key-performance-counters-and-their-thresholds-for-windows-server-recommended-by-microsoft/

最后，当memory被大量占用时，可以用一下sql查询是什么进程导致的：

SELECT
session_id,convert(varchar(max),t2.[text]) as SQlquery,t1.requested_memory_kb,
t1.granted_memory_kb,t1.required_memory_kb,t1.used_memory_kb,t1.max_used_memory_kb,t1.query_cost,t1.ideal_memory_kb

FROM sys.dm_exec_query_memory_grants t1 CROSS APPLY sys.dm_exec_sql_text(t1.sql_handle) t2
order by query_cost desc


select * from sys.dm_exec_sessions where session_id =100

SELECT DEST.TEXT  
FROM sys.[dm_exec_connections] SDEC 
 CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST 
WHERE SDEC.[most_recent_session_id] = 52