Get page life expectancy by SQL server Extended events

The following script can be used to track how many seconds Page life expectancy stays below the recommended value 300. If you use perfmon to track page life expectancy you will get the actual value right now, but what if you want to keep track of how long time SQL server have been running with a low Page life expectancy? This is one efficient solution by using Extended events. Feel free to use it at your own responsibility or contact us if you want help from one of our SQL server consultants.

--Creates the event and the event target-- IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='XE_PageLifeExpectancy') DROP EVENT SESSION [XE_PageLifeExpectancy] ON SERVER; CREATE EVENT SESSION [XE_PageLifeExpectancy] ON SERVER ADD EVENT sqlos.wait_info( WHERE (([duration]>(100)))), ADD EVENT sqlserver.buffer_manager_page_life_expectancy( WHERE (([count]<(300)))) ADD TARGET package0.synchronous_event_counter WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF) -- Start the event -- ALTER EVENT SESSION [XE_PageLifeExpectancy] ON SERVER STATE = START -- Query the Target SELECT n.value('../@name[1]', 'varchar(50)') as PackageName, n.value('@name[1]', 'varchar(50)') as EventName, n.value('@count[1]', 'bigint') as Occurence FROM ( SELECT CAST(target_data AS XML) as target_data FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address WHERE s.name = 'XE_PageLifeExpectancy' AND t.target_name = 'synchronous_event_counter' ) as tab CROSS APPLY target_data.nodes('CounterTarget/Packages/Package/Event') as q(n) -- Use this statement if you want to drop the Event Session IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='XE_PageLifeExpectancy') DROP EVENT SESSION [XE_PageLifeExpectancy] ON SERVER;