Keith Browning (12/1/2010)

---

...or you could use perfmon to get the same.

/******************************************************************************

http://www.sqlservercentral.com/scripts/Performance+Monitoring/71677/

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.
*******************************************************************************/

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

SET @counter = 0

BEGIN TRY
DROP TABLE #pleSample
END TRY
begin CATCH
END CATCH
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] LIKE N'%:Buffer Manager%'
AND counter_name = N'Page life expectancy'

SET @counter = @counter + 1
RAISERROR('loop number %i ', 10, 1, @counter) WITH NOWAIT
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