January 22, 2018 at 2:39 am
Whilst monitoring various counters on our production instance of SQL Server 2014 (Web Edition), we have noticed that the Page Life Expectancy climbs steadily throughout the day but plummets to rock bottom at the same time every morning in the early hours, and again a couple of hours later.
This correlates with two scheduled SQL Server agent jobs, one of which moves data from one database to another, and another which backs up the user databases.
Is there a reason for page life expectancy to be affected by database processes such as these?
January 22, 2018 at 5:04 am
You've pretty much described standard behavior for PLE. It's one of the reasons why it's not a great measure for performance. I'd focus on other counters, especially wait statistics and queues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 22, 2018 at 7:48 am
Hi Grant, thanks very much for the response. Will do!
January 22, 2018 at 8:46 am
When you have a process that reads a lot of data, like your ETL, this will flush pages out of memory to hold the reads. As ETL ends, and "normal" queries take over, they use memory.
The flushing means that pages live in memory for less time and SQL calculates this as a lower PLE. This is an indicator of change in workload, not necessarily a performance issue, as Grant noted.
January 22, 2018 at 11:15 am
Index maintenance cause pages to flush out, so if you have index maintenance jobs scheduled it could be the one of reasons other then described above.
January 29, 2018 at 2:08 am
zoggling - Monday, January 22, 2018 7:48 AMHi Grant, thanks very much for the response. Will do!
Please check Wait stats and enquire about all your SQL queries against databases. This is good place to start first.
January 29, 2018 at 2:17 am
zoggling - Monday, January 22, 2018 2:39 AMIs there a reason for page life expectancy to be affected by database processes such as these?
Yes. They're processing large amounts of data and hence displacing the buffer pool, meaning the 'regular' queries don't have their pages in cache.
Unlike Grant, I do like PLE as a measure (but never used alone), providing it's used to understand the system's patterns and norms.
Sharp drops due to bulk queries is normal, as is sharp drops during index rebuilds and other maintenance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply