April 15, 2015 at 5:13 am
I saved values about Life time expectency every night.
By this :
SELECT @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy'
THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
FROM master.sys.sysprocesses
WHERE cmd='LAZY WRITER')
ELSE ''
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
,Getdate() as InfoDate
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND[counter_name] = 'Page life expectancy'
But I cant interprated it . (I will read the book that you advice : Accidental_DBA_EBook)
Befor that these are the results :
some values are not in range : (Fields : [cntr_value] AS PLE_SECS) such as 51 and 164 and ... and the day after came back to range .
7148----------- 2015-03-25 10:00:00.290
665----------- 2015-03-26 10:00:01.183
6511----------- 2015-03-27 10:00:00.770
164----------- 2015-03-28 10:00:00.407
2240----------- 2015-03-29 10:00:00.350
51----------- 2015-03-30 10:00:00.827
1886----------- 2015-03-31 10:00:00.787
9643----------- 2015-04-01 10:00:00.157
31308----------- 2015-04-02 10:00:00.140
6474----------- 2015-04-03 10:00:00.910
1179----------- 2015-04-04 10:00:00.753
406----------- 2015-04-05 10:00:00.750
1593----------- 2015-04-06 10:00:00.230
541----------- 2015-04-07 10:00:01.273
3924----------- 2015-04-08 10:00:00.543
4808----------- 2015-04-09 10:00:00.720
5060----------- 2015-04-10 10:00:00.253
5375----------- 2015-04-11 10:00:01.110
2617----------- 2015-04-12 10:00:00.203
14251----------- 2015-04-13 10:00:00.543
7595----------- 2015-04-14 10:00:00.227
93996----------- 2015-04-15 10:00:00.603
whats the meaning of these ?
April 15, 2015 at 5:24 am
At that granularity, not all that much. You need to have much more granular data to be able to see trends and behaviors. I prefer to have no more than 30 minute intervals.
And yes, you should read the accidental DBA book as this counter is discussed in Chapter 1 and, I think, chapter 4.
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
April 15, 2015 at 7:27 am
Also, it's really important that you don't attach massive significance to a single counter. Page Life Expectancy (PLE) is a decent measure of the behavior of some aspects of memory management within a system. But, it's largely looking at that system and that system only over time that gives PLE any meaning. You can't really compare one systems's PLE with another. And, just because PLE is low/high in a system doesn't mean you don't have/have performance issues, even ones related to memory. You have to look at a broader scope of metrics and then compare them to each other, and compare them to themselves over time. That's what monitoring is all about.
"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