Average Page Life Expectancy

/****************************************************************************** 
NOTES: 
 This script provides a sampling of PLE based on (1) minute intervals from 
 sys.dm_os_performance_counters. Originally written on November 11, 2010 
 by Tim Parker.
*******************************************************************************/ 


DECLARE @counter INT --This will be used to iterate the sampling loop for the PLE measure. 

SET @counter = 0 

CREATE TABLE #pleSample
 (
 CaptureTime DATETIME,
 PageLifeExpectancy BIGINT
 ) 

WHILE @counter < 30 --Sampling will run approximately 1 minute. 
 BEGIN 
 --Captures Page Life Expectancy from sys.dm_os_performance_counters 
 INSERT INTO #pleSample
 (
 CaptureTime,
 PageLifeExpectancy 
 )

 SELECT CURRENT_TIMESTAMP,
 cntr_value
 FROM sys.dm_os_performance_counters
 WHERE [object_name] = N'SQLServer:Buffer Manager'
 AND counter_name = N'Page life expectancy'

 SET @counter = @counter + 1 
 WAITFOR DELAY '000:00:02'

 END 


--This query will return the average PLE based on a 1 minute sample. 
SELECT RIGHT(CONVERT(VARCHAR(24), DATEADD(SECOND, AVG(PageLifeExpectancy), 0), 113),12) AS 'Average Page Life Expectancy'
FROM #pleSample 


DROP TABLE #pleSample