What it is Page Life Expectancy (PLE)

Which cup will you prefer to go the less
number of times to the coffee maker?
image was taken from here
Let us imagine this: Every morning when you get to the office, you must drink coffee to be fully awake, if you are a coffee lover, maybe you should have your own personalized 24 oz cup to put it.
Let's say one day you lose your cup and you have to grab one of the tiny-ugly cups available in the office, now you have to put the coffee on it several times to drink the same 24 oz, this means you will have to leave your desk several times.

Something similar happens with Page Life Expectancy (PLE). This is a measure that indicates us how long a data page stays in memory(in seconds) so it can be reused by other SQL Processes without having to read the same page from disk again (which translates in less I/O and less overhead). So with this in mind, the higher the value the better, but, how much is high enough for our system?


Right PLE value for my SQL Server


Long time ago, when servers were not so powerful, the recommendation was to have our PLE higher than 300, but now times have changed and servers too, so the recommendation is to have a PLE higher than 300 for each 4 GB of RAM assigned to SQL Server if you have Max server memory set (or for your server memory if you don't have a maximum set to SQL Server).

Keep in mind that you have to measure this value over a period of time and then average it to have a more consistent value or to identify sudden drops and correlate them with queries executed at that time.

To Calculate it


I take it from performance counters, more specifically from the Buffer Manager counter.
To calculate it I use the following T-SQL (please note that this only gives you the current value):


DECLARE @SQLmemory NUMERIC(9, 2)
DECLARE @Maxmemory INT

SET @SQLmemory = (
  SELECT physical_memory_kb / 1024.0 / 1024.0
  FROM sys.dm_os_sys_info
  )
SET @Maxmemory = (
  SELECT convert(INT, value_in_use)
  FROM sys.configurations
  WHERE [name] = 'max server memory (MB)'
  )

IF (@Maxmemory) <> 2147483647
BEGIN
 SET @SQLmemory = @Maxmemory / 1024.0
END

SELECT [counter_name]
 ,@SQLmemory AS [Server Memory_GB]
 ,cast([cntr_value] AS INT) AS [PLE_Current]
 ,(@SQLmemory / 4) * 300 AS [PLE_Expected]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
 AND [counter_name] = 'Page life expectancy'

What if the value is too low?


This means SQL Server has to flush memory very often. this could be a symptom that your SQL Server is struggling with the currently allocated memory, so you need to tune your Database checking (but not limited to):
  • Check your indexes (missing or unused)
  • Select statements that return data you don't need
  • Tune your queries
  • Check your statistics
  • Check for ad-hoc queries
If all of the above does not work then you should consider expanding the memory of the server.


There are 2 posts I want to recommend to for further reading about this topic:

NUMA considerations for PLE from Paul Randal
Fixing PLE from Steve Hood

Comments

Post a Comment