September 7, 2017 at 2:28 pm
Below is the standard query to find the PLE value. But if this is higher or lower, how to calculate?
Per the below blog post by Paul Randal, the PLE value to be calculated by the below formula.
In that way, how to modify the query to find the present PLE value and if that is HIGH or low?
Refer: https://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy
( Buffer pool memory in GB / 4 ) x 300
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'
GO
Thanks.
September 7, 2017 at 2:53 pm
There's no magic value for PLE that's good.
In general, you want it as high as possible. Consider that it's a measure of how much churn there is in the buffer pool (pages discarded and new pages fetched). High churn leads to IO subsystem contention and resulting slow queries. Hence you want the buffer pool to be stable, and PLE is a measure of how stable it is.
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
September 8, 2017 at 5:55 am
Everything that Gail says, plus, you have to track it over time to see how your server behaves. For example, we had a nightly load process that would dump the PLE down near zero (PANIC!!!, not really) and then the PLE would go up all day long. In short, we didn't have any kind of problem with memory. Everything was working fine. However, if we looked at the number just one morning, we might be thinking there was a problem when there really wasn't one.
Please, don't get hung on any single measure of performance telling you what's going on with your system. They all have to be used in combination with other measures to have a full understanding.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply