SQL 2008 Page Life Expectancy very high
When I run the following query to determine the Page Life Expectancy:
SELECT cntr_value
AS
'Page Life Expectancy'
FROM
sys.dm_os_performance_counters
WHERE
object_name
=
'SQLServer:Buffer Manager'
AND counter_name
=
'Page life expectancy'
It returns a value greater than 120,000, Shouldn't this value be in seconds? So that would work out to 33 Hours, that does not seem right? The server is a Virtual
Windows 2008 R2 Enterprise running SQL 2008 Standard. It has 24 GB in the box and 22.5 allocated to SQL. The Buffer Cache is roughly 99% and the Target Server Memory (KB) is 21764. So SQL is not using all of the RAM Allocated to it
October 10th, 2011 4:51pm
This counter can be helpful in determining whether you have a memory problem, giving you a reasonably accurate view of whether your server has memory pressure. According to Microsoft, 300 seconds is the minimum target for page life expectancy. If the buffer
pool flushes your pages in less than 300 seconds, you probably have a memory problem. Looking at this value is particularly handy when your page life expectancy is significantly higher or lower than 300 seconds
http://social.msdn.microsoft.com/Forums/en-AU/sqldatabaseengine/thread/583bdbbc-9437-46e1-8a4e-0badae03dbcc
October 10th, 2011 6:08pm
Let me add more to my question... How would be the best way to determine the amount of RAM to give SQL? I have been doing alot of reading and for the industry that I work in, I agree with the logic that a PLE of 300 or 5 minutes is just not
enough given the amounts of RAM in a common server. So given that the PLE is really high [> 120000] and the Buffer Hit Cache Ratio is above 98% those are good numbers but is that only because 93% of the RAM in the Box was given to SQL?
How do I find the happy medium? When I look at the Target Server Memory (KB) it is 21 GB. So SQL is saying that it wants to use 21 GB, but how can I find out if I have not set the RAM too high for SQL?
October 11th, 2011 12:32pm
When I run the following query to determine the Page Life Expectancy:
SELECT cntr_value
AS
'Page Life Expectancy'
FROM
sys.dm_os_performance_counters
WHERE
object_name
=
'SQLServer:Buffer Manager'
AND counter_name
=
'Page life expectancy'
It returns a value greater than 120,000, Shouldn't this value be in seconds? So that would work out to 33 Hours, that does not seem right? The server is a Virtual
Windows 2008 R2 Enterprise running SQL 2008 Standard. It has 24 GB in the box and 22.5 allocated to SQL. The Buffer Cache is roughly 99% and the Target Server Memory (KB) is 21764. So SQL is not using all of the RAM Allocated to it
I don't have SQL at my wife's laptop to check but can you check if the value is culmulative in DMV? Does the same value shown in perfmon also?
October 11th, 2011 12:43pm
October 11th, 2011 2:28pm
I do not believe it is cumulative
October 11th, 2011 5:55pm
Have you found the reason for the very high PLE? I encountered with 2 million + PLE on my prod server after the reboot. I''m curious to know what is the reason for it.
Thanks!
July 31st, 2014 10:06pm
I know I'm super late to the party but we have a customer that experiences random readings (through the Data Collector) of over 4 million, whereas normally it ranges from 50 to 2000 depending on load etc.
This must be a bug? Anyone seen this before? SQL2008 R2, Service pack 1 is the version.
March 3rd, 2015 1:09am
Did you find an answer to this I am seeing the the same spikes in PLE of Over 4 million.
June 1st, 2015 3:52pm