October 3, 2013 at 9:50 am
I have an issue whereby every now and again throughout the day the PLE value plummits from something like 14hrs to 0.1hr. It is at this point I get complaints of the app running slowly. CPU looks fine. Also the buffer cache hit ratio is within normal operational levels.
Is this indicative of some poorly written code somewhere ?
What can I do to further investigate this to help increase performance ?
October 3, 2013 at 12:04 pm
Something is flushing the data out of cache. You'll want to look for long running queries or queries that are moving very large amounts of data (even if they're not returning to the end users, execution plans that scan 10 million records only to return 5, that kind of thing). I'd start by looking for queries with high IO or high IO wait times.
"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
October 4, 2013 at 2:37 am
Could also be in an agent job...
We see this behaviour when our Litespeed backups kick off during our maintenance window
October 4, 2013 at 3:23 am
Hi
PLE min values should not go under 300 seconds. Fluctuating from 14h to 0.1h is still above 300 seconds, but the behavior itslef is not good for the database work.
It could be some other processes on the server that are eating the memory, as well as the other proposals given in previous posts of this thread.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
October 4, 2013 at 3:27 am
IgorMi (10/4/2013)
HiPLE min values should not go under 300 seconds. Fluctuating from 14h to 0.1h is still above 300 seconds, but the behavior itslef is not good for the database work.
It could be some other processes on the server that are eating the memory, as well as the other proposals given in previous posts of this thread.
Regards,
IgorMi
This is not a true statement. The Microsoft recommended value of 300 was redundant a while ago since memory became cheap and most production servers are filled with gigs and gigs of ram. A "true" value for PLE should be taken after careful perfmon monitoring with a "like live" workload.
October 4, 2013 at 3:59 am
Loundy (10/4/2013)
IgorMi (10/4/2013)
HiPLE min values should not go under 300 seconds. Fluctuating from 14h to 0.1h is still above 300 seconds, but the behavior itslef is not good for the database work.
It could be some other processes on the server that are eating the memory, as well as the other proposals given in previous posts of this thread.
Regards,
IgorMi
This is not a true statement. The Microsoft recommended value of 300 was redundant a while ago since memory became cheap and most production servers are filled with gigs and gigs of ram. A "true" value for PLE should be taken after careful perfmon monitoring with a "like live" workload.
Well, nobody wants 300 seconds for PLE (nearly 0.1 hour).
On some servers where I've been monitoring this counter I took its values varying and from 1500 and up to 30K.
So this is a good notice from you, and the low limit could practically go higher. However it depends on many other factors (number of users, hardware resources, the codes, other activities, and etc...).
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
October 4, 2013 at 4:10 am
Don't get too hung on page life expectancy. It's not a measure of performance really, but an indicator of certain behaviors within the system. I've seen servers that were performing just fine with a very low page life expectancy. But radical fluctuations like are being discussed are concerning, not because of the PLE itself, but what it represents.
"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
October 4, 2013 at 4:13 am
IgorMi (10/4/2013)
PLE min values should not go under 300 seconds.
That was a poor recommendation 8 odd years ago when servers had maybe 4GB of memory. These days it's a terrible recommendation. PLE of 300 means that the entire buffer pool is getting displaced and replaced in 5 minutes. Work out, for a normal server (32GB, 64Gb memory) just how much sustained IO throughput would be required to manage that and then tell me if you still think 300 is a good base threshold.
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
October 4, 2013 at 5:39 am
GilaMonster (10/4/2013)
IgorMi (10/4/2013)
PLE min values should not go under 300 seconds.That was a poor recommendation 8 odd years ago when servers had maybe 4GB of memory. These days it's a terrible recommendation. PLE of 300 means that the entire buffer pool is getting displaced and replaced in 5 minutes. Work out, for a normal server (32GB, 64Gb memory) just how much sustained IO throughput would be required to manage that and then tell me if you still think 300 is a good base threshold.
Hi Gail
Thanks for your reply. Now I don't think the old poor recommendation of 300 is near a real low limit threshold for PLE.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
October 4, 2013 at 5:54 am
Ok Guys thanks for all your input.
Going back to Grants original response - I think I have found the query which is running for a long time and causing the slow response in the app. Now off to the Lead developers office........!!!!:w00t:
October 4, 2013 at 12:41 pm
GilaMonster (10/4/2013)
IgorMi (10/4/2013)
PLE min values should not go under 300 seconds.That was a poor recommendation 8 odd years ago when servers had maybe 4GB of memory. These days it's a terrible recommendation.
We have a few here with PLE < 20 😮 I was planning on implementing some alerting on the value (<300) but once I saw those...no complaints by users though which would imply its heavy OLTP instead of OLAP?
October 4, 2013 at 1:04 pm
Dird (10/4/2013)
GilaMonster (10/4/2013)
IgorMi (10/4/2013)
PLE min values should not go under 300 seconds.That was a poor recommendation 8 odd years ago when servers had maybe 4GB of memory. These days it's a terrible recommendation.
We have a few here with PLE < 20 😮 I was planning on implementing some alerting on the value (<300) but once I saw those...no complaints by users though which would imply its heavy OLTP instead of OLAP?
No it wouldn't imply that. OLTP isn't immune to low PLE. Implement some alerts, not on that stupid, outdated 300 value.
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
October 4, 2013 at 1:45 pm
No, I'm saying I guess it IS heavy OLTP? Manipulate in memory -> spew to disk -> repeat
Either that or the overhead of retrieving from disk everytime a query is run is deemed acceptable on that server~
I'll run a trace next Monday & see 😮
October 4, 2013 at 1:47 pm
OLTP does not automatically result in low PLE.
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
October 4, 2013 at 1:51 pm
What do you mean by automatically?
I ran out of ideas for alerting too~ read that the hit ratio is pointless and nobody complains of page life way below 300. Instead of alerts I just plan to add maybe top 5 wait events & top 5 statements (s). Page is pretty full & nobody uses it anyways except me occassionally =P
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply