Top 5 Reasons to Check SQL Server’s Page Life Expentency

One of the very misunderstood values that you can monitor in Microsoft SQL Server is Page Life Expectancy (PLE). I’ve read online that many people think that the value of Page Life Expectancy is worthless to monitor. And that really isn’t true. While Page Life Expectancy won’t tell you a specific problem by itself, it can be used to point you in the correct direction so that you know where to look.

Construction workers monitor a building build process.

The first thing to do in order to make use of the Page Life Expectancy value is to measure a baseline and keep that baseline where you can refer to it. So what can we learn from the Page Life Expectancy?

  1. If PLE is randomly dropping and then recovering this means that someone is running a one-off query that is either hitting a bunch of rows (pages) that aren’t in the buffer pool. If this is happening every once and a while it probably doesn’t mean anything.
  2. If PLE has dropped and is staying down, then either the load of the server has increased, or indexes have probably been dropped that are needed by the system.
  3. If PLE is climbing at the rate of 1 per second and PLE is low, then the server was probably just restarted.
  4. If PLE is climbing at the rate of 1 per second and PLE is high, and the PLE stays high, then the server probably has more RAM than is usable by the SQL Server.
  5. If PLE is stable and it is near the value of the baseline, then there is probably no memory pressure on the server.

One thing to remember is that when a server has multiple NUMA nodes things get a little more difficult to measure as SQL Server can use the nodes differently at each SQL Server restart. So if the SQL Server has multiple NUMA nodes care is going to be needed when monitoring and reviewing the PLE numbers.

Denny

Share

One Response

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?