The SQL Server Buffer Manager Object offers some useful performance objects. My favourite is the Page Life Expectancy object , which may indicate a  memory bottleneck.

BOL defines the  Page Life Expectancy object  as  “the number of seconds a page will stay in the buffer pool without references”.

This means , a buffer with 300 second page life expectancy will  keep any page  in the  memory buffer pool for  300 seconds before the page is flushed to disk- unless the page is referenced.

Microsoft  recommends a page life expectancy of greater than 300 seconds. This means if the page life expectancy counter is consistently greater than 300 seconds – then adding more physical memory will not improve performance (or not as much as you think)

SQL Server memory allocations are increasing - as costs of memory decrease. In estimating the PLE of a SQL server , accepting the Microsoft recommendation should be questioned. Conisder other methods of calculating the PLE such as an adaptive algorithm e.g DataCacheSize(GB)/4GB*300.

I measure page life expectancy over 10 minutes during a typical workload, allowing me to  check different scenarios. This method evens out the result , high and low.

If you take a snapshot – and the page life expectancy dropped to below 300 for a few seconds , this may not reflect the normal situation. The key is consistency.

A memory problem  does not  mean more physical memory is required. For example , a page life expectancy of 2000 seconds – indicates to me more memory won’t help, look to  sql server memory configuration management.

 On the other hand if  page life expectancy is repeatedly low  – then that is a good indicator more physical memory is required. Also, check for missing indexes,  updated statistics or index missing statistics

--alter the 'MSSQLServer:Buffer Manager' if using a name instance 

--example MSSQL$INST1:Buffer Manager'

 SELECT cntr_value AS [page_life_expectancy]

FROM sys.dm_os_performance_counters

WHERE OBJECT_NAME = 'MSSQLServer:Buffer Manager'

AND counter_name = 'Page life expectancy';

 Analyse with a real workload – to get an accurate view on page life expectancy , and based on those numbers – analyse the reasons why there might be memory pressure or a memory bottleneck