SQL Server Page Life Expectancy (PLE)

Published Jan 15 2019 04:35 PM 1,638 Views
Microsoft
First published on MSDN on May 14, 2015
This week I was involved in a conversation with Paul Randal relating to PLE per node vs PLE server wide.

There is an all-up PLE counter as well as individual, per NUMA node PLE counters.  SQL Server Books Online describes the values as:

SQL Server Buffer Manager \ Page life expectancy – Indicates the number of seconds a page will stay in buffer pool without references.

SQL Server Buffer Node \ Page life expectancy – Indicates the minimum number of seconds a page will stay in buffer pool on this node without references.



The descriptions leave a bit to the imagination.   It is pretty common place to ask someone about the all-up value and the assumption is a simple average of the individual node values.  For example, using the following 4 Node values, the AVG = 1750 divided by 1000 = 175.



1000
2000
1500
2500


This is not the calculation used for all-up number.  The Buffer Manager value is an average of the rates or the ( Harmonic Mean .)  Using the harmonic mean, run rates average, for this example the PLE = 155 .

Paul outlines additional calculations and highlights the need to watch per node values for better management of your PLE targets in his post: http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think

Bob Dorr - Principal SQL Server Escalation Engineer

%3CLINGO-SUB%20id%3D%22lingo-sub-318572%22%20slang%3D%22en-US%22%3ESQL%20Server%20Page%20Life%20Expectancy%20(PLE)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318572%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20May%2014%2C%202015%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20This%20week%20I%20was%20involved%20in%20a%20conversation%20with%20Paul%20Randal%20relating%20to%20PLE%20per%20node%20vs%20PLE%20server%20wide.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20There%20is%20an%20all-up%20PLE%20counter%20as%20well%20as%20individual%2C%20per%20NUMA%20node%20PLE%20counters.%26nbsp%3B%20SQL%20Server%20Books%20Online%20describes%20the%20values%20as%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CSTRONG%3ESQL%20Server%20Buffer%20Manager%20%5C%20Page%20life%20expectancy%20%3C%2FSTRONG%3E%20%E2%80%93%20Indicates%20the%20number%20of%20seconds%20a%20page%20will%20stay%20in%20buffer%20pool%20without%20references.%3CP%3E%3C%2FP%3E%0A%20%20%20%3CP%3E%3CSTRONG%3ESQL%20Server%20Buffer%20Node%20%5C%20Page%20life%20expectancy%20%3C%2FSTRONG%3E%20%E2%80%93%20Indicates%20the%20minimum%20number%20of%20seconds%20a%20page%20will%20stay%20in%20buffer%20pool%20on%20this%20node%20without%20references.%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EThe%20descriptions%20leave%20a%20bit%20to%20the%20imagination.%26nbsp%3B%26nbsp%3B%20It%20is%20pretty%20common%20place%20to%20ask%20someone%20about%20the%20all-up%20value%20and%20the%20assumption%20is%20a%20simple%20average%20of%20the%20individual%20node%20values.%26nbsp%3B%20For%20example%2C%20using%20the%20following%204%20Node%20values%2C%20the%20AVG%20%3D%20%3CSTRONG%3E%201750%20divided%20by%201000%20%3D%20175.%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E1000%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3E2000%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3E1500%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3E2500%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20This%20is%20not%20the%20calculation%20used%20for%20all-up%20number.%26nbsp%3B%20The%20Buffer%20Manager%20value%20is%20an%20average%20of%20the%20rates%20or%20the%20(%20%3CA%20href%3D%22http%3A%2F%2Fen.wikipedia.org%2Fwiki%2FHarmonic_mean%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Harmonic%20Mean%20%3C%2FA%3E%20.)%26nbsp%3B%20Using%20the%20harmonic%20mean%2C%20run%20rates%20average%2C%20for%20this%20example%20the%20PLE%20%3D%20%3CSTRONG%3E%20155%20%3C%2FSTRONG%3E%20.%20%3CBR%20%2F%3E%3CBR%20%2F%3EPaul%20outlines%20additional%20calculations%20and%20highlights%20the%20need%20to%20watch%20per%20node%20values%20for%20better%20management%20of%20your%20PLE%20targets%20in%20his%20post%3A%20%3CA%20href%3D%22http%3A%2F%2Fwww.sqlskills.com%2Fblogs%2Fpaul%2Fpage-life-expectancy-isnt-what-you-think%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fwww.sqlskills.com%2Fblogs%2Fpaul%2Fpage-life-expectancy-isnt-what-you-think%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EBob%20Dorr%20-%20Principal%20SQL%20Server%20Escalation%20Engineer%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318572%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20May%2014%2C%202015%20This%20week%20I%20was%20involved%20in%20a%20conversation%20with%20Paul%20Randal%20relating%20to%20PLE%20per%20node%20vs%20PLE%20server%20wide.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318572%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ememory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20OS%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 04:35 PM
Updated by: