Significance of Page Life Expectancy (PLE) to analyze memory bottlenecks

Page Life Expectancy can tell whether SQL Server has enough memory for its bufferpool to function properly. Extremely low Page Life Expectancy values can be an indication of:
  • Too little memory on the system
  • Too little memory configured for SQL Server’s use
  • Poorly written application code
According to Microsoft, 300 seconds is the minimum target for page life expectancy. If the buffer pool flushes the pages from memory in less than 300 seconds, that indicates a memory bottleneck. If PLE goes low only during certain times of the day, a badly written query could be behind the problem.

On NUMA machines, you need to be looking at the Buffer Node:Page Life Expectancy counters for all NUMA nodes otherwise you’re not getting an accurate view of buffer pool memory pressure and so could be missing performance issues.

For instance, for a machine with 8 NUMA nodes, with PLE of each being 4000, the overall PLE is 4000. If one of them drops to 1000, the overall PLE only drops to 3625, which likely wouldn’t trigger your alerting as it hasn’t even dropped 10%.

http://www.sqlskills.com/blogs/jonathan/post/Finding-what-queries-in-the-plan-cache-use-a-specific-index.aspx

Today the value 300 is ridiculously small as a threshold for when to start worrying about buffer pool pressure. Don’t use the value 300 as any kind of Page Life Expectancy threshold. Anyone that continues to recommend doing so is doing you a disservice. Use an adaptive formula like (DataCacheSizeInGB/4GB*300). Even better – monitor the steady-state value of PLE and react when it dips *and stays* below your steady-state value.
















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