0

Looking at performance data I realized sudden drops of Page Life Expectancy on a Sql Server 2016 SP1 running on VMWare consuming 58982 MB of 64 GB RAM. The previous value of PLE was around 133,000 and suddenly it dropped to 7,300 sec.

enter image description here

It looks like there was only one single query a candidate to cause this. I uploadad the execution plan.

That query ran very early in the morning so it looks like there was almost no additional activity on the system. It required 01:27 m:s runtime and caused 600,000 Reads.

Why did this query cause this drop of PLE?

What are the consequences of that drop?

Improve this question
2
  • If the query is going to do a lot of reads there can be pages that are flushed. Is your index optimization on the same time?
    – Stijn Wynants
    May 2, 2017 at 9:47
  • The query also includes an update. Rows that have updated would need to be written to disk and reloaded by other queries.
    – Sir Swears-a-lot
    May 2, 2017 at 9:51

2 Answers 2

Reset to default
6

PLE is a bit of a flaky metric. Sure, it's better when it's high and steady, but if you have more data than RAM, that ain't always gonna happen.

Things that can make PLE drop:

  • Queries that get large memory grants
  • Queries that displace large amounts of pages in memory with new ones
  • Queries that modify many pages and force them to get flushed to disk
  • DBCC CHECKDB
  • Index Rebuilds

PLE is more of a concern if it's constantly low. As with most things, you need to look at your overall wait stats to see if it's an issue.

Since you're looking at My Favorite Monitoring Tool© -- what is it telling you about overall wait stats? If you don't know where to find those, contact support.

The RAM-ifications (wait for it) are that you'll likely need to read those pages back into RAM (there it is) at some point.

Hope this helps!

Improve this answer
1
  • If you are talking about the Wait Stats Analysis report, this indicates in Wais by category that Memory makes 0.0% of all Waits, 20% DISK and 80% other for this instance.
    – Magier
    May 2, 2017 at 14:48
0

The Page / Buffer life expectancy is simply how long the data is held in memory for See: https://blogs.msdn.microsoft.com/mcsukbi/2013/04/11/sql-server-page-life-expectancy/ for a more detailed view

Basically its a gauge on how volatile your data is in RAM, the lower the time, the more re-writing of data there is going on, quite often if you perform a large query that has had no data in active memory (like a lot of nightly reporting jobs often do) you'll see a large drop off on the life expectancy of the database, (I personally see this at 2.32 every morning when a certain report kicks in)

If your low time is over two hours then you're doing alight still and wouldn't be worried, if it comes down to less than 30 minutes and stays there that's the point I'd start considering looking at what's taking the memory.

Ultimately unless you're seeing a performance hit from disk reads (writing to memory wont be taking time unless you've saturated your memory

Improve this answer
2
  • The link in your answer appears to be dead now.
    – Erik Darling
    Apr 16, 2018 at 20:16
  • Still working for me
    – Ste Bov
    Apr 17, 2018 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.