Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

PLE (Page life expectancy) revisited again. Expand / Collapse
Author
Message
Posted Friday, February 22, 2013 8:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 248, Visits: 1,178
Hello I try to understand PLE.

Suppose the following situation. On a system were the usage is continues and stable. Suppose the PLE is stable on 400.

At a specific time say 15:36:48. Nothing special is happening. How long will the average (non touched) page which is in cache last from 15:36:48.

A. Is that 200 seconds?
B. Is that 400 seconds?
C. Or something else?

Now the same situation for 16:36:48.
Were I have run some DBCC function which uses the unfavored mechanism. I do understand that the PLE will drop because of this.
How long will the average (non touched) page which is in cache last from 16:36:48.

D. Just under 200 seconds?
E. Just under 400 seconds?
F. Or something else?


A week ago I would have anwsered B and 'What is a unfavored page?'
Now I would go with A and hope/think/assume D. But am very unsure about this anwser.

Ben Brugman



Post #1423103
Posted Friday, February 22, 2013 8:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 2,014, Visits: 3,453
I think you're looking at PLE from the wrong side - it's a measure of what has happened on your system, not what will happen.

Post #1423111
Posted Friday, February 22, 2013 9:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
PLE is a historical average of how long a page remained in cache after having been read in.

As for a disfavoured page, it will remain in cache until something else needs cache space. disfavoured just means it'll be the first thing to be tossed out of cache when some other pages need to be cached, instead of been evaluated using the LRU algorithm.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423120
Posted Friday, February 22, 2013 9:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 248, Visits: 1,178
Gazareth (2/22/2013)
I think you're looking at PLE from the wrong side - it's a measure of what has happened on your system, not what will happen.



The E stands for expectancy. Yes this is a prediction, but the weatherforecast is a prediction as well and I still listen to it and wil react to that forecast.

So I do not understand were I am looking at the wrong side.
And yes I am still wondering how the PLE is calculated from what has happened. And in the question I did mention that the use of the system was continuous and stable, indicating the future is very similar to the past.

But maybe I should ask how long have the pages in the cache been there on average? (Half of the PLE?)

Thanks for participating,
Ben Brugman
Post #1423130
Posted Friday, February 22, 2013 9:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
This might help: www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

However it's an average of all pages, not any particular page. You have to use it as a thumbnail guide in that sense, and look at it to try and determine if you have queries that might be disparate enough that they are being constantly retrieved from disk.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1423135
Posted Friday, February 22, 2013 10:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 248, Visits: 1,178
GilaMonster (2/22/2013)
PLE is a historical average of how long a page remained in cache after having been read in.


The average time a page remains in the cache is two times the time it has been in the cache at any moment.
So (in a stable system were the usage is continuous and same over the the time). A PLE of 400 does it mean that the pages are on average 400 seconds old and will live for an average of another 400 seconds ? Or is the total lifespan of the page in cache 400. So the average time that the page is in cache is 200 seconds and it will live on for another 200 seconds. (Asume a very stable and continuous system).


GilaMonster (2/22/2013)

As for a disfavoured page, it will remain in cache until something else needs cache space. disfavoured just means it'll be the first thing to be tossed out of cache when some other pages need to be cached, instead of been evaluated using the LRU algorithm.

At the moment I am running a checkdb to see if this influences the PLE number. After previous discussions my impression was that dbcc would drop the PLE. And yes it does within 10 minutes the PLE has dropped from 15150 to 189.
The system was not touched for some time so the 15150 was not only the average but also very close to the PLE of each individual page of all pages in the cache. So if the PLE is the average of the time the pages staying in the cache then less then 2 percent of those pages are still there. (Otherwise the average can not become this low.).

This leaves me confused.
If the PLE is realy the average then the disfavored system did not work in my situation.
If the disfavored system uses a small part of the cache and reuses that then the PLE is not the actual average of all the pages in cache and also does not predict how long the average page still will stay in cache.

Am I missing something ?

Thanks for your time and attention.
Thanks for bearing with me on this.
ben brugman

PLE during the checkdb.
Start at time 0: 15150
After 10 min : 189
Stable for about ? 10 minutes: 186
After those 20 minutes : 207
After 24 minutes : 187

Post #1423147
Posted Friday, February 22, 2013 10:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-does-page-life-expectancy-mean-137153

Page life expectancy is an instantaneous measure of how long (in seconds) a data file page is expected to remain in memory in the buffer pool (also referred to as the buffer cache).


ie, if PLE is 400, then on average a page is expected to remain in cache for 400 seconds.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1423151
Posted Friday, February 22, 2013 10:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 2,014, Visits: 3,453
ben.brugman (2/22/2013)

The average time a page remains in the cache is two times the time it has been in the cache at any moment.


Is it?

Edit: actually, I see how you got that. Not sure I agree though.
Post #1423153
Posted Friday, February 22, 2013 10:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 248, Visits: 1,178
Gazareth (2/22/2013)
ben.brugman (2/22/2013)

The average time a page remains in the cache is two times the time it has been in the cache at any moment.


Is it?

Sorry but with any moment I actually meant a random moment. And with average I meant off a large number of tries.

If you pick a point in time totally random and look at a totally random page in the cache. Statistically the time that the page has been in cache and the time that the page will remain in the cache are the same.

But maybe I did not write it clearly enough. Suppose a page remains in the cache for one hour. 3600 seconds.
If you randomly check the cache many times during a long time and you find that specific page is the cache on average that page will have been there 1800 seconds statistically.

Hope to have been a bit clearer with this explanations.
If not I am prepared to try to give a more comprehensive illustration.

Gazareth (2/22/2013)
Edit: actually, I see how you got that. Not sure I agree though.


In real life no, but to understand what the definition is of PLE, I am assuming a stable situation were the amount new pages read from disk is totaly constant. And pages enter AND exit the cache in the same order. After I get this about the PLE, I want to understand how the unfavored pages should be seen in this context. But statistically the assumption is correct. Compare an always open shop. If at random times you measure the time that a person is in the shop and at (other) random times you ask an exiting person how long he/she has been in the shop, the exiting persons have been there twice the time of the the first measurement.
When using random times this principle holds true for many situations.

ben
(Sorry, by now I am noticing that my English is not correct in all my sentences, sorry)
Post #1423163
Posted Friday, February 22, 2013 10:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 2,014, Visits: 3,453
Hi Ben,
Yep, understood what you meant after I thought about it a bit.

You can't average the value for a given page - the value at 1 hour isn't comparable to the one at (say) 15 minutes, except to say the life has increased over time.
Post #1423169
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse