February 22, 2013 at 8:37 am
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
February 22, 2013 at 8:56 am
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.
February 22, 2013 at 9:13 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2013 at 9:33 am
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
February 22, 2013 at 9:44 am
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.
February 22, 2013 at 10:05 am
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
February 22, 2013 at 10:13 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2013 at 10:16 am
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.
February 22, 2013 at 10:39 am
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)
February 22, 2013 at 10:57 am
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.
February 22, 2013 at 11:07 am
GilaMonster (2/22/2013)
ie, if PLE is 400, then on average a page is expected to remain in cache for 400 seconds.
Is that from the moment we measure. Or from the moment the page was last touched ?
(This makes a difference of a factor 2).
What would the PLE be in the example below ?
6 total time that a page is expected to be in the cache.
3 the remaining time that the page is expected to be in the cache.
111111|
22222|
3333|3
444|44
55|555
6|6666
|77777
|888888
|9999999
| AAAAAAA
| BBBBBBB
^
^
time
Above example only 6 pages fit in a cache. The vertical bar is the time line.
Page two gets at that moment replaced by page 8.
Cache now time_left Total_time
1NO
2 YES 6 0 6
3 YES 5 1 6
4 YES 4 2 6
5 YES 3 3 6
6 YES 2 4 6
7 YES 1 5 6
8 YES 0 6 6
9 NO
A NO
B NO
So the average time (past) the 6 pages are in the cache is 3 seconds
And the average time (future) the 6 pages will remain in the cache is also 3 seconds.
The total time (total = past + future) that a page has remained in the cache is 6 seconds.
Maybe this wil clear up my question.
Sorry that I wasn't clear enough with my question.
Ben
February 22, 2013 at 11:19 am
ben.brugman (2/22/2013)
GilaMonster (2/22/2013)
ie, if PLE is 400, then on average a page is expected to remain in cache for 400 seconds.Is that from the moment we measure. Or from the moment the page was last touched ?
If the PLE is 400, then on average a page is expected to remain in cache for a total of 400 seconds. 400 seconds from the time the page was pulled into cache until the time is is removed from cache. On average. PLE = Page Life Expectancy, the time that a page is expected to live in the cache
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
February 22, 2013 at 11:31 am
GilaMonster (2/22/2013)
ben.brugman (2/22/2013)
GilaMonster (2/22/2013)
If the PLE is 400, then on average a page is expected to remain in cache for a total of 400 seconds. 400 seconds from the time the page was pulled into cache until the time is is removed from cache. On average. PLE = Page Life Expectancy, the time that a page is expected to live in the cache
Thank you, that does claer it up.
(Sorry that I interpreted the term as REMAINING ple. Probably because I was associating the word expectancy with the future. Maybe a language difference.)
For the checkdb which has finished now, the PLE remained fairly stable. Around 200. Now it is going up again 318 at the moment. It is going up with 1 second for each second. But that's expected. But I still do not know if the cache was completely cleared by the dbcc checkdb.
Thanks,
Ben
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply