Page Life Expectancy
Page Life Expectancy is often quoted as being a key metric to watch out for memory pressure within SQL Server. If you search you will often see 300 listed as a low water mark to indicate that you may have a problem, indeed several vendor applications raise warnings at this threshold.
The standard definition of this metric is the amount of seconds that a page will stay in the buffer pool. So the 300 second value would give us 5 minutes. If we have a 5GB buffer pool that would mean that on average 1GB per minute of buffer change was occurring. 20GB buffer pool then 4GB per minute etc… It is obvious that as the amount of memory has increased over the years this value of 300s/5min value is much to low for modern systems. However what is a good value for page life expectancy? The answer is a high value that does not fluctuate too much, but it will be intrinsically tied to your system specifications and work load that you are running. A value of several hours maybe good on one system, but be a sign of a problem on another.
In order to address this I have written a script to not only show the page life expectancy, but also the amount of data per second that needs to be moved in/out of buffer pool for this page life.
One more aspect to the script is that because numa nodes are present in most modern systems it is will show how this is distributed across them. e.g a 2 numa node system would split its buffer pool 50/50 across them.
Growing and Shrinking the Buffer Pool Under NUMA
My desktop SQL Express instance shows that I have a page life of nearly 2 hours (guess how long my pc has been on), with a tiny buffer pool of 23 MiB, which would mean SQL Server would have to do 0.003 MiB per second. Is 2 hours good/bad?
An empty value for node would indicate the totals/averages.
--http://jongurgul.com/blog/page-life-expectancy/ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT ple.[Node] ,LTRIM(STR([PageLife_S]/3600))+':'+REPLACE(STR([PageLife_S]%3600/60,2),SPACE(1),'0')+':'+REPLACE(STR([PageLife_S]%60,2),SPACE(1),'0') [PageLife] ,ple.[PageLife_S] ,dp.[DatabasePages] [BufferPool_Pages] ,CONVERT(DECIMAL(15,3),dp.[DatabasePages]*0.0078125) [BufferPool_MiB] ,CONVERT(DECIMAL(15,3),dp.[DatabasePages]*0.0078125/[PageLife_S]) [BufferPool_MiB_S] FROM ( SELECT [instance_name] [node],[cntr_value] [PageLife_S] FROM sys.dm_os_performance_counters WHERE [counter_name] = 'Page life expectancy' ) ple INNER JOIN ( SELECT [instance_name] [node],[cntr_value] [DatabasePages] FROM sys.dm_os_performance_counters WHERE [counter_name] = 'Database pages' ) dp ON ple.[node] = dp.[node]