判断SQL SERVER内存正常或者不足脚本

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_v