SQL Server Page Life Expectancy

Page Life Expectancy (PLE) can be used to measure memory pressure on a SQL Server, though it should be used with some caution.

Per Microsoft’s documentation, PLE "indicates the number of seconds a page will stay in the buffer pool without references."  In other words, it measures the average amount of time that a page will remain in the memory pool before being flushed to disk.  PLE is measured in seconds.  The lower the value, the more memory pressure on the server.

The PLE of a SQL Server instance can be checked using the following command:

SELECT *
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Manager%’
AND [counter_name] = ‘Page life expectancy’

When examining the output of the query, note the “cntr_value” column; it contains the PLE value.

Now, some reasons to be cautious when examining the PLE:

1) Some years ago Microsoft provided some often-quoted (even today) guidance on the recommended threshold for the PLE.  That threshold was based on the hardware common when the guidance was given, and in the vast majority of cases is no longer valid today.  This blog post explains the problem very well, and provides a better way to establish what the PLE threshold should be for your server.  The recommendation given there is to use a threshold of (DataCacheSizeInGB / 4GB *300). 

As is often the case when tuning and monitoring, the best method is to monitor your server to establish the baseline PLE value during periods of normal usage (and good performance), and to pay attention when the value drops and remains below that baseline.

2) Many modern servers use Non-Uniform Memory Acess (NUMA), which means that the buffer pool may be split into multiple nodes.  On such systems, PLE may not show the entire picture of what is happening on the server.  PLE is an average across all NUMA nodes, so if only one node is under memory pressure, it may not be clearly reflected in the average value given by the PLE.  More information can be found here.

3) Memory pressure may be due to inefficient query plans, so don’t immediately assume that the server lacks enough memory to handle the workload.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: