set nocount on;

drop table #memoryifno;

select 1 seq, counter_name,cntr_value

into #memoryifno

from sys.dm_os_performance_counters 

WHERE counter_name = 'Lazy writes/sec' or counter_name='Free list stalls/sec' or counter_name = 'Memory Grants Pending' or counter_name='Target Server Memory (KB)' or counter_name='Total Server Memory (KB)'

  or (counter_name ='Page life expectancy' and object_name='SQLServer:Buffer Manager')

  or counter_name ='Page reads/sec' or  counter_name ='Page writes/sec'

  or counter_name='Lock Memory (KB)' or counter_name='Free Memory (KB)'

declare @icount as int

set @icount=1

while @icount<=60

begin

waitfor delay '00:00:10';

set @icount=@icount+1

insert into #memoryifno

select @icount,counter_name,cntr_value

from sys.dm_os_performance_counters 

WHERE counter_name = 'Lazy writes/sec' or counter_name='Free list stalls/sec' or counter_name = 'Memory Grants Pending' or counter_name='Target Server Memory (KB)' or counter_name='Total Server Memory (KB)' 

or (counter_name ='Page life expectancy' and object_name='SQLServer:Buffer Manager') or counter_name ='Page reads/sec' or  counter_name ='Page writes/sec'

end;

with t as(   

   SELECT seq,[Lazy writes/sec] ,[Free list stalls/sec] ,[Memory Grants Pending],[Target Server Memory (KB)],[Total Server Memory (KB)],[Page life expectancy],[Page reads/sec],[Page writes/sec]

  FROM

  (select seq, counter_name,cntr_value from #memoryifno WHERE counter_name = 'Lazy writes/sec' or counter_name='Free list stalls/sec' or counter_name = 'Memory Grants Pending' 

  or counter_name='Target Server Memory (KB)' or counter_name='Total Server Memory (KB)' or counter_name ='Page reads/sec' or  counter_name ='Page writes/sec'

  or (counter_name ='Page life expectancy' )) p

   pivot(min(cntr_value) for counter_name in([Lazy writes/sec],[Free list stalls/sec],[Memory Grants Pending],[Target Server Memory (KB)],[Total Server Memory (KB)],[Page life expectancy],[Page reads/sec],[Page writes/sec])) as t

   ),m as(

   select t1.*,t2.[Lazy writes/sec]-t1.[Lazy writes/sec] lazywritessub,t2.[Free list stalls/sec]-t1.[Free list stalls/sec] freeliststallssub,t1.[Total Server Memory (KB)]/4/1024/1024*300 plestd,

   t2.[Page reads/sec]-t1.[Page reads/sec] pagereadssub,t2.[Page writes/sec]-t1.[Page writes/sec] pagewritesub from 

   t t1 join  t t2 on t2.seq=t1.seq+1)

   select m.*,case when (freeliststallssub>2 or [Memory Grants Pending]>2 or [Target Server Memory (KB)]>[Total Server Memory (KB)]+[Total Server Memory (KB)]*0.05 or [lazywritessub]>20 or [pagereadssub]>90 or pagewritesub>90 or [Page life expectancy]<plestd)

   then '内存可能不足' else '内存使用正常' end info

   from m

--   <[Total Server Memory (KB)]/4/1024/1024*300 计算页的生命周期  以 300ms/4GB的标准计算，低于该值，可能内存不够

--Free list stalls/sec（每秒必须等待空闲页面的请求数） >2可能内存不足

--Memory Grants Pending(内存授权挂起) >0可能内存不足

--Target Server Memory>Total Server Memory （想要的内存大于总的内存*(1.05） 也是内存不足的表现

--Lazy writes/sec(惰性写入)>20 可能内存不足

--Page writes/sec（每秒物理数据库页面写入数）>90 可能内存不足

--Page reads/sec（每秒物理数据库页面读取数）>90 可能内存不足