Sql-server – Can SQL Server’s Page Life Expectancy be too high

memorypage-life-expectancysql server

I've read several articles about SQL Server's Page Life Expectancy and what it means and what kind of information you can glean from it. In most of these articles that I've read, a common, healthy PLE value is somewhere around 1000-2000 seconds. I've read that getting down to around 300 seconds can mean you're likely low on RAM.

I recently just upgraded our hardware to have 64GB of RAM, up from 14GB. On the 14GB my PLE was around 300 seconds, and I was having a lot of 5-6 memory grants pending per second. So, that was bad and I increased the RAM. Now, my PLE is much, much higher around 5000 seconds, and no more pending memory grants. I've seen it 7000 seconds, if I recall. This is much higher than anything I've read.

Can a high PLE be a bad thing? Or is it the higher the better?

EDIT: I'm sorry, my PLE wasn't 7000 seconds, it was 70,000 seconds! Although, at the moment it is down to around 7000.

enter image description here

  • The database size is around 160GB. A few tables have 5+ million rows.
  • max_server_memory is set to 2147483647.

Best Answer

No, I can't think of any situation or theory in my head that could illustrate a negative side effect for an astronomically high PLE (unless you enjoy the humming sound of the platters in a hard disk drive?).

With memory getting cheaper and cheaper, and buyers-of-hardware getting a little more generous, these bigger memory boxes we see it is quite common to have PLE in the thousands upon thousands range. It's simply one indication that your instance may not be under memory pressure. Take it for what it is, and only that.

Page Life Expectancy is just an estimation SQL Server makes on how long it thinks a page will last in a buffer. This is SQL Server's way of saying it thinks it'll be "a while".

Another thing of note, take Paul Randal's advice and don't concentrate on the PLE reported by the buffer manager. Like all averages, the buffer manager can hide differing PLEs. You want to look at all of the individual NUMA nodes' PLEs with the Buffer Node counter:

Get-Counter -ListSet "*" | 
    Select-Object -ExpandProperty Counter | 
    Where-Object {$_ -like "*buffer node*page life expectancy*"} | 
    Get-Counter

Page Life Expectancy is simply one color in the beautiful painting that is SQL Server memory. Look at it along with the other "colors" (target/total server memory, available mbytes, etc.). Too often people look at one simple metric and fear either their server is about to explode, or conversely that it'll live for centuries unmanaged.

Related Question