SQL DB 2008 Engine Page Life Expectancy is too low

Summary:

 Page Life Expectancy is number of seconds a page will stay in the buffer pool without references. High page life expectancy means that required data can be found in cache instead of going to hard drive.

                     

 Causes:

 Extremely low Page Life Expectancy values can be an indication of:

                  

 Resolutions:

 Check I/O system to verify how it handles the load, if values of Avg. Disk sec/Read and Avg. Disk sec/Write counters for object PhysicalDisk   are high             (usually higher than 10 msec.), it means your system may overloaded or opportunities exist for query and index improvement.

Buffer Pool can be overloaded because SQL Server works with high amount of tables simultaneously, or full scanning is used in place of search by criteria.

Check SQL Server:Access Methods:Full Scans/sec counter to verify the problem.

 You may use SQL Server Profiler and browse Showplan Statistics in the Performance category to find an application that cause full scanning.

  QUERIES             

 

select spid, memusage, * from sys.sysprocesses

SELECT cntr_value AS [Page Life Expectancy] 

FROM sys.dm_os_performance_counters 

WHERE [object_name] = N'SQLServer:Buffer Manager' -- Modify this if you have named instances 

AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

---Query to see memory per connection  

select convert(decimal(8,2), granted_memory_kb / 1024. /1024.) as memory_requested_gb ,

      (

   select top 1 sp.loginame from master..sysprocesses sp (nolock) where qmg.session_id = sp.spid ) as loginame ,*

from sys.dm_exec_query_memory_grants qmg (nolock)

order by convert(decimal(8,2), granted_memory_kb / 1024. /1024.)  desc