Can SQL Server's Page Life Expectancy be too high?

Question

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.

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

Answer 1

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.

Answer 2

Sure. In a perfect world where I can spend as much money as I want, then yes. PLE can never be too high.

The world is not perfect.

One man's "It can never be high enough" is another man's "why am I paying so much for RAM on all these servers???" I'd say, in my limited experience with only a couple thousand SQL instances in the cloud that yes, PLE can be too high on one of our VMs if and when it means the RAM is stagnant and the VM is running other instances that are not, and are starved.

Also, at some point if PLE is "off the charts" high, then you may want to consider that you over-provisioned the RAM on the server or that the instance isn't being used anymore. An exception might be if the same static data (and a lot of it) is always present in RAM and always being queried--like in a scenario where you have a single database and it fits perfectly into RAM.

PLE being too high, then, is especially something you should keep an eye on in any cloud service such as Azure if you are using SQL installed on IaaS machines. I am not sure what metric you would look at to see how often the memory is being queried. I am currently in the middle of trying to figure that one out myself right now and is how I ended up here in the first place but that's my problem :)